January 10, 2009 at 8:10 am
Hi there,
For a calendar application: I have a table with "blocked times", in which I store on what DOW (day of week) a person is unavailable. In this routine, I supply a startdate and a number of days, returned is a rowset on which days, which times are blocked.
In the example below I want to see what times are blocked, starting on January 4th 2009, and then 7 days ahead. In the table @blocks I add a number of blocked times.
I works, still I got two questions:
1)
I use the code
LEFT JOIN @blocks b ON 1=1
It looks kind of weird.... is there a "better" way to write this?
2)
I would like to have a WHERE clause like this:
WHERE blockFrom IS NOT NULL
But that gives an error (Invalid column name 'blockFrom') so I have to put the whole calculation in the WHERE clause. Can this be done differntly?
Thanks!
Ray
SET DATEFORMAT dmy
DECLARE @startDate DATETIME
SET @startDate = CAST('04-01-2009' AS DATETIME)
DECLARE @numberOfDays TINYINT
SET @numberOfDays = 7
DECLARE@blocks TABLE (
dayOfWeekTINYINT,
startTimeCHAR(5),
stopTimeCHAR(5)
)
INSERT INTO @blocks VALUES (1,'00:00','23:59')
INSERT INTO @blocks VALUES (2,'00:00','09:00')
INSERT INTO @blocks VALUES (2,'17:15','23:59')
INSERT INTO @blocks VALUES (3,'00:00','09:00')
INSERT INTO @blocks VALUES (3,'17:15','23:59')
INSERT INTO @blocks VALUES (4,'00:00','09:00')
INSERT INTO @blocks VALUES (4,'17:00','23:59')
INSERT INTO @blocks VALUES (5,'00:00','09:00')
INSERT INTO @blocks VALUES (5,'17:00','23:59')
INSERT INTO @blocks VALUES (6,'00:00','09:00')
INSERT INTO @blocks VALUES (6,'17:00','23:59')
INSERT INTO @blocks VALUES (7,'00:00','23:59')
SELECT t.calenderDate,
CASE WHEN DATEPART(dw,t.calenderDate) = b.dayOfWeek
THEN DATEADD(mi,CAST(RIGHT(startTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(startTime,2) AS INTEGER),t.calenderDate))
ELSE NULL END AS blockFrom,
CASE WHEN DATEPART(dw,t.calenderDate) = b.dayOfWeek
THEN DATEADD(mi,CAST(RIGHT(stopTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(stopTime,2) AS INTEGER),t.calenderDate))
ELSE NULL END AS blockTo
FROM
(
SELECT DATEADD(d,v.number,@startDate) AS calenderDate
FROM master..spt_values v
WHERE v.type='P' AND v.number>=0 AND v.number<@numberOfDays
) AS t
LEFT JOIN @blocks b ON 1=1
WHERE CASE WHEN DATEPART(dw,t.calenderDate) = b.dayOfWeek
THEN DATEADD(mi,CAST(RIGHT(startTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(startTime,2) AS INTEGER),t.calenderDate))
ELSE NULL END IS NOT NULL
ORDER BY t.calenderDate,b.startTime,b.stopTime
January 10, 2009 at 12:44 pm
Instead of that CASE in WHERE clause, you could write this:
SELECT t.calenderDate,
CASE WHEN DATEPART(dw,t.calenderDate) = b.dayOfWeek
THEN DATEADD(mi,CAST(RIGHT(startTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(startTime,2) AS INTEGER),t.calenderDate))
ELSE NULL END AS blockFrom,
CASE WHEN DATEPART(dw,t.calenderDate) = b.dayOfWeek
THEN DATEADD(mi,CAST(RIGHT(stopTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(stopTime,2) AS INTEGER),t.calenderDate))
ELSE NULL END AS blockTo
FROM
(
SELECT DATEADD(d,v.number,@startDate) AS calenderDate
FROM master..spt_values v
WHERE v.type='P' AND v.number>=0 AND v.number<@numberOfDays) AS t
LEFT JOIN @blocks b ON 1=1
WHERE 1 = 1
AND DATEPART(dw,t.calenderDate) = b.dayOfWeek
AND DATEADD(mi,CAST(RIGHT(startTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(startTime,2) AS INTEGER),t.calenderDate)) IS NOT NULL
ORDER BY t.calenderDate,b.startTime,b.stopTime
January 10, 2009 at 1:00 pm
And also, LEFT JOIN is not required here. You can just write it as:
SELECT t.calenderDate,
CASE WHEN DATEPART(dw,t.calenderDate) = b.dayOfWeek
THEN DATEADD(mi,CAST(RIGHT(startTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(startTime,2) AS INTEGER),t.calenderDate))
ELSE NULL END AS blockFrom,
CASE WHEN DATEPART(dw,t.calenderDate) = b.dayOfWeek
THEN DATEADD(mi,CAST(RIGHT(stopTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(stopTime,2) AS INTEGER),t.calenderDate))
ELSE NULL END AS blockTo
FROM
(
SELECT DATEADD(d,v.number,@startDate) AS calenderDate
FROM master..spt_values v
WHERE v.type='P' AND v.number>=0 AND v.number<@numberOfDays) AS t,
@blocks b
WHERE 1 = 1
AND DATEPART(dw,t.calenderDate) = b.dayOfWeek
AND DATEADD(mi,CAST(RIGHT(startTime,2) AS INTEGER),DATEADD(hh,CAST(LEFT(startTime,2) AS INTEGER),t.calenderDate)) IS NOT NULL
ORDER BY t.calenderDate,b.startTime,b.stopTime
January 10, 2009 at 3:07 pm
gyessql (1/10/2009)
And also, LEFT JOIN is not required here. You can just write it as:
SELECT ...FROM (...) AS t, @blocks b
...
You would be better off sticking with the newer JOIN syntax:
SELECT ...
FROM (...) AS t CROSS JOIN @blocks b
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 10, 2009 at 10:28 pm
RBarryYoung (1/10/2009)
You would be better off sticking with the newer JOIN syntax:
SELECT ...FROM (...) AS t CROSS JOIN @blocks b
...
SELECT ...FROM (...) AS t, @blocks b
...
Is there any performance difference by using CROSS JOIN than just taking the table in the FROM clause?
January 10, 2009 at 10:34 pm
Not that I know of.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 12, 2009 at 3:07 am
Hey folks,
Thanks for all your replies. I think that using CROSS JOIN is the best, because it's a newer syntax.
Thx,
Ray
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply