November 6, 2014 at 6:47 am
Sorry if the title is a bit vague - this is the age old problem of creating the missing records in a date/time range.
However, I need to return different groups for each span of records.
Ok, here's some data....
aaa1
aaa7
bbb2
bbb5
bbb6
The numbers are the hour of the day.
I need to return
aaa 0 0
aaa 1 1
aaa 2 0
aaa 3 0
...
bbb 0 0
bbb 1 0
bbb 2 1
...
and so on.
I've got a numbers table and I can left join with it but I just get nulls for the missing hours instead of having it as above.....I can't think of a way of repeating the groups for each of the 'missing' hours - other than creating a length insert statement to fill in the gaps....unless that is the only way of doing it.
Any help or pointers would be greatly appreciated.
Many thanks
Kan
November 6, 2014 at 8:07 am
Unless I'm not understanding what you are doing you can just use ISNULL to deal with the NULLs like this:
ISNULL (<value>, 0)
-- Itzik Ben-Gan 2001
November 7, 2014 at 1:53 am
I may not have explained myself properly
If I just want the sum per hour and join to my numbers table I get what I want:
hour sum
01 1
02 1
03 2
04 0
05 0
06 0
...
but I need to have the group in there as well and then I don't get the records where there is no entry for a particular time
group hour sum
aaa 01 1
aaa 05 1
...
In English terms, I want the sum of all sales per hour per town, with zero's for all the hours where there were no transactions
thanks
November 7, 2014 at 3:01 am
Note: I posted some code, removed it because it didn't work, and then posted this code in its place.
Try something like this:
declare @data table (groupid int, salesperhour tinyint)
declare @groups table (id int, groupname char(3))
declare @hours table (hourofday tinyint)
declare @grouphours table (groupname char(3), hourofday tinyint)
insert into @hours values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
,(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)
insert into @groups values (1,'aaa'),(2,'bbb')
insert into @data values
(1,1),
(1,7),
(2,2),
(2,5),
(2,6)
insert into @grouphours select groupname, hourofday from @groups cross join @hours
select gh.groupname, gh.hourofday, isnull(count(d.salesperhour),0) as SalesPerHour from @data d
left join @groups g on d.groupid = g.id
right join @grouphours gh on g.groupname = gh.groupname and d.salesperhour = gh.hourofday
group by gh.groupname, gh.hourofday
order by gh.groupname, gh.hourofday
In order to get this to work we need a worktable listing the hours in the day and another worktable cross-referencing this to the groups so that we have an entry for each group and each hour.
November 7, 2014 at 3:47 am
Oki doki - let me give that a try and see what happens!
Thanks for suggesting something....
November 7, 2014 at 4:12 am
Here is another way of achieving by using TALLY table logic.
DECLARE @dataTABLE (groupid int, salesperhour tinyint)
DECLARE @groups TABLE (id int, groupname char(3))
INSERT INTO @groups VALUES (1,'aaa'),(2,'bbb')
INSERT INTO @dataVALUES
(1,1),
(1,7),
(2,2),
(2,5),
(2,6)
-------- Create a CTE for the 0 to 23 hr
;WITH cTally
AS
(
SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS NUMBER
FROM SYS.all_columns
)
SELECT G.groupname, NUMBER AS hourofday, COUNT(D.salesperhour) AS SalesPerHour
FROM cTally T
CROSS JOIN @groups G
LEFT JOIN @data D ON D.groupid = G.id AND T.NUMBER = D.salesperhour
GROUP BY G.groupname, NUMBER
November 7, 2014 at 4:57 am
Perfect - works a treat. Amended it to fit my data set, even expanded it for a few more groups and voila.
Nice one, very much appreciated!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply