How to Add missing Months and display TotalPayment = 0-CodePudding

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

CodePudding user response：

To generate every combination of 'brgy' and month this CROSS JOIN's a virtual table called 'months_cte' containing the sequence 1, 2, ..., 12 with DISTINCT values of 'brgy'. Something like this

drop TABLE if exists #brgypayment;
go
CREATE TABLE #brgypayment (
  brgy VARCHAR(40),
  month_dt date,
  TotalPayment decimal(10,2));

INSERT INTO #brgypayment VALUES
    ('barangay1', '2021-01-01', 5345.00)
  , ('barangay1', '2021-02-01', 123.00)
  , ('barangay1', '2021-05-01',     8000.00)
  , ('barangay1', '2021-07-01',   34.00)
  , ('barangay2', '2021-09-01', 111.00)
  , ('barangay2', '2021-11-01',    2000.00)
  , ('barangay2', '2021-12-01',  134.00);

with
unq_brgy_cte(brgy) as (
    select distinct brgy
    from #brgypayment),
months_cte(n) as (
    select * 
    from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(n))
select ub.brgy, datename(month, dateadd(month, m.n-1, 0)) month_dt,
       isnull(bp.TotalPayment, 0) TotalPayment
from unq_brgy_cte ub
     cross join months_cte m
     left join #brgypayment bp on ub.brgy=bp.brgy
                                  and m.n=month(bp.month_dt)
order by ub.brgy, m.n;

CodePudding user response：

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 |
 ----------- ----------- ---------