sql closing distinct counts at end of each day

Question

Hi I have a requirement to calculate the closing counts of distinct House Numbers with status = 'AA' at the end of each day.

So at end of day on 19/03/2016 - if we have 3 distinct house H1, H2, H3 with AA count = 3 On 20/03/2016 if we have H2 record again with AA , and h4,h5 with AA , closing balance = 5 on 21/03/2016 if 2 of the houses move out of AA and another 3 get added to AA ,the count will be = 6

Could someone please help me with the sql to resolve this.

Database = Netezza .

So basically I need to count the number of distinct Houses from the beginning of time to that day which have status AA, for each and every day as the Closing count of Houses for the day.[CLsBal is the derivation required]

ReleaseDate|HNo|Status|HType|RelReason|  ValidFrm |  ValidTo |ClsBal
-------------------------------------------------------------------
01-Jan-16   H1    AA      R      XYZ    01-Jan-16   01-Jan-16   2
01-Jan-16   H2    AA      R      XYZ    01-Jan-16   31/12/2999  2
02-Jan-16   H3    AA      R      XYZ    02-Jan-16   31/12/2999  4
02-Jan-16   H4    AA      R      XYZ    02-Jan-16   31/12/2999  4
02-Jan-16   H5    AA      R      XYZ    02-Jan-16   31/12/2999  4
02-Jan-16   H1    AB      R      XYZ    02-Jan-16   31/12/2999  4
03-Jan-16   H6    AA      R      XYZ    02-Jan-16   31/12/2999  8
03-Jan-16   H7    AA      R      XYZ    02-Jan-16   31/12/2999  8
03-Jan-16   H8    AA      R      XYZ    02-Jan-16   31/12/2999  8
03-Jan-16   H9    AA      R      XYZ    02-Jan-16   31/12/2999  8
03-Jan-16   H3    AA      R      XYZ    02-Jan-16   31/12/2999  8

The code below is the logic for finding the closing Balance for a given day :

select cast('31-dec-2015' as date ) as RELEASEDATE,
    HNo,  
    HType,
    count(distinct Hno ) as ClosingCount
from HouseChanges
where  ReleaseDate <= '31-dec-2015'  
    and Status='AA'  -- House Status
    and ValidTo >= '31-dec-2015'
group by  RelReason,  Htype

I need this to be calculated for every single Day from 31-dec-2015

Can we use a recursive CTE to resolve this ?

I tried to but it keeps erroring out and Aginity isn't very great at returning error codes.

I used something Like this :

with closing as (
select cast('31-dec-2015' as date ) as RELEASEDATE,
HNo,  
HType,
count(distinct Hno ) as ClosingCount
from HouseChanges
where  ReleaseDate <= '31-dec-2015' 
and 
Status='AA'  -- House Status
and
VALID_TO_DATE >= '31-dec-2015'
 group by  RelReason,  Htype

union all

 select max(Release_date) as RELEASEDATE,
 HNo,  
HType,
count(distinct Hno ) as ClosingCount
from HouseChanges t1
inner join closing t2
on t1.Release_date <= t2. RELEASEDATE++interval '1 days'
where  
Status='AA'  -- House Status
and
VALID_TO_DATE >= t2. RELEASEDATE+interval '1 days'
and
t2. RELEASEDATE<=current_date
group by  RelReason,  Htype
)
 select * from closing

Turns out Netezza does not support reccursive CTE . Could someone advise how I can go about doing this without using a CTE ?

What kind of database: SQL-server, MySQL, PostgreSQL, Oracle, etc.? What have you tried till now? Where are you stuck? — Maciej Los, Mar 21 '16 at 13:03

score 0 · Answer 1 · answered Mar 21 '16 at 17:43

I don't fully understand the requirements you're describing but here is my guess. It should be possible to use a window function for this though I don't know if you have those available:

select
    ReleaseDate, HNo, HType,
    (
        select count(distinct hc2.Hno)
        from HouseChanges hc2
        where hc2.ReleaseDate <= hc.ReleaseDate
            and Status = 'AA' and ValidTo >= '31-dec-2015'
    ) as ClosingCount
from HouseChanges hc
where Status = 'AA' and ValidTo >= '31-dec-2015'
group by ReleaseDate, HNo, HType

sql closing distinct counts at end of each day

1 Answers1