CODEWITHSUNDEEP
sql-server
user2297683
user2297683

Reputation: 406

How to display monthly balance for month with no balance SQL Server

I have the following block of code which I am using to query a SQL Server database that summarizes all balances for each of the previous 13 months. It works pretty good, but there are a few months when there were no balances to report. These months are not displaying which I do need. At this point, I am at a loss for what to try next.

DECLARE @StartDate DATE, @EndDate DATE;

SELECT 
    @StartDate = CONVERT(VARCHAR(11), DATEADD(month, -13, GETDATE())),
    @EndDate = CONVERT(VARCHAR(11), DATEADD(month, 0, GETDATE()));

;WITH d(d) AS
(
    SELECT 
        DATEADD(MONTH, n, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate), 0))
    FROM 
        (SELECT TOP (DATEDIFF(MONTH, @StartDate, @EndDate) + 1)
             n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
         FROM 
             sys.all_objects ORDER BY [object_id]) AS n
)
SELECT
    FORMAT(d.d, 'MMM yy') AS Purchase_Date,
    CAST(ROUND(SUM(lm.BALANCE), 0) AS FLOAT) AS Balance
FROM 
    d 
LEFT OUTER JOIN 
    dbo.purchases AS lm ON lm.purchase_date >= d.d
                        AND lm.purchase_date < DATEADD(MONTH, 1, d.d)
WHERE 
    lm.Buyer_code = 'FirstTime'
    AND lm.PROGRAM_ID = 'NewBuyers'
GROUP BY 
    d.d
ORDER BY 
    d.d

Upvotes: 0

Views: 291

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35613

You are overriding the LEFT JOIN by the where clause, which requires that every row have certain values from the left joined table. In effect it is equivalent to an inner join. You need to allow rows from d to survive into the result, which you can do by using the wanted conditions directly in the LEFT JOIN:

SELECT
    FORMAT( d.d, 'MMM yy' )                        AS Purchase_Date
  , CAST( ROUND( SUM( lm.BALANCE ), 0 ) AS float ) AS Balance
FROM d
LEFT OUTER JOIN DBO.purchases AS lm ON lm.purchase_date >= d.d
    AND lm.purchase_date < DATEADD( MONTH, 1, d.d )
    AND lm.Buyer_code = 'FirstTime'
    AND lm.PROGRAM_ID = 'NewBuyers
GROUP BY
    d.d
ORDER BY
    d.d

Upvotes: 2

Related Questions