CODEWITHSUNDEEP
sqlsql-servert-sql
Wil-Liam
Wil-Liam

Reputation: 49

SQL How would I show a rolling 3 month average for this query

Need some help with maths side of things with this bit of code. You may see I need help on even more! But any help you could give me would be great

Basically, my aim is to bring back an; - AccountID, - something called a SPID, of which there could be multiple in an account, - Billed Date - The amount of the invoice on the corresponding billed date - Then 3 Month Average

The last part is what I am having an issue with

So far I have done this

Select 
t3.AccountID,
t3.SPID,
t2.BilledDate,
Amount = Sum (t1.Amount)

From dbo.table1 t1
Inner Join dbo.table2 t2
On t1.item1 = t2.item2

Inner Join Table3 t3 
On t2.Item1 = t3.item3

Where AccountID In (xxxxxxx)
And t2.BilledDate >= '20190401' 

Group By 
AccountID
,t3.spid
,t2.BilledDate
,t1.Discount

Now this gives me the raw data as so (apologies, not sure hwo to show in here, if someone can pleas edit that would be great);

 AccountID  SPID    BilledDate  Amount
xxx930  xxxxx1  05/04/2019  11.81
xxx930  xxxxx2  07/05/2019  11.01
xxx930  xxxxx3  06/06/2019  11.38
xxx930  xxxxx4  04/07/2019  11.01
xxx930  xxxxx5  06/08/2019  11.38
xxx930  xxxxx6  06/09/2019  11.38
xxx930  xxxxx7  04/10/2019  11.01
xxx930  xxxxx8  06/11/2019  11.38
xxx930  xxxxx9  04/12/2019  11.01
xxx930  xxxxx10 07/01/2020  11.38

However I would like a column at the end which would show the average of the cost for the previous 3 months

I have tried to use lead and lag functions, but I keep getting NULL in the column, which is where I think I'm gong wrong

any advice or help would be great

Thanks in advance

Upvotes: 0

Views: 528

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

I think apply is your best bet, because you don't seem to have one row per account and month:

with t as (
      <your query here>
     ) 
select t.*, tt.running_amount_3
from t outer apply
     (select avg(t2.amount) as running_amount_3
      from t t2
      where t2.AccountID = t.AccountID and
            t2.BilledDate <= t.BilledDate and
            t2.BilledDate > dateadd(month, -3, t.BilledDate)
     ) tt;

Upvotes: 1

Thom A
Thom A

Reputation: 95561

If there will always be a row, then seems like the easiest would be with a windows AVG function. Using the end sample values you have:

SELECT V.AccountID,
       V.SPID,
       V.BilledDAte,
       V.Amount,
       AVG(V.Amount) OVER (PARTITION BY V.AccountID ORDER BY V.BilledDate
                           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Last3Average
FROM (VALUES('xxx930','xxxxx1 ',CONVERT(date,'05/04/2019'),11.81),
            ('xxx930','xxxxx2 ',CONVERT(date,'07/05/2019'),11.01),
            ('xxx930','xxxxx3 ',CONVERT(date,'06/06/2019'),11.38),
            ('xxx930','xxxxx4 ',CONVERT(date,'04/07/2019'),11.01),
            ('xxx930','xxxxx5 ',CONVERT(date,'06/08/2019'),11.38),
            ('xxx930','xxxxx6 ',CONVERT(date,'06/09/2019'),11.38),
            ('xxx930','xxxxx7 ',CONVERT(date,'04/10/2019'),11.01),
            ('xxx930','xxxxx8 ',CONVERT(date,'06/11/2019'),11.38),
            ('xxx930','xxxxx9 ',CONVERT(date,'04/12/2019'),11.01),
            ('xxx930','xxxxx10',CONVERT(date,'07/01/2020'),11.38))V(AccountID,SPID, BilledDate,Amount);

Upvotes: 1

Related Questions