Count Consecutive Days that we did not receive a file for a SKU

Database
Enthusiast

Count Consecutive Days that we did not receive a file for a SKU

Looking for assistance in counting consecutive days between the max data we did not receive a file for a SKU and today.  The fields are SKU_ID, Cal_Dte, File_Rcv_Flag where a 1 is that we received a file.  This is what I have so far to give me the max date for a SKU and the flag for that day.  There is a record for each day.

This is what I have so far:

(select sku_id,A.Last_Date,File_rcv_flag

from

     (select sku_id,file_rcv_flag,max(cal_dt) as Last_Date

     from micro_views.fact_vndr_inv_fd

     group by 1,2) A

A data example looks like as follows:

SKU_ID     Last Date     file_rcv_flag

12345       10/3/2015   1

12345       10/4/2015   0

12345       10/5/2015   0

So the results should be SKU_ID, Last Date and number of days missed.

thank you for your help

8 REPLIES
Junior Contributor

Re: Count Consecutive Days that we did not receive a file for a SKU

You need conditional aggregates:

select sku_id,
max(case when file_rcv_flag = 0 then cal_dt end) as Last_Date,
Last_Date - max(case when file_rcv_flag = 1 then cal_dt end) as Days_Missed
from micro_views.fact_vndr_inv_fd
group by 1
having Days_missed > 0
Enthusiast

Re: Count Consecutive Days that we did not receive a file for a SKU

Thanks, I think this is working.

How could I combine that with the full table so the results are a last received and days missed columns and the rest of the columns and if this condition does not exist then 0.


					
				
			
			
				
			
			
				
Enthusiast

Re: Count Consecutive Days that we did not receive a file for a SKU

select a.cal_dt,a.sku_id,a.sku_cnt,a.sku_cnt_sent,a.file_rcv_flag,b.Last_date,b.Days_missed from
(SELECT * FROM micro_views.fact_vndr_inv_fd) a
left join (select sku_id,
   max(case when file_rcv_flag = 0 then cal_dt end) as Last_Date,
   Last_Date - max(case when file_rcv_flag = 1 then cal_dt end) as Days_Missed
from micro_views.fact_vndr_inv_fd
group by 1
having Days_missed > 0) b
on a.sku_id=b.sku_id and a.cal_dt=b.Last_date
order by a.sku_id,a.cal_dt

I think I have it.

Enthusiast

Re: Count Consecutive Days that we did not receive a file for a SKU

REPLACE VIEW MICRO_VIEWS.FACT_VNDR_INV_FD
AS LOCKING ROW FOR ACCESS
select a.cal_dt,a.vndr_id,a.sku_id,a.sku_cnt,a.sku_cnt_sent,a.file_rcv_flag,b.Last_date,b.Days_missed from
(SELECT * FROM Micro_Data.FACT_EDI_VNDR_INV_SUM) a
left join (select sku_id,
   max(case when file_rcv_flag = 0 then cal_dt end) as Last_Date,
   Last_Date - max(case when file_rcv_flag = 1 then cal_dt end) as Days_Missed
from Micro_Data.FACT_EDI_VNDR_INV_SUM
group by 1
having Days_missed > 0) b
on a.sku_id=b.sku_id and a.cal_dt=b.Last_date;

I thought I had this SQL working and it does to a point.  Where it does not work is if a file has never been received... example no 1 flag for that SKU then the logic breaks down as it does not have a 1 to look back to and count forward for consecutive days.  Any thoughts on how to work this situation in?

Junior Contributor

Re: Count Consecutive Days that we did not receive a file for a SKU

Simply change the logic to:

having Days_missed > 0 or Days_missed is null 

Do you actually need the LEFT JOIN, this will return all rows?

In that case you might better rewrite it using OLAP functions.

Enthusiast

Re: Count Consecutive Days that we did not receive a file for a SKU

We do need all rows... I am new to the OLAP functions so I don't know about that

Enthusiast

Re: Count Consecutive Days that we did not receive a file for a SKU

Adding having Days miss is null is populating a last_date as the cal_dt for those with 0 file receive flag but does not count the days missed

Junior Contributor

Re: Count Consecutive Days that we did not receive a file for a SKU

Using OLAP functions this might be something like this:

select
t.*,
max(case when file_rcv_flag = 1 then cal_dt end)
over (partition by sku_id
order by cal_dt
rows unbounded preceding) as last_date,

case
when file_rcv_flag = 1 then 0
else cal_dt - (coalesce(last_date,
max(case when file_rcv_flag = 0 then cal_dt end)
over (partition by sku_id
order by cal_dt
rows unbounded preceding)-1)
)
end as Days_missed
from Micro_Data.FACT_EDI_VNDR_INV_SUM as t
order by 1,2

It returns a bit different result set, the number of missing days is calculated for each row, but this might fit your needs...