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;