Query needed - 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 2005 Forums

Transact-SQL (2005)

Query needed

Author

Topic

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-13 : 07:11:09

Hello All

I have a table with the entries


Date                          Events
---------------------------------------
01/01/2009 00:00:01           Event X
01/01/2009 01:01:01           Event Y
01/01/2009 02:20:01           Event Z
01/01/2009 04:00:01           Event X
01/01/2009 04:20:01           Event Y
01/01/2009 05:00:01           Event Z

As you can see from the table, there are no entries between 3:00:00 and 3:59:59 hrs .. I need to populate NULL events when there are no entries in that timezone when there are no events happening

ie.


Date                          Events
---------------------------------------
01/01/2009 00:00:01           Event X
01/01/2009 01:01:01           Event Y
01/01/2009 02:20:01           Event Z
01/01/2009 03:00:00        NO EVENT
01/01/2009 04:00:01           Event X
01/01/2009 04:20:01           Event Y
01/01/2009 05:00:01           Event Z

Please help with the query which performs check for the missing time event detail and outputs the results.

Regards
Srivatsa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 07:36:02

you need a table with hourly date to left join to your table.


select coalesce(t.ddate, h.date), isnull(t.events, 'NO EVENT')
from   hourly_table h
       left join table t on h.date <= t.date and dateadd(hour, 1, h.date) > t.date

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 07:48:33

Something similar to this?

DECLARE	@Sample TABLE
	(
		dt DATETIME,
		Evnts VARCHAR(20)
	)

INSERT	@Sample
SELECT	'01/01/2009 00:00:01', 'Event X' UNION ALL
SELECT	'01/01/2009 01:01:01', 'Event Y' UNION ALL
SELECT	'01/01/2009 02:20:01', 'Event Z' UNION ALL
SELECT	'01/01/2009 04:00:01', 'Event X' UNION ALL
SELECT	'01/01/2009 04:20:01', 'Event Y' UNION ALL
SELECT	'01/01/2009 05:00:01', 'Event Z'

SELECT * FROM @Sample

;WITH Yak (theTime)
AS (
	SELECT		DATEADD(HOUR, v.Number, d.theDay)
	FROM		(
				SELECT		DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0) AS theDay,
						DATEPART(HOUR, dt) AS theHour,
						MIN(DATEPART(HOUR, dt)) OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0)) AS minHour,
						MAX(DATEPART(HOUR, dt)) OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0)) AS maxHour
				FROM		@Sample
			) AS d
	INNER JOIN	master..spt_values AS v ON v.Type = 'P'
	WHERE		v.Number > d.minHour
			AND v.Number < d.maxHour
	GROUP BY	DATEADD(HOUR, v.Number, d.theDay)
	HAVING		MAX(CASE WHEN v.Number = d.theHour THEN 1 ELSE 0 END) = 0
)

INSERT	@Sample
SELECT	theTime,
	'No event'
FROM	Yak

SELECT * FROM @Sample

N 56°04'39.26"
E 12°55'05.63"

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-13 : 08:38:28

Hello Peso
Could you please elaborate on these items
The table named Yak and the variable : theTime

Thanks
Srivatsa

quote:

Originally posted by Peso

Something similar to this?

DECLARE	@Sample TABLE
	(
		dt DATETIME,
		Evnts VARCHAR(20)
	)

INSERT	@Sample
SELECT	'01/01/2009 00:00:01', 'Event X' UNION ALL
SELECT	'01/01/2009 01:01:01', 'Event Y' UNION ALL
SELECT	'01/01/2009 02:20:01', 'Event Z' UNION ALL
SELECT	'01/01/2009 04:00:01', 'Event X' UNION ALL
SELECT	'01/01/2009 04:20:01', 'Event Y' UNION ALL
SELECT	'01/01/2009 05:00:01', 'Event Z'

SELECT * FROM @Sample

;WITH Yak (theTime)
AS (
	SELECT		DATEADD(HOUR, v.Number, d.theDay)
	FROM		(
				SELECT		DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0) AS theDay,
						DATEPART(HOUR, dt) AS theHour,
						MIN(DATEPART(HOUR, dt)) OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0)) AS minHour,
						MAX(DATEPART(HOUR, dt)) OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0)) AS maxHour
				FROM		@Sample
			) AS d
	INNER JOIN	master..spt_values AS v ON v.Type = 'P'
	WHERE		v.Number > d.minHour
			AND v.Number < d.maxHour
	GROUP BY	DATEADD(HOUR, v.Number, d.theDay)
	HAVING		MAX(CASE WHEN v.Number = d.theHour THEN 1 ELSE 0 END) = 0
)

INSERT	@Sample
SELECT	theTime,
	'No event'
FROM	Yak

SELECT * FROM @Sample

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 08:44:18

that's Common Table Expression, CTE. Read about it in BOL http://msdn.microsoft.com/en-us/library/ms175972%28SQL.90%29.aspx

KH
[spoiler]Time is always against us[/spoiler]

srivatsahg
Yak Posting Veteran

71 Posts

Posted - 2009-07-13 : 08:54:12

Yes i do want something like this ..
But i am getting the results as twice...
Any idea how to avoid this ??

quote:
Originally posted by khtan

that's Common Table Expression, CTE. Read about it in BOL http://msdn.microsoft.com/en-us/library/ms175972%28SQL.90%29.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources