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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy