SQLTeam.com | Weblogs | Forums

Averages


#1

Please see code below, is there a simple way of saying that if the sales for the day is 0 then use the previous calculated 21 day average. You will see that I have put some dummy code in for the column [C&U 21DA].

Current code:

select *
, Sum(data.[R&D]) Over (order by DocumentDate rows between 20 preceding and current row) / 21 as [R&D 21DA]
, Sum(data.[M&E]) Over (order by DocumentDate rows between 20 preceding and current row) / 21 as [M&E 21DA]

, Case When Sum(data.[C&U]) >0 Then Sum(data.[C&U]) Over (order by DocumentDate rows between 20 preceding and current row) / 21 **
** Else 1 End as [C&U 21DA]

, Sum(data.[DPM]) Over (order by DocumentDate rows between 20 preceding and current row) / 21 as [DPM 21DA]
, Sum(data.[TP.com]) Over (order by DocumentDate rows between 20 preceding and current row) / 21 as [TP.com 21DA]
, Sum(data.[OSS]) Over (order by DocumentDate rows between 20 preceding and current row) / 21 as [OSS 21DA]
, Sum(data.[Group]) Over (order by DocumentDate rows between 20 preceding and current row) / 21 as [Group 21DA]
, Cast(Cast(Convert(datetime,DocumentDate) as float) as bigint)+2

From (
select CONVERT(date,DocumentDate) as DocumentDate,
Sum(Case When User_PrimaryChannelId = 8 Or (o.AnalysisCode14 In ('AHILL','LPOME') and YEAR(DocumentDate)=2015) Or (o.AnalysisCode14 = 'AHILL' and YEAR(DocumentDate)=2016 and MONTH(DocumentDate)=1)
Or (o.AnalysisCode14 = 'JFRE2' and YEAR(DocumentDate)=2016)
Then TotalNetValue *(Case When PriceBandID=247258 Then 0.8 Else 1 End) Else 0 End) as [R&D],

   Sum(Case When o.AnalysisCode14 In ('RBURK','TBURK','GPONS','LPOCO','FRROS','JDBUR') Or (o.AnalysisCode14 In ('DRFRE','TOEAG','LPRES') and YEAR(DocumentDate)=2015) 
      or (o.AnalysisCode14='TJENN' and YEAR(DocumentDate)=2015 and Month(DocumentDate) <=9) Or (o.AnalysisCode14='LPOME' and YEAR(DocumentDate)=2016) 
		Then TotalNetValue *(Case When PriceBandID=247258 Then 0.8 Else 1 End) Else 0 End) as [M&E],

   Sum(Case When o.AnalysisCode14 In ('LPRES','TOEAG') and YEAR(DocumentDate) >= 2016 Or (o.AnalysisCode14 = 'AHILL' and YEAR(DocumentDate)=2016 and MONTH(DocumentDate) >=2) 
       Then TotalNetValue *(Case When PriceBandID=247258 Then 0.8 Else 1 End) Else 0 End) as [C&U], 

   Sum(Case When User_PrimaryChannelId = 50024 and YEAR(DocumentDate) >= 2016 
       Then TotalNetValue *(Case When PriceBandID=247258 Then 0.8 Else 1 End) Else 0 End) as [DPM],

   Sum(Case When User_PrimaryChannelId = 50026 
       Then TotalNetValue *(Case When PriceBandID=247258 Then 0.8 Else 1 End) Else 0 End) as [TP.com], 

   Sum(Case When o.AnalysisCode14 In ('ISMIT','LWILL','JGARD','SHILL') or (o.AnalysisCode14='TJENN' and YEAR(DocumentDate)=2015 and Month(DocumentDate) >=10) 
       or (o.AnalysisCode14='TJENN' and YEAR(DocumentDate)=2016)
       Then TotalNetValue *(Case When PriceBandID=247258 Then 0.8 Else 1 End) Else 0 End) as OSS,

   Sum(Case When User_PrimaryChannelId In (8,7,50013,50024,50026,10) 
       Then TotalNetValue *(Case When PriceBandID=247258 Then 0.8 Else 1 End) Else 0 End) as [Group]

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

Where DocumenttypeId = 0 and DocumentStatusId In (0,1,2) and CONVERT(DATE,DocumentDate) >= '2015-12-01' and User_Logon Not In ('RCFRE','DDIBL','SRHAR','RJFRE','NAFRE','GDUDE','AMFRE')
and DocumentNo Not like '##%'

Group by CONVERT(date,DocumentDate)
Having Sum(Case When User_PrimaryChannelId In (8,7,50013,50024,50026,10) Then TotalNetValue Else 0 End) !=0
)data
Group by data.DocumentDate, data.[R&D], data.DPM, data.[Group], data.[M&E], data.OSS, data.[TP.com], data.[C&U]
Order by CONVERT(date,DocumentDate) asc