SQL Server Average of Amt for each vendor in Date and Date-1

I'm trying to find a quicker, cleaner and easier way to calculate the average of an amount displayed over two months/periods. In my data set I have periods with several record per vendor going over a year, and I would like to know the average of two periods for the total amount per vendor.

So if Vendor ABC have two posts in January and 1 in February and 1 in March I would like there two be two rows in total for Vendor ABC. The first one would display the ABC average of (the Total of January + the total of February). The second row would show the ABC average of (the total in February and the total of march).

I can think of one way do this, which would be something like bellow (for the month of February):

select 
CONCAT(YEAR(Date_Col),MONTH(Date_Col)) AS Date_Col
,Vendor_Col
,SUM(Amt_Col) AS Amt_Col
CASE
    WHEN MONTH(Date_Col) = '02' THEN SUM(CASE
        WHEN MONTH(Date_Col) = '02' or MONTH(Date_Col) = '01' THEN Amt_Col)/2 AS '2MonthAvg'

Group by Date_Col, Vendor_col

This means I would have to make 11 separate WHEN-Statements: I'm thinking that the grouping would take care of displaying the vendor level 2month averages, but is there a smoother way than above to make it dynamic?

Here's an example of the data I'm working with

Date_Col  Vendor_col   Amt_col
202201   ABC          150.789,41
202201   ABC           75.678,85
202201   DEF          845.678,45
202201   GHI          450.657,43
202202   ABC           50.694,45
202202   DEF          135.624,86
202202   DEF          123.250,15
202202   GHI          450.000,00
202203   ABC           12.600,50
202203   DEF           60.600,60

I hope my question makes sense and please let me know if I should elaborate on anything.

I haven't really tried anything as I'm having trouble figuring out the logic, but what I've been messing around with is a dynamic SUM(CASE WHEN But I can't figure out how to make it compare the two dates dynamically since I'm working with historical data and not MontH(Today()).

The output I'm looking at would be something like this:

Date_Col  Vendor_col   Amt_col
202201   ABC          226.468,30 (total Jan amt, since I want the average with prior month)   
202201   DEF          845.678,45
202201   GHI          450.657,43
202202   ABC          138.581,36 ((sum of feb + sum of jan)/2)
202202   DEF          552.276,73
202202   GHI          450.328,72
202203   ABC           31.647,48 ((sum of mar + sum of jan)/2)
202203   DEF          159.737,81

I've also looked in to LAG, but as I understand LAG it looks at the preceding row, which doesn't make sense if I have several items of the Vendors which should have independent sums/averages for each period?

Try the following, it will give the desired output for one year starting from January:

DECLARE @start_date DATE = '20220101';

WITH dates AS
(
  SELECT Vendor_col, DATEADD(MONTH, n, @start_date) dt FROM
  (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) d(n)
  CROSS JOIN (SELECT DISTINCT Vendor_col FROM table_name) tb
), 
Sum_Per_V_D AS
(
  SELECT dts.Vendor_col, dts.dt,
         SUM(tb.Amt_col) sm
 FROM dates dts LEFT JOIN table_name tb
 ON YEAR(dts.dt) = YEAR(tb.Date_Col) AND 
    MONTH(dts.dt) = MONTH(tb.Date_Col) AND
    dts.Vendor_col = tb.Vendor_col
 GROUP BY dts.Vendor_col, dts.dt
),
final_result AS
(
  SELECT Vendor_col, dt,
    SUM(sm) OVER (PARTITION BY Vendor_col ORDER BY dt ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)/
      CASE WHEN MONTH(dt)<>1 THEN 2 ELSE 1 END AS res -- or simply AVG(sm) OVER (PARTITION BY Vendor_col ORDER BY dt ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
  FROM Sum_Per_V_D
  WHERE sm IS NOT NULL
)
SELECT Vendor_col, dt Date_Col, res Amt_col
FROM final_result
ORDER BY Vendor_col, dt

Output:

See a demo .

SQL Server Average of Amt for each vendor in Date and Date-1

1条答案

相关问题

热门标签

最新问答