1

Column names: Site_key. Billing_date ,Reason_type

Things to Find:

  1. Consecutive count for last 5 months:
  2. Last 3 month Count
  3. Last 6 month Count

I have to find Consecutive count for last 5 months for a site_key based on the condition Reason_TYpe. There are 4 distinct Reason_type:

Zero_bill
Same_Units
Units_Deviate
Average_Bill\

I need to find for a particular site key say for ex: 3, billing_date is say 20-11-2015, and reason type ='Zero_Bill'

  1. How many consecutive previous scenarios were there were site had Zero_Bill (excluding current month)
  2. How many such Scenarios in last 3 months (including current months)
  3. How many such Scenarios in last 6 months (including current months)

Sample data:

 site_key |    billing_date     | Reason_type 
----------+---------------------+---------------------
        3 | 2015-01-03 00:00:00 | AVERAGE_BILL
        3 | 2015-02-03 00:00:00 | ZERO_Bill  
        3 | 2015-03-01 00:00:00 | ZERO_Bill 
        3 | 2015-04-03 00:00:00 | AVERAGE_BILL
        3 | 2015-05-03 00:00:00 | AVERAGE_BILL
        3 | 2015-06-03 00:00:00 | ZERO_BILL
        3 | 2015-07-03 00:00:00 | ZERO_BILL
        3 | 2015-08-03 00:00:00 | AVERAGE_BILL
        3 | 2015-10-06 00:00:00 | ZERO_BILL

Expected Output:

 site_key |    billing_date     | Reason_type            |LAST_Consecutive| Last_3_Months | Last_6_months
----------+---------------------+----------------------- +----------------+---------------+--------------------
        3 | 2015-01-03 00:00:00 | AVERAGE_BILL           |0               |1              |1
        3 | 2015-02-03 00:00:00 | ZERO_Bill              |0               |1              |1
        3 | 2015-03-01 00:00:00 | ZERO_Bill              |1               |2              |2    
        3 | 2015-04-03 00:00:00 | AVERAGE_BILL           |0               |2              |2
        3 | 2015-05-03 00:00:00 | AVERAGE_BILL           |1               |2              |2
        3 | 2015-06-03 00:00:00 | ZERO_BILL              |0               |2              |3
        3 | 2015-07-03 00:00:00 | ZERO_BILL              |1               |2              |4
        3 | 2015-08-03 00:00:00 | AVERAGE_BILL           |0               |1              |3
        3 | 2015-10-06 00:00:00 | ZERO_BILL              |0               |3              |3

What I tried: For Consecutive count:

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

For Last_3_Months and Last_6_Months:

Select site_key,Billing_Date,Case when cnt>3 then 3 else cnt end as Count_OF_3, Case when cnt>6 then 6 else cnt end as Count_OF_6
from (
Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Zero_Bill' group by billing_date,site_key,Reason_TYPE
union all
Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from Schema.AVG_SCN where reason_type='Average_Bill'  group by billing_date,site_key,Reason_TYPE
union all
Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Units_Deviate'  group by billing_date,site_key,Reason_TYPE
union all
Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Same_Units'  group by billing_date,site_key,Reason_TYPE) as a;

Your Answer

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

Browse other questions tagged or ask your own question.