CODEWITHSUNDEEP
sqlsql-serversql-server-2008
whoisearth
whoisearth

Reputation: 4170

how to include dates where no value

I have a query like so - 

select CAST(jobrun_proddt as Date) as 'Date', COUNT(*) as 'Error Occurred Jobs' from jobrun
where jobrun_orgstatus = 66 and jobmst_type <> 1
group by jobrun_proddt
order by jobrun_proddt desc

Not every date will have a count. What I want to be able to do is the dates that are blank to have a count of 0 so the chart would look like this - 

2014-11-18  1
2014-11-17  0
2014-11-16  0
2014-11-15  0
2014-11-14  0
2014-11-13  1
2014-11-12  0
2014-11-11  1

Currently it's not returning the lines where there's no count.

2014-11-18  1
2014-11-13  1
2014-11-11  1

edit to add that the jobrun table DOES have all the dates, just some dates don't have the value I'm searching for.

Upvotes: 0

Views: 69

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you have data for all dates, but the other dates are being filtered by the where clause, then you can use conditional aggregation:

select CAST(jobrun_proddt as Date) as [Date],
       SUM(CASE WHEN jobrun_orgstatus = 66 and jobmst_type <> 1 THEN 1 ELSE 0
           END) as [Error Occurred Jobs]
from jobrun
group by jobrun_proddt
order by jobrun_proddt desc

Upvotes: 2

torun
torun

Reputation: 480

firstly, you must specify a particular date range then you should connect your table with "left join" 

DECLARE @start DATE, @end DATE;
SELECT @start = '20141114', @end = '20141217';

;WITH n AS 
(
  SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) 
    n = ROW_NUMBER() OVER (ORDER BY [object_id])
  FROM sys.all_objects
)

SELECT 'Bob', DATEADD(DAY, n-1, @start) as 'xdate' FROM n;


select CAST(jobrun_proddt as Date) as 'Date', COUNT(*) as 'Error Occurred Jobs' from jobrun
left join n on DATEADD(DAY, n-1, @start) = jobrun.jobrun_proddt
where jobrun_orgstatus = 66 and jobmst_type <> 1
group by jobrun_proddt
order by jobrun_proddt desc

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Try this. Use Recursive CTE to generate the Dates.

WITH cte
     AS (SELECT CONVERT(DATE, '2014-11-18') AS dates --Max date
         UNION ALL
         SELECT Dateadd(dd, -1, dates)
         FROM   cte
         WHERE  dates > '2014-11-11') -- Min date
SELECT a.dates,
       Isnull([Error_Occurred_Jobs], 0)
FROM   cte a
       LEFT JOIN (SELECT Cast(jobrun_proddt AS DATE) AS Dates,
                         Count(*)                    AS [Error_Occurred_Jobs]
                  FROM   jobrun
                  WHERE  jobrun_orgstatus = 66
                         AND jobmst_type <> 1
                  GROUP  BY jobrun_proddt) B
              ON a.dates = b.dates 
         Order by a.dates desc

Upvotes: 1

Jason W
Jason W

Reputation: 13179

You'll have to join to a table or list of generated sequential dates using OUTER JOIN so that for the dates with no matches to your jobrun_orgstatus you can use ISNULL or COALESCE to return 0.

Upvotes: 0

Related Questions