How do I superimpose rows in my ResultSet for transactions that never occurred?-CodePudding

Here's my query with the results of the query below...

DROP TABLE IF EXISTS #test_grouping3;
DROP TABLE IF EXISTS #first_day_of_month;

CREATE TABLE #test_grouping3 (
    Customer VARCHAR(1),
    STOTALAMOUNT DECIMAL (10,2),
    UPOSTDATE DATETIME
)

INSERT INTO #test_grouping3
    VALUES ('A', 50.0, '2020-01-01')
INSERT INTO #test_grouping3
    VALUES ('B', 10.0, '2020-02-01')
INSERT INTO #test_grouping3
    VALUES ('A', 25.0, '2020-03-01')

CREATE TABLE #first_day_of_month (
    FirstDateOfMonth DATETIME
)

INSERT INTO #first_day_of_month
    VALUES ('2020-01-01')
INSERT INTO #first_day_of_month
    VALUES ('2020-02-01')
INSERT INTO #first_day_of_month
    VALUES ('2020-03-01')
INSERT INTO #first_day_of_month
    VALUES ('2020-04-01')

SELECT
a.Customer
,dt.FirstDateofMonth AS The_UPOSTDATE
,ISNULL(SUM(a.STOTALAMOUNT), 0) AS TransTotal
FROM #test_grouping3 a
FULL JOIN (SELECT
        FirstDateOfMonth
    FROM #first_day_of_month
    WHERE 1 = 1) dt
    ON a.UPOSTDATE = dt.FirstDateofMonth
WHERE 1 = 1
GROUP BY Customer
,dt.FirstDateofMonth
ORDER BY Customer
, dt.FirstDateofMonth

RESULTS

Customer	The_UPOSTDATE	TransTotal
NULL	2020-04-01	0.00
A	2020-01-01	50.00
A	2020-03-01	25.00
B	2020-02-01	10.00

This is the desired result...

Customer	The_UPOSTDATE	TransTotal
A	2020-01-01	50.00
A	2020-02-01	0.00
A	2020-03-01	25.00
A	2020-04-01	75.00 <-- Notice this is the SUM of Customer A) 2020-01-01 through 2020-04-01
B	2020-01-01	0.00
B	2020-02-01	10.00
B	2020-03-01	0.00
B	2020-04-01	10.00 <-- Notice this is the SUM of Customer B) 2020-02-01 through 2020-04-01

I don't know how to deal with the rows where there was no transaction, i.e, Customer A had no transaction in 2020-02-01 yet, the TransTotal is 0.00. Using the ISNULL(SUM()) handles the TransTotal amount, but I don't know how to get each Customer to not be NULL. Finally, I would like to have a Row where it has summed up all the previous rows, i.e. Customer A had no transaction in 2020-04-01, yet TransTotal is the SUM of rows 2020-01 through 2020-03-01. Please advise.

CodePudding user response：

for each custoemr per each month, you can see below code: for the SUM of Customer amount from 2020-02-01 through 2020-04-01, have no idea, it may need to use union.


SELECT
dt.Customer
,dt.FirstDateofMonth AS The_UPOSTDATE
,ISNULL(SUM(a.STOTALAMOUNT), 0) AS TransTotal
FROM #test_grouping3 a
FULL JOIN 
( -- cross join customer and month to create full customer and month pair.
    SELECT  mon.FirstDateOfMonth, c.Customer
    FROM #first_day_of_month mon, (select distinct customer from #test_grouping3) c
    WHERE 1 = 1) dt ON a.UPOSTDATE = dt.FirstDateofMonth and a.Customer = dt.Customer
WHERE 1 = 1
GROUP BY dt.Customer
,dt.FirstDateofMonth
ORDER BY Customer
, dt.FirstDateofMonth

I guess you want to aggreate the sum per month, then you can use:

SELECT  
mon.FirstDateOfMonth
, c.Customer
,(select isnull(sum(STOTALAMOUNT),0) from #test_grouping3 t where t.UPOSTDATE <= mon.FirstDateOfMonth and t.Customer = c.Customer) AS TransTotal
FROM #first_day_of_month mon
, (select distinct customer from #test_grouping3) c