richecker
asked on
Need grouping to show "0" for certain fields
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?
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
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
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(h
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
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(h
) 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
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
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
ASKER
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?
ASKER
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.
what did you modify it to, can you please show?
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 (
select tt.period, counts, hh_created
from dbo.MakeTimeTable( '00:00', '23:59' ) tt left join (
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The time table should contain all the times you want to monitor.