July 29, 2009 at 10:46 am
I do truly want and hope to. After my hospitalization in April and May, I did finally get caught up in my work by the end of June. Then I was on vacation for a week, then a close family member passed away and I happen to be the executor of his estate. The last one is still on-going, but I am really going to try to get the next one out in August.
[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]
December 10, 2009 at 9:23 am
Total noob here. Love this series, can't wait for 3rd installment.
I found some code in an article today, author said "The easiest way I've found to do this is run through a cursor. Sounds painful, but you will only have to do it once..."
What he's referring to is adding a UTCOffset column to a Calendar table, then populating that column only.
CREATE TABLE dbo.Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,
isWeekday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT,
)
GO
Add a few years of dates dates...
INSERT Calendar(dt)
SELECT DATEADD(DAY, Number, '20000101')
FROM #Numbers
WHERE Number <= 10957
ORDER BY Number
GO
Populate the table...
UPDATE dbo.Calendar SET
isWeekday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0,
Y = YEAR(dt),
FY = YEAR(dt),
/*
-- if our fiscal year
-- starts on May 1st:
FY = CASE
WHEN MONTH(dt) < 5
THEN YEAR(dt)-1
ELSE YEAR(dt) END,
*/
Q = CASE
WHEN MONTH(dt) <= 3 THEN 1
WHEN MONTH(dt) <= 6 THEN 2
WHEN MONTH(dt) <= 9 THEN 3
ELSE 4 END,
M = MONTH(dt),
D = DAY(dt),
DW = DATEPART(DW, dt),
monthname = DATENAME(MONTH, dt),
dayname = DATENAME(DW, dt),
W = DATEPART(WK, dt)
GO
Add the column...
ALTER TABLE dbo.Calendar ADD UTCOffset TINYINT NULL
GO
Use Cursor to populate UTCOffset column...
SET NOCOUNT ON
DECLARE @dt SMALLDATETIME
DECLARE @offset TINYINT
SET @offset = 5
DECLARE c CURSOR
LOCAL STATIC READ_ONLY FOR
SELECT dt FROM dbo.Calendar ORDER BY dt
OPEN c
FETCH NEXT FROM c INTO @dt
WHILE @@FETCH_STATUS = 0
BEGIN
IF DATENAME(dw, @dt)='Sunday'
AND DATEPART(DAY, @dt) <= 7
AND DATENAME(MONTH, @dt) = 'April'
SET @offset = 4
IF DATENAME(dw, @dt)='Sunday'
AND DATEPART(DAY, @dt) >= 25
AND DATENAME(MONTH, @dt) = 'October'
SET @offset = 5
UPDATE dbo.Calendar SET UTCOffset = @offset WHERE dt = @dt
FETCH NEXT FROM c INTO @dt
END
CLOSE c
DEALLOCATE c
GO
So I took a stab at converting it...
DECLARE @offset TINYINT
SELECT @offset = 5
UPDATE dbo.Calendar SET
isWeekday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,
isHoliday = 0,
Y = YEAR(dt),
FY = YEAR(dt),
/*
-- if our fiscal year
-- starts on May 1st:
FY = CASE
WHEN MONTH(dt) < 5
THEN YEAR(dt)-1
ELSE YEAR(dt) END,
*/
Q = CASE
WHEN MONTH(dt) <= 3 THEN 1
WHEN MONTH(dt) <= 6 THEN 2
WHEN MONTH(dt) <= 9 THEN 3
ELSE 4 END,
M = MONTH(dt),
D = DAY(dt),
DW = DATEPART(DW, dt),
monthname = DATENAME(MONTH, dt),
dayname = DATENAME(DW, dt),
W = DATEPART(WK, dt),
@offset = UTCOffset = CASE WHEN DATENAME(dw, dt)='Sunday'
THEN CASE WHEN DATEPART(DAY, dt) <= 7 THEN CASE WHEN DATENAME(MONTH, dt) = 'April'
THEN 4
ELSE @offset END
--WHEN DATENAME(dw, dt)='Sunday'
WHEN DATEPART(DAY, dt) >= 25
THEN CASE WHEN DATENAME(MONTH, dt) = 'October'
THEN 5
ELSE @offset END
ELSE @offset END
ELSE @offset END
GO
Forgive me if the code formatting is bad (remember, I said NOOB here!)
Any alternatives anyone?
Thanks Barry, GWS.
Edit: Note that the dates when the clocks change have since changed.
December 10, 2009 at 9:34 am
Looks good, Ron. Though I cannot test it right now to be certain, this is basically the approach that I would take.
[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]
December 10, 2009 at 11:22 am
Hey Ron,
It's even easier if you have SQL 2008 -- the DateTimeOffset data type holds date/time and offset information together. The only thing I would mention about your script is it appears to adjust the UTC offset for daylight savings time. The UTC offset is actually a constant, so it won't change. For instance, New York City is always UTC offset -5. Normally a DST adjustment is stored separately from the UTC offset, so you would add two columns to your table -- UTC_Offset and DST_Adjustment (would always be 0 or 1 depending on time of year and local DST laws).
Thanks
Mike C
May 10, 2010 at 6:21 pm
How does the idea that you can always replace a cursor with something else, fit in with your Connect suggestion, https://connect.microsoft.com/SQLServer/feedback/details/440375?
May 11, 2010 at 6:53 am
john.moreno (5/10/2010)
How does the idea that you can always replace a cursor with something else, fit in with your Connect suggestion, https://connect.microsoft.com/SQLServer/feedback/details/440375?
Maybe it's just me but it seems obvious... it's a permanent replacement for cursors instead of having to do things like XML concatenation or use undocumented heavy cursor hacks like sp_MSForEachTable.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 11, 2010 at 10:47 am
Jeff Moden (5/11/2010)
john.moreno (5/10/2010)
How does the idea that you can always replace a cursor with something else, fit in with your Connect suggestion, https://connect.microsoft.com/SQLServer/feedback/details/440375?Maybe it's just me but it seems obvious... it's a permanent replacement for cursors instead of having to do things like XML concatenation or use undocumented heavy cursor hacks like sp_MSForEachTable.
Yeah, but...it seemed to me that the point of this series of articles was that you could get away from a cursor NOW, not if they introduced something else that was better (and that is definitely a better design).
Which may be the answer...he's attacking the problem on multiple fronts.
May 11, 2010 at 7:55 pm
john.moreno (5/11/2010)
Yeah, but...it seemed to me that the point of this series of articles was that you could get away from a cursor NOW, not if they introduced something else that was better (and that is definitely a better design).
Yes, that is the point, and yes, you certainly can get rid of them now. However it would be nice to have a simpler way of getting rid of them in some cases. Personally I would prefer to see MAP and REDUCE added to SQL because I think that way up, but generalising how a result set (or cte or whatever) can be used is another valid way of doing it.
Which may be the answer...he's attacking the problem on multiple fronts.
Yes, at least two fronts: (1) educate the users about how awful cursors are and (2) change the language to make it even easier than it is now to avoid them.
Tom
May 13, 2010 at 8:25 pm
Yep, Jeff & Tom got it right. My Connect suggestion would be a huge leap forward for cursor-less SQL, but almost everything that I requested can be done in SQL Server now. For an example, see my recently submitted OVER_SET procedure[/url].
However, my EXECUTE .. FROM command would be able to do it much better. 🙂
[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]
May 13, 2010 at 9:59 pm
Hey Barry
I'm going to play around with your code tomorrow -- looks cool. It almost sounds like you're describing a cross apply type operation for stored procs. That could come in very handy, especially for admin tasks (although I can imagine the ways people would find to abuse it :))
Thanks!
Mike C
May 14, 2010 at 5:23 pm
Thanks Mike, let us know how it goes ... 😎
[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]
May 14, 2010 at 5:26 pm
When do you think part 3 will be ready?
June 25, 2010 at 2:16 am
What a fantastic article! I'm really looking forward to Part 3. This is something I have been banging on about for years and I think people just consider me insane. Its nice to know there are others out there who share my view.
My philosophy is "cursors are the devil" and the best response I have heard to that is "cursors maybe the devil but without evil what use is good". That was from an Oracle programmer who loves his loops and cursors 🙂 I even had his response put on a T-Shirt for him!
I have also blogged on this subject if anyone is interested. You can find my post here.
June 26, 2010 at 11:38 pm
Great article and it surely points the way for more efficient statements.
I'm eagerly anticipating the next series of this article for more complicated scenarios where cursors can be replaced.
I'm somewhat novice but would be keen for pointers on how I can eliminate cursors in the following scenario where I have a table:
Column1 Column2
1 A
1 B
1 C
2 A
2 D
and I want to get a string like "A,B,C" when I search for 1, or "A,D" for 2 etc.
June 27, 2010 at 1:31 am
I hope part three will have better examples,
the examples up until now are too obvious.
I have used cursors my self in much more complex situations and i would love to see methods of converting them to usual sql code.
Viewing 15 posts - 196 through 210 (of 316 total)
You must be logged in to reply to this topic. Login to reply