Group data by hour fill data gaps

[jwaldner]

I have reports that need to do this, and I know how to do it based on a datetime as long as there is data for the hour in question:

So report grouping is based on the hour of the date time then grouped each hour [8.9.10.11.12.1.2,3,4,5,6,7,8] works great.

Where i start to have issues is if there is no data for that hour. then I get a gap. What i need is zeros for the data and the next hour for the group instead of the gap. This report happens to be a cross tab but the issue would crop up on other reports too. Anyone know any tricks to do this...

 

[IdoMillet]

One option is to add an "Hours" table and use an Outer Join...

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[jwaldner]

That is exactly what I ended up doing, i used a union because I had no unique field to use as a join. but it worked: Thanks for the confirmation!

-- loop used to create a table of dates for schedules that span days
declare @day datetime
declare @hour int

set @day = CONVERT(datetime, CONVERT(varchar, @STARTDATE, 112))

declare @scheddates table ([Start] datetime, [End] datetime)
SET @hour = 8

while @day<= CONVERT(datetime, CONVERT(varchar, @ENDDATE, 112))

begin
--insert into @scheddates ([Start], [End]) values (@day, DATEADD(D,1,@day))
while @hour <=17
begin
insert into @scheddates ([Start], [End]) values (DATEADD(HOUR, @hour, @day), DATEADD(D,1,@day))
SET @hour = @hour + 1
end
set @day = DATEADD(D,1,@day)
set @hour = 8
end