Return data for all records - SQL Server Forums

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)

Return data for all records

Author

Topic

ghastings
Starting Member

7 Posts

Posted - 2007-02-20 : 09:28:32

Hello all,

I'm trying to generate a query that will list a load of faults along with the type grouped per hour. Ideally I'm trying to end up with some results that look either like this:


HOUR    FAULTS PER HOUR     TYPE
--------------------------------
12      2                   1
12      0                   2
12      0                   3
13      1                   1
13      1                   2
13      4                   3


HOUR    TYPE1 TYPE2 TYPE3
-------------------------
12      2     0     0
13      1     1     4

The type field could have either a 1,2 or 3 in it. There may or may not be faults listed

in every hour for each fault type but I'd like to see 0 if thats the case.

The other thing I've been trying to do is create a pretty quick query that does this. Currently I use a date/time lookup table.

Here is my sample table and data


create table [dbo].[faults](
	[id] [int] not null,
	[date] [datetime] not null,
	[type] [nvarchar](1) collate sql_latin1_general_cp1_ci_as not null,
 constraint [pk_faults] primary key clustered 
(
	[id] asc
) on [primary]
) on [primary]


insert faults
	select 1, '2007-02-20 12:00:00', 1 union all
	select 2, '2007-02-20 12:10:00', 2 union all
	select 3, '2007-02-20 13:00:00', 2 union all
	select 4, '2007-02-20 15:20:00', 1 union all
	select 5, '2007-02-20 15:30:00', 2 union all
	select 6, '2007-02-20 13:40:00', 1 union all
	select 7, '2007-02-20 12:40:00', 1 union all
	select 8, '2007-02-20 15:40:00', 2 union all
	select 9, '2007-02-20 15:40:00', 1 union all
	select 10, '2007-02-20 16:40:00', 3 union all
	select 11, '2007-02-20 13:40:00', 1 union all
	select 12, '2007-02-20 12:40:00', 2 union all
	select 13, '2007-02-20 15:40:00', 2

Here is the SQL I've been playing with


select 
	t.hour,
	count(datepart(hour, f.date)) as [Total Faults per Hour],
	case f.type
		when 1 then 'Nothing to worry about'
		when 2 then 'Whoops'
		when 3 then 'Eeeeek!'
		else ''
	end as [Fault Type]
from timelookup t
	left join faults f on datepart(hour, f.date)=t.hour
group by
	t.hour,
	datepart(hour, f.date),
	f.type
order by hour

The table timelookup is simply a list of numbers 0-23 (called hour)


create table timelookup (hour int not null, display nvarchar(20))

Here is the resulting output from my sql.


HOUR   FAULTS     TYPE
0	0	
1	0	
2	0	
3	0	
4	0	
5	0	
6	0	
7	0	
8	0	
9	0	
10	0	
11	0	
12	2	Nothing to worry about
12	2	Whoops
13	2	Nothing to worry about
13	1	Whoops
14	0	
15	2	Nothing to worry about
15	3	Whoops
16	1	Eeeeek!
17	0	
18	0	
19	0	
20	0	
21	0	
22	0	
23	0

Does anyone have any ideas?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 09:54:22

[code]SELECT DATEPART(hour, date) AS Hour,
SUM(CASE WHEN Type = 1 THEN 1 ELSE 0 END) AS Type1,
SUM(CASE WHEN Type = 2 THEN 1 ELSE 0 END) AS Type2,
SUM(CASE WHEN Type = 3 THEN 1 ELSE 0 END) AS Type3
FROM dbo.Faults
GROUP BY DATEPART(hour, date)
ORDER BY 1[/code]
Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-20 : 09:57:01

Be sure you have a table of Types that defines the 3 types (1,2,3). Add more info to that tables as necessary -- description, for example. Try to avoid ever manually handling specific values in your data with SQL -- put things in tables whenever possible.

Once you have a table of types, you simply CROSS JOIN your Hours with your Types, and then do a LEFT OUTER JOIN to your data. this gives you all possible hours and all possible types and allows you to return 0 if there is no data.

Something like this:


select H.Hour, T.Type, ISNULL(F.Count,0) as Count
from timelookup H
cross join Types T
left outer join
   (select datepart(hour, f.date) as Hour, Type, count(*) as Count
    from faults
    group by datepart(hour, f.date), Type
   ) F
        on T.Type = F.Type and H.Hour = F.Hour
order by H.Hour, T.Type

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-20 : 09:57:50

UPDATE: Use Peso's if you want the cross tab, mine if you want to keep it normalized. Either way, there's your two options!

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-20 : 10:03:46

And if you want ALL TIMES, use UNION like this

SELECT		DATEPART(hour, date) AS Hour,
		SUM(CASE WHEN Type = 1 THEN 1 ELSE 0 END) AS Type1,
		SUM(CASE WHEN Type = 2 THEN 1 ELSE 0 END) AS Type2,
		SUM(CASE WHEN Type = 3 THEN 1 ELSE 0 END) AS Type3
FROM		(
			SELECT date, type FROM dbo.Faults WHERE date >= @FromDate AND date < DATEADD(day, 1, @ToDate) UNION ALL
			SELECT	'00:00:00', 0 UNION ALL
			SELECT	'01:00:00', 0 UNION ALL
			SELECT	'02:00:00', 0 UNION ALL
			SELECT	'03:00:00', 0 UNION ALL
			SELECT	'04:00:00', 0 UNION ALL
			SELECT	'05:00:00', 0 UNION ALL
			SELECT	'06:00:00', 0 UNION ALL
			SELECT	'07:00:00', 0 UNION ALL
			SELECT	'08:00:00', 0 UNION ALL
			SELECT	'09:00:00', 0 UNION ALL
			SELECT	'10:00:00', 0 UNION ALL
			SELECT	'11:00:00', 0 UNION ALL
			SELECT	'12:00:00', 0 UNION ALL
			SELECT	'13:00:00', 0 UNION ALL
			SELECT	'14:00:00', 0 UNION ALL
			SELECT	'15:00:00', 0 UNION ALL
			SELECT	'16:00:00', 0 UNION ALL
			SELECT	'17:00:00', 0 UNION ALL
			SELECT	'18:00:00', 0 UNION ALL
			SELECT	'19:00:00', 0 UNION ALL
			SELECT	'20:00:00', 0 UNION ALL
			SELECT	'21:00:00', 0 UNION ALL
			SELECT	'22:00:00', 0 UNION ALL
			SELECT	'23:00:00', 0
		) AS d
GROUP BY	DATEPART(hour, date)
ORDER BY	1

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-02-20 : 11:02:45

Peso -- he already has a "TimeLookup" table that will provide him with the 24 hours.

- Jeff
http://weblogs.sqlteam.com/JeffS

ghastings
Starting Member

7 Posts

Posted - 2007-02-21 : 04:11:33

Thanks very much for the suggestions, I'll be trying them out later.

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources