Avoid dynamic SQL "time range"

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

SQL Server Development (2000)

Avoid dynamic SQL "time range"

Author

Topic

beyonder422
Posting Yak Master

124 Posts

Posted - 2006-02-08 : 12:08:49

Is there a simple way to "statically" code a where statement choosing time ranges without using dynamic SQL?

Example:
Day shift time range - @shift_start = '07:30', @shift_end = '16:15'
where ((convert(char(5),cp.consumed_date,114) >= @shift_start)
and (convert(char(5),cp.consumed_date,114) <= @shift_end))

Night shift time range - @shift_start = '19:30', @shift_end = '04:30'
where ((convert(char(5),cp.consumed_date,114) >= @shift_start)
or (convert(char(5),cp.consumed_date,114) <= @shift_end))

The problem is switching between the "and" and the "or" logic.
I've seen an "in (1,2,3,..)" hourly "where", but is there a simpler "static" "where" that I can incorporate minutes into as well?

www.beyonder422.com

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-02-10 : 08:45:39

I would definitely want to get the shift information into a table to avoid having to update your code if shift patterns change. As I'm sure you've realised, the problem here is that SQL Server has no 'Time' datatype. Try this approach:


	CREATE TABLE dbo.Shifts
	(
		ShiftID TINYINT IDENTITY(1, 1), 
		ShiftName VARCHAR(30), 
		StartHour TINYINT, 
		StartMinute TINYINT, 
		EndHour TINYINT, 
		EndMinute TINYINT, 
		SpansDayFlag BIT
	)
	INSERT INTO dbo.Shifts 
	(
		ShiftName, 
		StartHour, 
		StartMinute, 
		EndHour, 
		EndMinute, 	
		SpansDayFlag
	) 
	SELECT 
		'Day Shift', 
		7  AS ShiftName, 
		30 AS StartHour, 
		16 AS StartMinute, 
		15 AS EndHour, 
		0  AS EndMinute 
	UNION SELECT 
		'Night Shift', 
		19, 
		30, 
		4, 
		30, 
		1

	SELECT
		cp.*
	FROM
		dbo.CP AS cp
		JOIN dbo.Shifts AS s
			ON cp.consumed_date BETWEEN DATEADD(minute, s,StartMinute, DATEADD(hour, s.StartHour, DATEADD(day, CASE WHEN s.StartHour > EndHour THEN -1 ELSE 0 END + DATEDIFF(day, 0, cp.consumed_date) - CASE WHEN DATEPART(hour, cp.consumed_date) < 12 THEN CAST(s.SpansDayFlag AS TINYINT) ELSE 0 END, 0 )))
                                                AND DATEADD(minute, s.EndMinute, DATEADD(hour, s.EndHour, DATEADD(day, DATEDIFF(day, 0, cp.consumed_date) + CASE WHEN DATEPART(hour, cp_consumed_date) >=12 THEN CAST(s.SpansDayFlag AS TINYINT) ELSE 0 END, 0 )))

This uses DATEADD in conjunction with DATEDIFF to strip out the time portion of consumed_date before in order to then add the hours and minutes from the shift table to produce start and end datetimes. The SpansDayFlag accommodates those shifts that run over night by subtracting a day from the start of the shift or adding a day to the end of the shift. In order to accommodate this, any dates with an hour part less than 12 are assumed to originate from a shift that started the previous day whilst any from midday onwards are assumed to be part of a shift starting on that day. It could be that this is a problem for you(?) I would also want to put some constraints on the Shifts table to constrain the hour and minute values appropriately, to check that no overlapping ranges exist, etc.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources