Calculating 21 Day Averages

1

I have written some simple code to get total values for 2 months as below:

select CONVERT(date,DocumentDate),
Sum(Case When User_PrimaryChannelId = 8 Then TotalNetValue End) as [R&D],
Sum(Case When User_PrimaryChannelId = 7 Then TotalNetValue End) as [M&E],
Sum(Case When User_PrimaryChannelId = 50013 Then TotalNetValue End) as [C&U],
Sum(Case When User_PrimaryChannelId = 50024 Then TotalNetValue End) as [DPM],
Sum(Case When User_PrimaryChannelId = 50026 Then TotalNetValue End) as [TP.com],
Sum(Case When User_PrimaryChannelId = 10 Then TotalNetValue End) as OSS,
Sum(Case When User_PrimaryChannelId In (8,7,50013,50024,50026,10) Then TotalNetValue End) as [Group]

From SOPOrderReturn Inner Join CRM..Users On Left(AnalysisCode14,5) = Left(User_Logon,5) and User_Deleted Is Null

Where DocumenttypeId = 0 and DocumentStatusId In (0,1,2) and YEAR(DocumentDate) >= 2016 and MONTH(DocumentDate) In (10,11)

Group by CONVERT(date,DocumentDate)
Order by CONVERT(date,DocumentDate) asc

However I now want to be able to create a further column for each which then holds the 21DA values, what is the easiest way to achieve this ?

Thanks in advance

3

I would change that to:

AND DocumentDate >= '20161001'
AND DocumentDate <  '20161201'

because that makes it SARGable - SQL will use an index on DocumentDate if possible, whereas if you use a function such as YEAR(DocumentDate) then SQL is likely to scan all rows in the table instead, which will be less efficient and scale badly

I would add an ELSE 0 to that - otherwise SQL will issue a warning message because the SUM contains NULL values, and an APP may cough! processing the Warning.

Not sure what your criteria is for 21DA, but if it is the last 21 days in the report report period (e.g. 10-Nov to 30-Nov) you could do something like:

...
Sum(Case When User_PrimaryChannelId = 8 
              AND DocumentDate >= '20161110'
              AND DocumentDate <  '20161201'
    Then TotalNetValue ELSE 0 End) as [R&D 21DA], 
...

(For an Average perhaps you want to divide the SUM for that 21 day period BY 21 ?)

4

Thanks for your reply.

However the 21DA needs to be calculated from the document date of the current row.

5

Sorry, means nothing to me :frowning: You'll need to provide a formula, or description of the calculation, unless someone else known how to calculate this.