locked
Query To Count Records Matching on a Date (Including Dates with No Records) RRS feed

  • Question

  • User-1842721478 posted

    So, I'm writing a query to provide input for a graph that details records matching certain criteria in the previous 30 days.  The query will return me all records that exist, however, in an attempt to make the input easier I would like the query to return all dates, even if no records match.  This is SQL Server 2008.  The following is the query I am using currently.

     

    select convert(char(10), CreatedDateTime, 111) as date into #temp_prio1_output from ticket
    
    where 
    	CreatedDateTime >= dateadd(day,datediff(day,0,GetDate())- 30,0)
    	AND CreatedDateTime <= dateadd(day,datediff(day,0,GetDate()),0)
    
    	AND Priority = '1'
    	AND ExternalInternal = 1
    	AND AssocProblem = 0
    	
    
    select date as Date, count(*) as Count from #temp_prio1_output
    
    group by date
    order by date

    Example of output would be... say....

    2012/12/19 1
    2012/12/20 1
    2012/12/24 2

    As you can see, there are gaps where no records match.  What would be optimal would be to see 2012/12/21 0; 2012/12/22 0; 2012/12/23 0; etc.

    I've scoured quite a few different forums and found quite a few ideas, none of them seem to be working.  If anyone has an idea that would provide the output I am looking for, I would greatly appreciate it!

    Thursday, January 17, 2013 7:14 PM

Answers

  • User223678428 posted

    If you aren't worried about using an ISNULL function, it may serve it's purpose here.  I know people say you shouldn't use it for various reasons, but if your data isn't that large and you only do it for the last 30 days, it may work for you.

    select convert(char(10), CreatedDateTime, 111) as date into #temp_prio1_output from ticket
    
    where
            CreatedDateTime >= dateadd(day,datediff(day,0,GetDate())- 30,0)
            AND CreatedDateTime <= dateadd(day,datediff(day,0,GetDate()),0)
    
            AND Priority = '1'
            AND ExternalInternal = 1
            AND AssocProblem = 0
            
    
    select date as Date, ISNULL(count(*),0) as Count from #temp_prio1_output
    
    group by date
    order by date



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 23, 2013 8:46 AM

All replies

  • User1553110397 posted

    I think you can solve it by  first filling a table with days from 1 to 30 by looping and then left outer joining your current table on dates .

    Thursday, January 17, 2013 11:09 PM
  • User3866881 posted

    Hi,

    Before you importing all the data contents into a DataTable, you should check whether you've really got records by using Select count(*)……,if you have values,just import directly;otherwises please use Select * from …… without any "where" conditions.

    Reguards!

    Saturday, January 19, 2013 8:17 PM
  • User223678428 posted

    If you aren't worried about using an ISNULL function, it may serve it's purpose here.  I know people say you shouldn't use it for various reasons, but if your data isn't that large and you only do it for the last 30 days, it may work for you.

    select convert(char(10), CreatedDateTime, 111) as date into #temp_prio1_output from ticket
    
    where
            CreatedDateTime >= dateadd(day,datediff(day,0,GetDate())- 30,0)
            AND CreatedDateTime <= dateadd(day,datediff(day,0,GetDate()),0)
    
            AND Priority = '1'
            AND ExternalInternal = 1
            AND AssocProblem = 0
            
    
    select date as Date, ISNULL(count(*),0) as Count from #temp_prio1_output
    
    group by date
    order by date



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, January 23, 2013 8:46 AM