Query to group by multiple criteria

[JMac]

I have a SQL 2008 table called linefaultdata that contains the following data

ID FaultCode AssetID StartTime Duration Shift
2670376 8 2 2011-08-19 19:20:30.000 14 AFTERNOONS
2670608 7 2 2011-08-19 21:10:24.000 12 AFTERNOONS
2671183 8 2 2011-08-19 01:10:36.000 22 NIGHTS
2671467 2 2 2011-08-19 03:20:34.000 12 NIGHTS
2672351 3 2 2011-08-19 09:10:30.000 19 DAYS
2673047 3 2 2011-08-19 13:10:45.000 19 DAYS
2675476 8 2 2011-08-19 14:14:54.000 23 DAYS
2678765 7 2 2011-08-19 01:23:43.000 19 NIGHTS
2678654 8 2 2011-08-19 20:01:03.000 21 AFTERNOONS


I'm looking to create a query that would group the data by FaultCode and Shift and by defined time periods in each shift. Here is an example of what I would like to see.

Shift Time Code Count Sum Of Duration
Nights 11pm - 12am
Nights 12am - 1am
Nights 1am - 2am 7 1 19
8 1 22
Nights 2am - 3am
Nights 3am - 4am 2 1 12
Nights 4am - 5am
Nights 5am - 6am
Nights 6am - 7am
Days 7am - 8am
Days 8am - 9am
Days 9am - 10am 3 1 19
Days 11am - 12pm
Days 12pm - 1pm
Days 1pm - 2pm 3 1 19
Days 2pm - 3pm 8 1 23
Afternoons3pm - 4pm
Afternoons4pm - 5pm
Afternoons5pm - 6pm
Afternoons6pm - 7pm
Afternoons7pm - 8pm 8 1 14
Afternoons8pm - 9pm 8 1 21
Afternoons9pm - 10pm 7 1 12
Afternoons10pm - 11pm

Any help would be appreciated.

Thanks

[Erland Sommarskog]

Post by JMac
I'm looking to create a query that would group the data by FaultCode and
Shift and by defined time periods in each shift. Here is an example of
what I would like to see.

You need an hours table, which would be a table with a single tinyint column
with the vaules 0 to 23.

Then you can do

SELECT h.hour, lfd.FaultCode, COUNT(lfd.Duration) AS Count,
SUM(lfd.Duration)
FROM Hours h
LEFT JOIN linefaultdata ldf ON h.Hour = datepart(Hour, lfd.StartTime)

It's not clear how you want to count a fault that extends over an hour
boundary; I could see any such example in the sample data. Here I have
just put it all in the hour the fault started.

[rpresser]

Post by Erland Sommarskog
SELECT h.hour, lfd.FaultCode, COUNT(lfd.Duration) AS Count,
SUM(lfd.Duration)
FROM Hours h
LEFT JOIN linefaultdata ldf ON h.Hour = datepart(Hour, lfd.StartTime)

You seem to have forgotten a GROUP BY.

And why is it better to use an hours table than to just group by datepart(Hour, lfd.StartTime)?

Also, how can you sum durations?

[rpresser]

Sorry. reading the original in Google Groups, I missed that the duration column is actually an integer.

[rpresser]

Post by rpresser
Sorry. reading the original in Google Groups, I missed that the duration column is actually an integer.

CREATE TABLE linefaultdata (
ID INT NOT NULL,
FaultCode INT NOT NULL,
AssetID INT NOT NULL,
StartTime DATETIME NOT NULL,
Duration INT NOT NULL,
Shift VARCHAR(20) NOT NULL
)
GO
INSERT linefaultdata
VALUES
(2670376,8,2,'2011-08-19 19:20:30.000',14,'AFTERNOONS'),
(2670608,7,2,'2011-08-19 21:10:24.000',12,'AFTERNOONS'),
(2671183,8,2,'2011-08-19 01:10:36.000',22,'NIGHTS'),
(2671467,2,2,'2011-08-19 03:20:34.000',12,'NIGHTS'),
(2672351,3,2,'2011-08-19 09:10:30.000',19,'DAYS'),
(2673047,3,2,'2011-08-19 13:10:45.000',19,'DAYS'),
(2675476,8,2,'2011-08-19 14:14:54.000',23,'DAYS'),
(2678765,7,2,'2011-08-19 01:23:43.000',19,'NIGHTS'),
(2678654,8,2,'2011-08-19 20:01:03.000',21,'AFTERNOONS')
GO
SELECT faultcode, shift, DATEPART(HOUR,StartTime) AS Hour, COUNT(lfd.duration) AS COUNT, SUM(lfd.Duration) AS Duration
FROM linefaultdata lfd
GROUP BY faultcode, shift, DATEPART(HOUR,starttime)
go
DROP TABLE dbo.linefaultdata


(9 row(s) affected)
faultcode shift Hour COUNT Duration
----------- -------------------- ----------- ----------- -----------
2 NIGHTS 3 1 12
3 DAYS 9 1 19
3 DAYS 13 1 19
7 AFTERNOONS 21 1 12
7 NIGHTS 1 1 19
8 AFTERNOONS 19 1 14
8 AFTERNOONS 20 1 21
8 DAYS 14 1 23
8 NIGHTS 1 1 22

(9 row(s) affected)

[Erland Sommarskog]

Post by rpresser
You seem to have forgotten a GROUP BY.

Yes. Thanks for the correction.

Post by rpresser
And why is it better to use an hours table than to just group by
datepart(Hour, lfd.StartTime)?

It seemed to me that JMac wanted a row for all hours, no matter there had
been a fault. Then you need the Hours table to produce those rows.