Optimizing daylight savings query by not using UNION

I was interested in optimizing a query I created in SQL Server 2000 for
adjusting time zones for daylight savings time (only USA DST for simplicity)
.
In essence I have one table "tblDaylightSavingsTime" where the PK is Year,
with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
10/30/2005 2:00:00 AM).
I am joining the Daylight Savings Table to another database view, the logic
goes like this:
If the view's datetime falls inside of the DST range for that year, then
adjust for Daylight Savings.
*UNION*
If the view's datetime falls outside of the DST range for that year, then
adjust for Regular Time.
The volume of data pushed through this query is large (in the millions). Is
there any different way to optimize this query by not using UNION?
Thanks!
--
SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
FROM qryhsplit_formatted h INNER JOIN
tblDaylightSavingsTime dst ON year(h.indatetime) =
dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
dst.Fall_Back))
UNION ALL
SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
FROM qryhsplit_formatted h INNER JOIN
tblDaylightSavingsTime dst ON year(h.indatetime) =
dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)How about using a calendar table? This specific example is treated:
http://www.aspfaq.com/2519
"br" <br@.discussions.microsoft.com> wrote in message
news:CA0D1166-D8D5-4C97-8DA1-2E06FDB212B5@.microsoft.com...
>I was interested in optimizing a query I created in SQL Server 2000 for
> adjusting time zones for daylight savings time (only USA DST for
> simplicity).
> In essence I have one table "tblDaylightSavingsTime" where the PK is Year,
> with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
> 10/30/2005 2:00:00 AM).
> I am joining the Daylight Savings Table to another database view, the
> logic
> goes like this:
> If the view's datetime falls inside of the DST range for that year, then
> adjust for Daylight Savings.
> *UNION*
> If the view's datetime falls outside of the DST range for that year, then
> adjust for Regular Time.
> The volume of data pushed through this query is large (in the millions).
> Is
> there any different way to optimize this query by not using UNION?
> Thanks!
> --
> SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
> dst.Fall_Back))
> UNION ALL
> SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)
>|||Try this instead; sorry I couldn't figure out all of your date fields, so I
wrote the below with my own column labels.
SELECT "adjusted_date" =
CASE
WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
THEN DATEADD(hh, 1, t1.[your datetime field])
ELSE t1.[your datetime field]
END
FROM qryhsplit_formatted
INNER JOIN tblDaylightSavingsTime t2
ON YEAR(t1.[your datetime field] = t2.Year
Keep in mind that the dates for DST clock changes will shift starting in 200
7!
"br" wrote:

> I was interested in optimizing a query I created in SQL Server 2000 for
> adjusting time zones for daylight savings time (only USA DST for simplicit
y).
> In essence I have one table "tblDaylightSavingsTime" where the PK is Year
,
> with fields Spring_Forward (ex: 4/3/2005 2:00:00 AM), and Fall_back (ex:
> 10/30/2005 2:00:00 AM).
> I am joining the Daylight Savings Table to another database view, the logi
c
> goes like this:
> If the view's datetime falls inside of the DST range for that year, then
> adjust for Daylight Savings.
> *UNION*
> If the view's datetime falls outside of the DST range for that year, then
> adjust for Regular Time.
> The volume of data pushed through this query is large (in the millions).
Is
> there any different way to optimize this query by not using UNION?
> Thanks!
> --
> SELECT h.*, DATEADD(hh, h.regularTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND ((h.indatetime < dst.Spring_Forward) OR (h.indatetime >
> dst.Fall_Back))
> UNION ALL
> SELECT h.*, DATEADD(hh, h.savingsTime, h.indatetime) AS indatetime_adj
> FROM qryhsplit_formatted h INNER JOIN
> tblDaylightSavingsTime dst ON year(h.indatetime) =
> dst.inYear AND (h.indatetime BETWEEN dst.Spring_Forward AND dst.Fall_Back)
>|||Forgot to put in t1 table alias for the qryhsplit_formatted view, and closin
g
) for YEAR function:
SELECT "adjusted_date" =
CASE
WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
THEN DATEADD(hh, 1, t1.[your datetime field])
ELSE t1.[your datetime field]
END
FROM qryhsplit_formatted t1
INNER JOIN tblDaylightSavingsTime t2
ON YEAR(t1.[your datetime field]) = t2.Year
"Mark Williams" wrote:
> Try this instead; sorry I couldn't figure out all of your date fields, so
I
> wrote the below with my own column labels.
> SELECT "adjusted_date" =
> CASE
> WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
> THEN DATEADD(hh, 1, t1.[your datetime field])
> ELSE t1.[your datetime field]
> END
> FROM qryhsplit_formatted
> INNER JOIN tblDaylightSavingsTime t2
> ON YEAR(t1.[your datetime field] = t2.Year
> Keep in mind that the dates for DST clock changes will shift starting in 2
007!
> --
>
> "br" wrote:
>|||Way faster!! Thanks very much. Sometimes I get bogged down in the syntax
for the Case / If statements, but this makes it much more clear.
Thanks for the heads up on the DST changes for 2007. The constant
historical changes for DST was one of the reasons why I chose to have a
tblDaylightSavingsTime that denotes the start times and end times of DST for
each year.
FYI - my adjustments "h.savingsTime", and "h.regularTime" are Time Zone
specific adjustments for bringing all data elements into Arizona Time - MST
(AZ).
"Mark Williams" wrote:
> Forgot to put in t1 table alias for the qryhsplit_formatted view, and clos
ing
> ) for YEAR function:
> SELECT "adjusted_date" =
> CASE
> WHEN t1.[your datetime field] BETWEEN t2.Spring_Forward AND t2.Fall_Back
> THEN DATEADD(hh, 1, t1.[your datetime field])
> ELSE t1.[your datetime field]
> END
> FROM qryhsplit_formatted t1
> INNER JOIN tblDaylightSavingsTime t2
> ON YEAR(t1.[your datetime field]) = t2.Year
>
> --
>
> "Mark Williams" wrote:
>