How to Add missing Months and display TotalPayment as 0
I already try row_number but it seems not working
Query:
select brgy,
datename(month,month_dt) as month_dt,
TotalPayment
from brgypayment
order by brgy, month(month_dt)
Results:
brgy month_dt TotalPayment
barangay1 January 5345.00
barangay1 February 34232.00
barangay1 March 53454.00
barangay1 April 25234.00
barangay1 May 43224.00
barangay1 June 14.00
barangay1 July 141.00
barangay1 August 4415.00
barangay1 September 5455.00
barangay1 October 1235.00
barangay1 November 53535.00
barangay1 December 1661.00
barangay2 January 97859.00
barangay2 February 5673.00
barangay2 March 78764.00
barangay2 April 84673.00
barangay2 May 63468.00
barangay2 June 6365.00
barangay2 July 2145.00
barangay2 August 4415.00
barangay2 September 62652.00
barangay2 October 24521.00
barangay3 January 52345.00
barangay3 February 15454.00
Target Results:
brgy month_dt TotalPayment
barangay1 January 5345.00
barangay1 February 34232.00
barangay1 March 53454.00
barangay1 April 25234.00
barangay1 May 43224.00
barangay1 June 14.00
barangay1 July 141.00
barangay1 August 4415.00
barangay1 September 5455.00
barangay1 October 1235.00
barangay1 November 53535.00
barangay1 December 1661.00
barangay2 January 97859.00
barangay2 February 5673.00
barangay2 March 78764.00
barangay2 April 84673.00
barangay2 May 63468.00
barangay2 June 6365.00
barangay2 July 2145.00
barangay2 August 4415.00
barangay2 September 62652.00
barangay2 October 24521.00
barangay2 November 0.00
barangay2 December 0.00
barangay3 January 52345.00
barangay3 February 15454.00
barangay3 March 0.00
barangay3 April 0.00
barangay3 May 0.00
barangay3 June 0.00
barangay3 July 0.00
barangay3 August 0.00
barangay3 September 0.00
barangay3 October 0.00
barangay3 November 0.00
barangay3 December 0.00
Advertisement
Answer
Assuming you have a list of distinct brgy names for the report, you can generate your months dynamically and then CROSS JOIN them with the list of brgy’s of interest. Now you can LEFT JOIN that list with your data to produce all brgy/month pairs, regardless of whether your data has every month for each brgy.
This is just one dynamic approach. You could even create a static list of months to use.
Here’s a working test case:
Working Test Case for SQL Server / tsql (Updated)
WITH months (month, monthname) AS (
SELECT 1 , DATENAME(month, DATEADD(month, 0, 0)) UNION ALL
SELECT month+1, DATENAME(month, DATEADD(month, month, 0))
FROM months
WHERE month < 12
)
, list AS (
SELECT b.*, m.*
FROM months AS m
, (SELECT DISTINCT bname FROM brgydata) AS b
)
SELECT l.bname, l.monthname, COALESCE(b.amount, 0.0) AS amount
FROM list AS l
LEFT JOIN brgydata AS b
ON b.bname = l.bname
AND b.monthname = l.monthname
ORDER BY l.bname, l.month
;
See the following test case for a little sample:
The data:
+-----------+-----------+---------+ | bname | monthname | amount | +-----------+-----------+---------+ | barangay1 | January | 5345.00 | | barangay1 | February | 123.00 | | barangay1 | May | 8000.00 | | barangay2 | January | 34.00 | | barangay2 | February | 111.00 | | barangay2 | June | 2000.00 | | barangay2 | October | 134.00 | +-----------+-----------+---------+
The result:
+-----------+-----------+---------+ | bname | monthname | amount | +-----------+-----------+---------+ | barangay1 | January | 5345.00 | | barangay1 | February | 123.00 | | barangay1 | March | 0.00 | | barangay1 | April | 0.00 | | barangay1 | May | 8000.00 | | barangay1 | June | 0.00 | | barangay1 | July | 0.00 | | barangay1 | August | 0.00 | | barangay1 | September | 0.00 | | barangay1 | October | 0.00 | | barangay1 | November | 0.00 | | barangay1 | December | 0.00 | | barangay2 | January | 34.00 | | barangay2 | February | 111.00 | | barangay2 | March | 0.00 | | barangay2 | April | 0.00 | | barangay2 | May | 0.00 | | barangay2 | June | 2000.00 | | barangay2 | July | 0.00 | | barangay2 | August | 0.00 | | barangay2 | September | 0.00 | | barangay2 | October | 134.00 | | barangay2 | November | 0.00 | | barangay2 | December | 0.00 | +-----------+-----------+---------+