Recursive Group By & Padding Results [Resolved]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Recursive Group By & Padding Results [Resolved]

Author

Topic

chenko
Starting Member

24 Posts

Posted - 2007-04-16 : 11:43:34

Here is what I have so far

SELECT MIN(tblDayDetail.intIn) / 60 AS ClockInHour INTO #HoursClockedIn
FROM tblDayDetail
WHERE dteDate = '13/04/2007'
GROUP BY intEmpID

SELECT ClockInHour, Count(*) As Total
FROM #HoursClockedIn
GROUP BY ClockInHour

I do a group by to get multiple clockin times from an employee per day to get the first one, I then want to group them all per hour and get a count of how many clocked in at that hour. I cannot figure how I can do this by using a new group by. For now I have just used a temporary table and feel as if I have cheated.

Whats the best way to do this?

Next part is it returns a list of hours, and the amount clocked in durning that hour, some hours are blank so it wont return it so I get hours of like 3, 4, 5, 6, 13, 14. But what would be nicer is if it would list 0 1 2 3, all of them with 0 for the count. Silly idea to try?

thanks :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 11:46:26

Please post sample data and expected output.

Peter Larsson
Helsingborg, Sweden

chenko
Starting Member

24 Posts

Posted - 2007-04-16 : 12:00:32

Well the current test table outputs


ClockInHour    Total
3              1
4              8
5              11
6              11
7              5
10             2
11             3
12             7
13             6

Where as I want it to output all 24 hours


ClockInHour    Total
0              0
1              0
2              0
3              1
4              8
5              11
.....
22             0
23             0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 12:58:52

SELECT x.number as clockinhour, coalesce(t1.total, 0) as Total
from (select number from master..spt_values where name is null and number between 0 and 23) as x
left join table1 as t1 on t1.clockinhour = x.number
order by x.number

Peter Larsson
Helsingborg, Sweden

chenko
Starting Member

24 Posts

Posted - 2007-04-16 : 17:31:38

Forgive me as I dont understand this.

Table1 needs to be my temp table? #ClockedInHours (alias t1), where is t1.total from? since I did not have this field created (its not been possible for me to create the 2nd group by to get this total, the first issue above)

I find that if I used my 2nd peice of code at the top, just inserted a null value with a clocked in hour for 0 to 23, and -1 from the count i could get it, but im not sure about inserting 24 records each time (unless there is a better way along these lines?)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-17 : 01:47:33

[code]SELECT x.number AS ClockInHour,
COALESCE(t1.Total, 0) AS Total
FROM (
SELECT Number
FROM master..spt_values
WHERE Name IS NULL
AND Number BETWEEN 0 AND 23
) AS x
LEFT JOIN (
SELECT ClockInHour,
COUNT(*) AS Total
FROM (
SELECT MIN(tblDayDetail.intIn) / 60 AS ClockInHour
FROM tblDayDetail
WHERE dteDate = '13/04/2007'
GROUP BY intEmpID
) AS y
GROUP BY ClockInHour
) AS t1 on t1.ClockInHour = x.Number
ORDER BY x.Number[/code]

Peter Larsson
Helsingborg, Sweden

chenko
Starting Member

24 Posts

Posted - 2007-04-17 : 11:33:18

Thanks for that, works perfectly and understood fully. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources