Sum value for each end of month

Question

I have two tables.

Table A with CustomerID, InternalID, and AsOfDate.

Table B with InternalID, Spend, StoreNum and AsOfDate.

I am looking to create an output such that I have the sum of Spend per CustomerID for the last day of the month for last 12 months. Last day of the month is not 30 or 31, it goes by business days so I would like a query that looks for MAX date within each month to determine last day.

Example tables with data and query.

Declare @MAXDATE DATETIME
Select @MAXDATE = MAX (as_of_date) from table2 

select
  s.customerID,
  sum(case when s.asofdate = CAST('11/29/21' AS DATETIME) then hn.spend end) as val_prevmonth,
  sum(case when s.asofdate = @MAXDATE then hn.spend end) as val_curr
from table1 s
join table2 hn on hn.asofdate = s.asofdate 
                 and hn.internalID = s.internalID
where 
and  (s.asofdate = CAST('11/30/21' AS DATETIME) or s.asofdate = @MAXDATE)
group by s.customerID
order by s.customerID;

Table A

CustomerID	InternalID	AsOfDate
610	016	11/30/2021
610	016	11/29/2021
610	016	11/28/2021
610	016	10/29/2021
610	016	10/15/2021
610	016	9/15/2021
610	016	9/14/2021
610	016	8/10/2021
725	527	11/30/2021
725	527	11/29/2021
725	527	11/28/2021
725	527	10/29/2021
725	527	10/15/2021
725	527	9/15/2021
725	527	9/14/2021
725	527	8/10/2021

Table B

Spend	InternalID	StoreNum	AsOfDate
5000	016	123	11/30/2021
4000	016	321	11/30/2021
3000	016	456	11/28/2021
4500	016	654	10/29/2021
5000	016	789	10/15/2021
4000	016	756	9/15/2021
9000	016	987	9/15/2021
1000	016	545	8/10/2021
6000	527	124	11/30/2021
4500	527	354	11/30/2021
9000	527	745	11/28/2021
8500	527	846	10/29/2021
7500	527	646	10/29/2021
6000	527	311	9/14/2021
3000	527	211	9/14/2021
2500	527	121	8/10/2021

Table C. Note CustomerID 725 is missing from September because there were no transactions conducted by them on the last business day of September, the 15th. Also column names do not need to have the date in them, I put it there for sake of clarity.

CustomerID	11/30/2021 TotalSpend	10/29/2021 TotalSpend	9/15/2021 TotalSpend	8/10/2021 TotalSpend
610	9000	4500	1300	1000
725	10500	16000		2500

Answer 1

Updated - I have modified my answer to target SQL Server.

We start by getting a list of CustomerIDs for the period we are looking at -

SELECT DISTINCT CustomerID
FROM TableA
WHERE AsOfDate > NOW() - INTERVAL 1 YEAR

We do something similar to get a list of last trading day of the month for the past year and apply ROW_NUMBER() so we have unique numbers from 1 to 12 to address in the pivot.

SELECT ROW_NUMBER() OVER(ORDER BY MAX(AsOfDate) DESC) AS RowNum, MAX(AsOfDate) EndOfMonth
FROM TableA
WHERE AsOfDate > NOW() - INTERVAL 1 YEAR
GROUP BY YEAR(AsOfDate), MONTH(AsOfDate)
ORDER BY EndOfMonth ASC

We then pull the above queries together as two derived tables with a CROSS JOIN to give the cartesian product and then left join to TableA and TableB.

WITH customers (CustomerID) AS (
    SELECT DISTINCT CustomerID
    FROM TableA
    WHERE AsOfDate > DATEADD(year, -1, GETDATE())
), months (RowNum, EndOfMonth) AS (
    SELECT ROW_NUMBER() OVER(ORDER BY MAX(AsOfDate) DESC) AS RowNum, MAX(AsOfDate) EndOfMonth
    FROM TableA
    WHERE AsOfDate > DATEADD(year, -1, GETDATE())
    GROUP BY YEAR(AsOfDate), MONTH(AsOfDate)
)
SELECT
    customers.CustomerID,
    SUM(CASE WHEN months.RowNum =  1 THEN TableB.Spend END) AS TotalSpend1,
    SUM(CASE WHEN months.RowNum =  2 THEN TableB.Spend END) AS TotalSpend2,
    SUM(CASE WHEN months.RowNum =  3 THEN TableB.Spend END) AS TotalSpend3,
    SUM(CASE WHEN months.RowNum =  4 THEN TableB.Spend END) AS TotalSpend4,
    SUM(CASE WHEN months.RowNum =  5 THEN TableB.Spend END) AS TotalSpend5,
    SUM(CASE WHEN months.RowNum =  6 THEN TableB.Spend END) AS TotalSpend6,
    SUM(CASE WHEN months.RowNum =  7 THEN TableB.Spend END) AS TotalSpend7,
    SUM(CASE WHEN months.RowNum =  8 THEN TableB.Spend END) AS TotalSpend8,
    SUM(CASE WHEN months.RowNum =  9 THEN TableB.Spend END) AS TotalSpend9,
    SUM(CASE WHEN months.RowNum = 10 THEN TableB.Spend END) AS TotalSpend10,
    SUM(CASE WHEN months.RowNum = 11 THEN TableB.Spend END) AS TotalSpend11,
    SUM(CASE WHEN months.RowNum = 12 THEN TableB.Spend END) AS TotalSpend12
FROM customers
CROSS JOIN months
LEFT JOIN TableA
    ON customers.CustomerID = TableA.CustomerID
    AND months.EndOfMonth = TableA.AsOfDate
LEFT JOIN TableB
    ON TableA.InternalID = TableB.InternalID
    AND TableA.AsOfDate = TableB.AsOfDate
GROUP BY customers.CustomerID
ORDER BY customers.CustomerID ASC;;

Here's a db<>fiddle

If you want to include the last trading day of each month in the result you could change the SELECT list to -

SELECT
    customers.CustomerID,
    MAX(CASE WHEN months.RowNum =  1 THEN months.EndOfMonth END) AS `Month1`,
    SUM(CASE WHEN months.RowNum =  1 THEN TableB.Spend END) AS `TotalSpend1`,
    MAX(CASE WHEN months.RowNum =  2 THEN months.EndOfMonth END) AS `Month2`,
    SUM(CASE WHEN months.RowNum =  2 THEN TableB.Spend END) AS `TotalSpend2`,
    ...

Answer 2

You can pivot on the Year-Month in MS Sql Server.

SELECT *
FROM (
  SELECT 
    CustomerID
  , FORMAT(MAX(AsOfDate), 'yyyy-MM')+' Total Spend' AS Col
  , SUM(Spend) AS TotalSpend
  FROM
  (
    SELECT 
      t1.*
    , t2.Spend
    , t2.StoreNum
    , rnk = DENSE_RANK() OVER (PARTITION BY t1.InternalID, EOMONTH(t1.AsOfDate) ORDER BY t1.AsOfDate DESC)
    FROM Table1 t1
    JOIN Table2 t2
      ON t2.InternalID = t1.InternalID
     AND t2.AsOfDate = t1.AsOfDate
    WHERE t1.AsOfDate >= DATEADD(year,-1,DATEADD(month,-1,DATEADD(day,1,EOMONTH(GETDATE()))))
      AND t2.AsOfDate >= DATEADD(year,-1,DATEADD(month,-1,DATEADD(day,1,EOMONTH(GETDATE()))))
  ) q
  WHERE rnk = 1
  GROUP BY CustomerID, EOMONTH(AsOfDate)
) Src
PIVOT (
  SUM(TotalSpend)
  FOR Col IN (
    [2021-11 Total Spend]
  , [2021-10 Total Spend]
  , [2021-09 Total Spend]
  , [2021-08 Total Spend]
  )
) Pvt
ORDER BY CustomerID;