Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1875
Members Online:
1
Guests Online:
78

Total Post History
Posts:
81446
Topics:
18714

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
konam534As

Other

FAQ
All times are UTC [ DST ]

Google Ads

Query To Count Records Matching on a Date (Including Dates w

Microsoft SQL Server
Please wait...

Query To Count Records Matching on a Date (Including Dates w

Postby marketfresh on Fri Jan 18, 2013 1:33 am

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! If not possible, that sucks, oh well.
.
marketfresh
Newbie
Newbie
 
Posts: 2
Joined: Fri Jan 18, 2013 1:29 am
Unrated

Re: Query To Count Records Matching on a Date (Including Dates w

Postby SQLDenis on Fri Jan 18, 2013 1:58 am

WHat you need is to have a calendar table


Here is an easy way to create such a table
  1. create table Calendar (SomeDate date not null primary key)
  2. GO
  3.  
  4. insert Calendar
  5. select dateadd(day,number,'20130101')
  6. from master..spt_values where type ='p'



Now let's see what we have in that table
  1. select min(SomeDate), max(SomeDate)
  2. from Calendar


2013-01-01 2018-08-10

Those are the min and max dates


Now let's create some fake order table for demo purposes
  1. create table SomeData (OrderDate date, SomeCol int)
  2. insert SomeData values('2013-01-01',1)
  3. insert SomeData values('2013-01-01',1)
  4. insert SomeData values('2013-01-02',1)
  5. insert SomeData values('2013-01-02',1)
  6. insert SomeData values('2013-01-03',1)
  7. insert SomeData values('2013-01-05',1)
  8. insert SomeData values('2013-01-05',1)
  9. insert SomeData values('2013-01-07',1)
  10. insert SomeData values('2013-01-08',1)
  11. insert SomeData values('2013-01-09',1)
  12. insert SomeData values('2013-01-09',1)



Here is what the data looks like
  1. select OrderDate, count(somecol) as SOmeCount
  2. from SomeData
  3. group by OrderDate


OrderDate	SOmeCount
2013-01-01 2
2013-01-02 2
2013-01-03 1
2013-01-05 2
2013-01-07 1
2013-01-08 1
2013-01-09 2


As you can see we are missing the 4th and the 6th

Now all you have to do is a left join with the calendar table
  1. -- grab min and max dates or supply range yourself
  2. declare @mindate date,@maxdate date
  3. select @mindate =min(OrderDate),@maxdate = max(OrderDate)
  4. from SomeData
  5.  
  6. --here is the query
  7. select c.SomeDate,coalesce(x.SOmeCount,0) as TheCount
  8. from Calendar c
  9. left join (
  10. select OrderDate, count(somecol) as SOmeCount
  11. from SomeData
  12. group by OrderDate) x
  13. on c.SomeDate = x.OrderDate
  14. where c.SomeDate between @mindate  and @maxdate
  15. order by c.SomeDate



Output
SomeDate	TheCount
2013-01-01 2
2013-01-02 2
2013-01-03 1
2013-01-04 0
2013-01-05 2
2013-01-06 0
2013-01-07 1
2013-01-08 1
2013-01-09 2


Makes sense? post back if you have questions

NB: I started the calendar from Jan 1st 2013, you might want to start at an earlier day, this table is really lightweight, it uses only 3 bytes per row so even with 100 years of data it is pretty small
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: Query To Count Records Matching on a Date (Including Dates w

Postby gmmastros on Fri Jan 18, 2013 2:10 am

Using Denis's calendar table, your query would be...

  1. select AllDates.SomeDate as Date,
  2.        count(#temp_prio1_output.date) as Count
  3. from   (
  4.        Select  SomeDate
  5.        From    Calendar
  6.        Where   SomeDate >= dateadd(day,datediff(day,0,GetDate())- 30,0)
  7.                And SomeDate <= dateadd(day,datediff(day,0,GetDate()),0)
  8.        ) As AllDates
  9.        Left Join #temp_prio1_output
  10.          On AllDates.SomeDate = #temp_prio1_output.date
  11. group by SomeDate
  12. order by SomeDate
  13.  
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2367
Joined: Tue Oct 09, 2007 5:19 pm

Re: Query To Count Records Matching on a Date (Including Dates w

Postby SQLDenis on Fri Jan 18, 2013 3:00 am

I decided to make a blogpost out of it, see here: Displaying missing dates by utilizing a calendar table
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond

Re: Query To Count Records Matching on a Date (Including Dates w

Postby marketfresh on Sat Jan 19, 2013 2:38 pm

Thank you very much for the feedback, was a great starting point. I'd like to share what I ended up going with, the basis being I do not have rights on the server to create permanent tables and I wanted the dates added to my temporary table automatically each time the query is executed, in order to keep them current.

  1.  
  2. CREATE TABLE #prio1_temp_1 (
  3.                 [date] [char](10) NULL,
  4.                 [count] [int] NULL )
  5.  
  6. insert into #prio1_temp_1(date) select DISTINCT convert(char(10), CreatedDateTime, 111) from TICKET
  7.     where
  8.     CreatedDateTime >= dateadd(day,datediff(day,0,GetDate())- 30,0)
  9.     AND CreatedDateTime <= dateadd(day,datediff(day,0,GetDate()),0)
  10.  
  11. select convert(char(10), CreatedDateTime, 111) as date into #prio1_temp_2 from TICKET
  12.  
  13.     where
  14.     CreatedDateTime >= dateadd(day,datediff(day,0,GetDate())- 30,0)
  15.     AND CreatedDateTime <= dateadd(day,datediff(day,0,GetDate()),0)
  16.     AND Priority = '1'
  17.     AND ExternalInternal = 1
  18.     AND AssocProblem = 0
  19.     --Input any priority number to populate based on that, 1/2/3
  20.     --ExternalInternal determines whether this is an internal or external ticket
  21.         --ExternalInternal = 1 - External   ExternalInternal = 0 - Internal
  22.     --AssocProblem determines if the ticket is associated with a PR
  23.         --AssocProblem = 0 for not associated   AssocProblem = 1 for associated
  24.  
  25. SELECT date AS date, count(*) AS count into #prio1_temp_3 from #prio1_temp_2
  26. group by date
  27. order by date
  28.  
  29. UPDATE #prio1_temp_1
  30. SET COUNT = b.count
  31. from #prio1_temp_1 A, #prio1_temp_3 B
  32. where A.date = B.date
  33.  
  34. UPDATE #prio1_temp_1 SET COUNT = 0 WHERE COUNT IS NULL
  35.  
  36. SELECT * FROM #prio1_temp_1
  37.  
  38. DROP TABLE #prio1_temp_2
  39. DROP TABLE #prio1_temp_3
  40.  
  41.  



Let me know your opinions and if there is anywhere you would change anything to improve performance or to ensure best practices are being followed.

Thanks again!
marketfresh
Newbie
Newbie
 
Posts: 2
Joined: Fri Jan 18, 2013 1:29 am
Unrated