How to calculate Monthly aggregates from daily data

Question

How to calculate Monthly aggregates from daily data

October 9, 2021 at 10:40 am

This was removed by the editor as SPAM

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

If you look at the previous code, 90% of the answer is in the first CTE. All you have to do to that is add some of the original columns to it and then call it with an external SELECT. It's almost identical to the previous code. Here's what I think you want. You may have to remove some columns (expand the "*" to do so) and/or change the sort order in the ORDER BY.

--===== Get the MIN and MAX FileDate 
DECLARE  @pStartMonth DATE
        ,@pEndMonth   DATE
;
 SELECT  @pStartMonth = MIN(FileDate)
        ,@pEndMonth   = MAX(FileDate)
    FROM #MYSRC
;
   WITH 
 cteEnumerate AS
(--==== Number the rows for the given date range by unique instance of ID_1,ID_2,ID_3
 SELECT  ID_1    
        ,ID_2    
        ,ID_3    
        ,Pay1    
        ,Pay2    
        ,Status 
        ,Status2        = SUBSTRING(Status,1,CHARINDEX('-',CONCAT(Status,'-'))-1)
        ,FileDate
        ,FileMonth      = CONVERT(DATE,DATEADD(mm,DATEDIFF(mm,0,FileDate),0))
        ,InstanceNum    = ROW_NUMBER() OVER (PARTITION BY ID_1,ID_2,ID_3 ORDER BY FileDate)
   FROM #MYSRC
  WHERE FileDate >=  DATEADD(mm,DATEDIFF(mm,0,@pStartMonth),0)
    AND FileDate <   DATEADD(mm,DATEDIFF(mm,-1,@pEndMonth) ,0) -- First month after @pEndMonth
)
 SELECT *
   FROM cteEnumerate
  WHERE InstanceNum = 1
  ORDER BY FileMonth, ID_1, ID_2, ID_3
;

The code above works with the previous test data that you posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

asita

SSCarpal Tunnel

Points: 4144

October 2, 2021 at 11:19 pm

#3935927

Can you please assist with some way.

Thank you

asita SSCarpal Tunnel Points: 4144 More actions · Answer 3

Agreed Jeff, Thank you very much for helping it out

only thing here i am doing is some conditions that i need to extend this. I am good so far

Thank you a ton AGain

asita

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

asita wrote:

Can you please assist with some way.
Thank you

Heh... DUDE! Look at the latest post I have code posted in! ;D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

asita SSCarpal Tunnel Points: 4144 More actions · Answer 5

Thank you Jeff.

One last request on this. so we have only 5 years worth of data altogether (Filedate column) from 2017 onwards.

so here how can I change months data to weekly aggregate data for total counts.

for example earlier we aggregated to month level, but now we would like to do weekly level

for example

Sep 5 2021 To Sep 11 2021 200 100 20

Sep 12 2021 To Sep 18 2021 50 10 2

Sep 19 2021 To Sep 25 2021 0 0 0

Thank you in advance Jeff.

asisit

Thank you,

Asiti

asita SSCarpal Tunnel Points: 4144 More actions · Answer 6

Hi Jeff,

I tried with WK passing in the Date Add but due with function somehow it is all zeros 🙁

can you please let me know since it is only 5 years of data altogether (about 60 months max) can we ignore the function? (fntally)

Thank you

asiti

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

Jeff Moden wrote:

so here how can I change months data to weekly aggregate data for total counts.

Heh... maybe send me a coupon for a week's worth of of #8 Jimmy John's foot long sandwiches with a $100 bill as the gift wrapping? 🙂

Months don't always start on Sundays. What do you want done there in relationship to the input parameters??

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

asita SSCarpal Tunnel Points: 4144 More actions · Answer 8

Good Morning Jeff 🙂

Definitely owe you for this great help for sure will celebrate Jeff 🙂

i just put some sample so in a way can we aggregate to weekly rather monthly? please

in the source data the we have daily data (filedate column) so please ignore monthly aggregation we will do weekly.

