0

I have a table containing various sites and billing information of those sites. Ex:

 site_key |    billing_date     | Reason_type | rank
----------+---------------------+-------------+------
        3 | 2015-01-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-02-03 00:00:00 | Zero_Bill   |    1
        3 | 2015-03-01 00:00:00 | Zero_Bill   |    2
        3 | 2015-04-03 00:00:00 | RIGHT_BILL  |    2
        3 | 2015-05-03 00:00:00 | RIGHT_BILL  |    3
        3 | 2015-06-03 00:00:00 | RIGHT_BILL  |    4
        3 | 2015-07-03 00:00:00 | RIGHT_BILL  |    5
        3 | 2015-08-03 00:00:00 | RIGHT_BILL  |    6
        3 | 2015-10-06 00:00:00 | RIGHT_BILL  |    7

What I need to find: I need to find consecutive count for each site based on their Reason_Type and billing_date. For example:If I receive any bill on 2015-10-06 whose Reason tyoe is RIGHT_BILL. So I need to find how many past consecutive cases were there for this scenario. For example If I take billing_date =2015-03-01 its showing me rank as 2 which is right but again If i go and check for 2015-04-03 its showing 2 which is wrong it should also show as one since the last month billing_date was having Zero_Bill.

Expected output:

 site_key |    billing_date     | Reason_type | rank
----------+---------------------+-------------+------
        3 | 2015-01-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-02-03 00:00:00 | Zero_Bill   |    1
        3 | 2015-03-01 00:00:00 | Zero_Bill   |    2
        3 | 2015-04-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-05-03 00:00:00 | RIGHT_BILL  |    2
        3 | 2015-06-03 00:00:00 | RIGHT_BILL  |    3
        3 | 2015-07-03 00:00:00 | RIGHT_BILL  |    4
        3 | 2015-08-03 00:00:00 | RIGHT_BILL  |    5
        3 | 2015-10-06 00:00:00 | RIGHT_BILL  |    6

I tried:

Select site_key,
       billing_date,
       Reason_type, 
       Dense_rank() over (partition by site_key,billing_date,reason_type order by billing_date) as rank 
from indus_Dev_analytics.AVG_SCN  
where site_key=3 
order  by 1,2;
1

The problem in your query actually comes from the first row. It has the same Reason_type so it gives the extra ranking to the bottom records.

I think you can use CONDITIONAL_CHANGE_EVENT, maybe like:

select *,
    dense_rank() 
        over (partition by site,change_num,reason_type order by billing_date) as rank 
from (
   Select site,
     billing_date,
     Reason_type, 
     CONDITIONAL_CHANGE_EVENT(reason_type) 
         over (partition by site order by billing_date) as change_num
   from AVG_SCN  
   where site=3 
) foo 
order  by 1,2;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.