CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Alex
Alex

Reputation: 143

Show Dates that have no values for selected column

I have the following query which counts the number of items created on a particular date in the last 10 days

SELECT 
    CONVERT (DATE, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688) AS 'Logged Date', 
    Count (*) AS 'Total' 
FROM   
    MTV_System$WorkItem$Incident 
WHERE  
    CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 >= DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 10, 0) 
GROUP BY 
    CONVERT(DATE, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688)

How do I get this to show the dates which have no values present (i.e. get every date value for the last 10 days, return the count if there is data or 0 if none). Using SQL Server 2012.

Upvotes: 1

Views: 78

Answers (3)

André Kops
André Kops

Reputation: 2713

Not that there is anything wrong with BeanFrog's answer, but if you don't want to use a recursive cte you could do this:

CREATE TABLE MTV_System$WorkItem$Incident (id int PRIMARY KEY, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 datetime)
INSERT INTO MTV_System$WorkItem$Incident VALUES (1, '20151201')
INSERT INTO MTV_System$WorkItem$Incident VALUES (2, '20151126')
INSERT INTO MTV_System$WorkItem$Incident VALUES (3, '20151127')
INSERT INTO MTV_System$WorkItem$Incident VALUES (4, '20151127')

SELECT 
    ReportDate AS 'Logged Date', 
    Count (CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688) AS 'Total'
FROM (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 0, 0) AS ReportDate
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 1, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 2, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 3, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 4, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 5, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 6, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 7, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 8, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 9, 0)
    UNION SELECT DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 10, 0)
) AS Dates
LEFT JOIN MTV_System$WorkItem$Incident ON ReportDate = CONVERT(DATE, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688)
GROUP BY ReportDate

BeanFrog's answer has the advantage of being able to easily change the number of days though.

Upvotes: 0

Kane
Kane

Reputation: 16802

Similar to BeanFrog's answer but a little shorter

-- sample data for testing
declare @MTV_System$WorkItemIncident table (
    [CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688] DATE,
    [Total] INT
);
INSERT INTO @MTV_System$WorkItemIncident VALUES ('2015-11-23', 23);
INSERT INTO @MTV_System$WorkItemIncident VALUES ('2015-11-21', 21);
INSERT INTO @MTV_System$WorkItemIncident VALUES ('2015-11-30', 30);

-- now the query
WITH TableA (LoggedDate) AS (
    SELECT TOP 10 CONVERT (DATE, DATEADD(DAY, number * -1, GETDATE())) AS 'LoggedDate' 
    FROM master.dbo.spt_values 
    WHERE name IS NULL
)
SELECT  TableA.[LoggedDate],
        SUM(ISNULL(Data.Total, 0)) AS 'LoggedCount'
FROM TableA
    LEFT JOIN @MTV_System$WorkItemIncident AS Data ON CONVERT (DATE, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688) = TableA.[LoggedDate]
GROUP BY TableA.[LoggedDate]

Upvotes: 2

BeanFrog
BeanFrog

Reputation: 2315

You can write a recursive cte to get the date for the last 10 days into a table as follows:

WITH TableA (StartDate) AS (SELECT  DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 10, 0)),
q as (
    SELECT  StartDate
            , Number = 0
    FROM    TableA
    UNION ALL 
    SELECT  DATEADD(d,1,StartDate) 
            , Number = Number + 1
    FROM    q
    WHERE   10 > Number )

Then join q with your original query, to get a row for every date.

    select q.StartDate, yourtable.Total from q
    left join (
        SELECT 
            CONVERT (DATE, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688) AS 'Logged Date', 
            Count (*) AS 'Total' 
        FROM   
            MTV_System$WorkItem$Incident 
        WHERE  
            CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 >= DATEADD(DAY, DATEDIFF(DAY, 0, Getdate()) - 10, 0) 
        GROUP BY 
            CONVERT(DATE, CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688) 
            ) as yourtable on [Logged Date] = q.StartDate

Upvotes: 3

Related Questions