I gave sample need not be start with sunday so on just by week Jeff.

Please

Thank you

asiti

Thanks

Asiti

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

I'll try to post a solution tonight after work if no one else beats me to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

asita SSCarpal Tunnel Points: 4144 More actions · Answer 10

asita

SSCarpal Tunnel

Points: 4144

October 8, 2021 at 4:21 pm

#3938911

Thank you Much Jeff. I owe you a big party 🙂

pietlinden SSC Guru Points: 63428 More actions · Answer 11

DECLARE @MyDate DATE = '10/9/2021';

SELECT DATEPART(week,@MyDate);

DATEPART(week,[DateColumn]) will return the weeknumber of the year. Just group on that.

Help yourself. Introduce yourself to the help files on SQL Server.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 12

asita

SSCarpal Tunnel

Points: 4144

October 10, 2021 at 2:57 am

#3939171

Anyluck / Chance Jeff ? Please 🙂

Thanks

asita SSCarpal Tunnel Points: 4144 More actions · Answer 13

asita

SSCarpal Tunnel

Points: 4144

October 10, 2021 at 2:58 am

#3939174

Sure Linden will try it. Thank you

asita SSCarpal Tunnel Points: 4144 More actions · Answer 14

Hi Jeff

tried below changing to week instead of MM, getting all zeros seems it has issue with join last but one line on filemonth. any advise please?

DECLARE @pStartMonth DATE

,@pEndMonth DATE

;

SELECT @pStartMonth = MIN(FileDate)

,@pEndMonth = MAX(FileDate)

FROM #MYSRC

;

WITH

cteEnumerate AS

(--==== Number the rows for the given date range by unique instance of ID_1,ID_2,ID_3

SELECT FileMonth = DATEPART(week,[filedate])

,Status2 = SUBSTRING(STATUS,1,CHARINDEX('-',CONCAT(STATUS,'-'))-1)

,InstanceNum = ROW_NUMBER() OVER (PARTITION BY [Indv id], [Chapter Id] ORDER BY FileDate)

FROM #MYSRC

WHERE FileDate >= DATEADD(mm,DATEDIFF(mm,0,@pStartMonth),0)

AND FileDate < DATEADD(mm,DATEDIFF(mm,-1,@pEndMonth) ,0) -- First month after @pEndMonth

)

,cteAggregate AS

(--==== Only the rows with an InstanceNum = 1 are new. Group those by month and count them.

SELECT DateCol = REPLACE(SUBSTRING(CONVERT(VARCHAR(20),FileMonth,106),4,20),' ','-')

,NewMembersCount = SUM(InstanceNum)

,ENMembersCount = SUM(IIF(Status2 = 'EN',InstanceNum,0))

,FUMembersCount = SUM(IIF(Status2 = 'FU',InstanceNum,0))

,FileMonth

FROM cteEnumerate

WHERE InstanceNum = 1

GROUP BY FileMonth

)

,cteCalendar AS

(--==== Create a calendar of starting dates for all months in the desired date range.

SELECT FileMonth = DATEADD(WEEK,t.N,DATEADD(WEEK,DATEDIFF(WEEK,0,@pStartMonth),0))

FROM dbo.fnGETBIGINT(0,DATEDIFF(WEEK,@pStartMonth,@pEndMonth))t

)--==== Outer join to the calendar table so dates with no data will produce a "0".

SELECT DateCol = REPLACE(SUBSTRING(CONVERT(VARCHAR(20),cal.FileMonth,106),4,20),' ','-')

,NewMembersCount = ISNULL(agg.NewMembersCount,0)

,ActiveMembersCount = ISNULL(agg.ENMembersCount ,0)

,PotentialMembersCount = ISNULL(agg.FUMembersCount ,0)

-- INTO DBO.DMT_MembersStatistics

FROM cteCalendar cal

LEFT JOIN cteAggregate agg

ON cal.FileMonth = agg.FileMonth

ORDER BY cal.FileMonth

;

Thank you

asiti