CODEWITHSUNDEEP
sql-serversql-server-2008-r2sql-server-2012
user3224208
user3224208

Reputation: 1027

Show zero value when no data available in date sequence

I have a table with two columns sale_date and amount:

sale_date     amount
-------------------
05/01/2014    400
05/02/2014    350
05/04/2014    430
.
.

I want the output to include a value of zero for amount if there are no sales for a particular date.

Desired output:

sale_date    amount
-------------------
05/01/2014    400
05/02/2014    350
05/03/2014      0
05/04/2014    430
.
.

Upvotes: 1

Views: 236

Answers (1)

Sarath Subramanian
Sarath Subramanian

Reputation: 21281

Consider this is your table

CREATE TABLE #TEMP(sale_date DATE, AMOUNT NUMERIC(18,2))

INSERT INTO #TEMP 
SELECT '05/01/2014',   400
UNION ALL
SELECT '05/02/2014',    350
UNION ALL
SELECT '05/04/2014',    430

I have written the logic inside query

 DECLARE @MAXDATE DATE
 SELECT @MAXDATE = MAX(sale_date) FROM #TEMP

; WITH  CTE as
 (
     -- Select minimum date
     SELECT MIN(sale_date) as DATES
     FROM #TEMP    
     UNION ALL
     -- Increments month recursively till maximum date
     SELECT DATEADD(MONTH,1,DATES)
     FROM    CTE C1     
     WHERE   DATES < @MAXDATE
 )
 SELECT DISTINCT DATES,ISNULL(T1.AMOUNT ,0)AMOUNT
 FROM    CTE C1
 LEFT JOIN #TEMP T1 ON C1.DATES=T1.sale_date

Upvotes: 2

Related Questions