cumulative count by date

Database

cumulative count by date

I have data such as the following:

Field 1 (unique ID)     Date/time                       

1                               1/1/2016 01:05:09

2                               1/1/2016 01:05:09

3                               2/1/2016 12:00:00

4                               3/1/2016 05:06:06

...

n

I want to produce a report that shows:

Month                Cumulative sum 

201601             2 (since there were two entries with a date in January)

201602             3  (equal to 2 (from the prior row) + 1 for the one additional record from February

201603             4  (equal to 3 (from the prior row) + 1 for the one additional record from March

I have code as follows:

SEL cast(my_timestamp_field as date format 'yyyymm') as rule_month,
count(my_unique_id_field) OVER(partition by cast(my_timestamp_field as date format 'yyyymm') ORDER BY my_unique_id_field ASC ROWS UNBOUNDED PRECEDING) AS CUM_AMT
FROM table_name
order by rule_month

but that's getting me results like this:

rule_month    cum_amt

201601          1

201601          2

201602          1

201603          1

So, basically they're starting the numbering over at 1 each time. And the first row should not exist.

Thanks for the help.

Tags (1)
5 REPLIES
Enthusiast

Re: cumulative count by date

This works:

sel temp.myyearmonth,temp.mycount,sum(mycount) over(order by myyearmonth) from 

(sel (extract(year from exec_dt) || extract(month from exec_dt)) as myyearmonth ,count(*)  as mycount from  db.mytab

group by myyearmonth  ) temp

Thanks, samir

Enthusiast

Re: cumulative count by date

I don't have a complete solution, but I am sure

cast(my_timestamp_field as date format 'yyyymm') as rule_month

needs to be changed to

cast(cast(my_timestamp_field as date format 'yyyymm') as char(6)) as rule_month

because without the second cast 1st jan and 2nd jan will be printed as 201601 but will be treated as two different values for computation purposes

ps:do you have a background in SAS? if yes, then I understand how the requirement of second cast will be surprising to you.
Senior Apprentice

Re: cumulative count by date

Further simplified:

SEL TRIM(cast(my_timestamp_field as format 'yyyymm')) as rule_month,
SUM(COUNT(*))
OVER (ORDER BY my_unique_id_field ASC
ROWS UNBOUNDED PRECEDING) AS CUM_AMT
FROM table_name
GROUP BY rule_month
order by rule_month
Enthusiast

Re: cumulative count by date

Is there an implicit conversion from datetime to character in using TRIM(cast(my_timestamp_field as format 'yyyymm')) ?

Senior Apprentice

Re: cumulative count by date

Oops, correct, there's no automatic typcast for Timestamps, so instead of TRIM it must be a CAST.