All Forums Database
jj91709 1 post Joined 05/15
26 May 2016
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:
teradatauser2 236 posts Joined 04/12
26 May 2016

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

sjuolay 7 posts Joined 03/16
27 May 2016

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.

dnoeth 4628 posts Joined 11/04
27 May 2016

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

 

Dieter

sjuolay 7 posts Joined 03/16
29 May 2016

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

dnoeth 4628 posts Joined 11/04
29 May 2016

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

Dieter

You must sign in to leave a comment.