We help IT Professionals succeed at work.

Need grouping to show "0" for certain fields

170 Views
Last Modified: 2010-03-20
I am attempting to group everything by hour for a sql report. My code works fine provided that every hour has at least one incident. If an hour does not have an incident then the hour will not even show up. I still need that particular hour to show up.
Ideally what I would have would look like this:

00:00 - 01:00              25
01:00 - 02:00              14
02:00 - 03:00              0           Here its displaying what i want... showing zero for the 2:00 hour.

I attempted the left outer self join but that still didnt work for me... maybe I did set it up correctly. Any suggestions?



select   
cast(datepart(hh, a.time_created) as varchar(10)) + ':00 - ' + 
cast(datepart(hh,dateadd(hour,1,a.time_created)) as varchar(10)) + ':00' as times,  
count(*) as counts, datepart(hh, a.time_created) 
from police_event_history as a
left outer join police_event_history as b on a.time_created = b.time_created
inner join call_type on a.call_type = call_type.call_type and b.call_type=call_type.call_type
where b.time_created >= @datea
    and b.time_created<= (dateadd(day, 1, @dateb))
and description = @calltype 
group by  (cast(datepart(hh, a.time_created) as varchar(10)) + ':00 - ' + 
cast(datepart(hh,dateadd(hour,1,a.time_created)) as varchar(10)) + ':00'), 
datepart(hh, a.time_created) 
order by datepart(hh, a.time_created) asc

Open in new window

Comment
Watch Question

Top Expert 2007

Commented:
Add a "time table" before the police_event_history and LEFT JOIN into it.
The time table should contain all the times you want to monitor.
Top Expert 2007

Commented:
Quick question if I may - Is @datea and @dateb going to cover more than 1 day, because the timetable would be hard to work out since you've got no day portion in it
Top Expert 2007

Commented:

select tt.period, counts, hh_created
from dbo.MakeTimeTable( @datea, @dateb ) tt left join (
select  
cast(datepart(hh, a.time_created) as varchar(10)) + ':00 - ' +
cast(datepart(hh,dateadd(hour,1,a.time_created)) as varchar(10)) + ':00' as times,  
count(*) as counts, datepart(hh, a.time_created) as hh_created
from police_event_history as a
left outer join police_event_history as b on a.time_created = b.time_created
inner join call_type on a.call_type = call_type.call_type and b.call_type=call_type.call_type
where b.time_created >= @datea
    and b.time_created<= (dateadd(day, 1, @dateb))
and description = @calltype
group by  (cast(datepart(hh, a.time_created) as varchar(10)) + ':00 - ' +
cast(datepart(hh,dateadd(hour,1,a.time_created)) as varchar(10)) + ':00')
) original on original.times = tt.period
order by hh_created asc

create function dbo.MakeTimeTable( @startDate datetime, @endDate datetime )
returns @tbl table (period char(13)) as
begin
 declare @h1 char(2)
 declare @h2 char(2)
 set @startDate = cast(floor(cast(@startDate as float)) as datetime)
 while @startDate <= @endDate
 begin
  set @h1 = datepart(hh, @startDate)
  set @h2 = datepart(hh, dateadd(hh, 1, @startDate))
  insert into @tbl select @h1 + ':00 - ' + @h2 + ':00'
  select @startDate = dateadd(hh, 1, @startDate)
 end
 return
end
GO

Open in new window

Top Expert 2007

Commented:
please change the first line to

select tt.period, isnull(counts,0), hh_created

You probably just want to
order by tt_period ASC

If you find that you have two 00:00's in your timetable, change line 7 of then function to

 while @startDate < @endDate

Author

Commented:
I had to modify one part of it to include the other grouping datepart(hh, a.time_created) or it would not have run.  I made the change that you made however it is a very intensive query and it never even finished running. This unfortunately wont work. Could I try a union statement with multiple case statements for each argument to pass? Any other ideas?

Author

Commented:
Also yes this is for multiple dates. WIth the way I had it set up I just grouped all the hours together and didnt care about the day part. I only cared about the hour so I did a group by on the hour and just set the dates in the where clause.
Top Expert 2007

Commented:
what did you modify it to, can you please show?
Top Expert 2007

Commented:
In that case, if you cover multiple dates, the timetable generated would be massive. Change the first two lines to

select tt.period, counts, hh_created
from dbo.MakeTimeTable( '00:00', '23:59' ) tt left join (
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.