TSQL for Election day
This could be a puzzles question, but since I wanted a SQL variation I figured I would post it here. Also, having just answered a few questions about date math in the last few days, I think the answer might be of general interest to SQL people.
So - the challenge is to come up with a formula that returns the date for the US national elections.
The formula should take today's date and return the election day for the year of that date, so it will work for any year.
If you are certified in SQL I know you can do it - so I'll propose that the question be answered by an as-yet uncertified expert. Any certified experts who want to chime in on rating the answers are of course welcome.
Just some SQL fun for anyone writing code over the weekend.
So - the challenge is to come up with a formula that returns the date for the US national elections.
The formula should take today's date and return the election day for the year of that date, so it will work for any year.
If you are certified in SQL I know you can do it - so I'll propose that the question be answered by an as-yet uncertified expert. Any certified experts who want to chime in on rating the answers are of course welcome.
Just some SQL fun for anyone writing code over the weekend.
Imran - You would be excluded from the questioners guidelines (as would I) So please don't answer. We can comment on the responses if you like. I realize this may not tbe the best forum for such a question but it is since we are all interested in SQL here.
But for the sake of any uncertified folks out there (and I presume you mean EE "certification", right?) the US Election law as outlined by a decision of congress happens on The First Tuesday of November following the first Monday of November.
Sounds confusing but basically if 11/1/yyyy is a Tuesday, the elections will be the following week. Sounds kind of strange.. Well this website: http://www.infoplease.com/askeds/election-day-first-tuesday-november.html answers the questions of :
Why Tuesday?
Why the First Tuesday after the first Monday?
But for the sake of any uncertified folks out there (and I presume you mean EE "certification", right?) the US Election law as outlined by a decision of congress happens on The First Tuesday of November following the first Monday of November.
Sounds confusing but basically if 11/1/yyyy is a Tuesday, the elections will be the following week. Sounds kind of strange.. Well this website: http://www.infoplease.com/askeds/election-day-first-tuesday-november.html answers the questions of :
Why Tuesday?
Why the First Tuesday after the first Monday?
So basically we are looking for first tuesday in month november of an year.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes Mike - you are correct on all counts. Here is the Wikipedia link I had checked out: http://en.wikipedia.org/wiki/Election_Day_(United_States)
It's a little ugly - also I wouldn't use weekday due to regional settings. You can also get away with it without a while loop (or declaring a local variable).
Sorry Imran - I meant datename
Aaron - I will post when you 'open up' the question to the general field.
Aaron - I will post when you 'open up' the question to the general field.
imran_fast,
> So basically we are looking for first tuesday in month november of an year.
Not quite. If the first Tuesday in November happened to be Nov 1, then it would not be Election Day--
it would go to Nov 8, as that is the first Tuesday following the first Monday.
Patrick
> So basically we are looking for first tuesday in month november of an year.
Not quite. If the first Tuesday in November happened to be Nov 1, then it would not be Election Day--
it would go to Nov 8, as that is the first Tuesday following the first Monday.
Patrick
hi Patrick,
The solution i provided will give the first Tuesday following the first Monday. if you look at it i am searching for first Monday and then adding one day to it.
Imran
The solution i provided will give the first Tuesday following the first Monday. if you look at it i am searching for first Monday and then adding one day to it.
Imran
ASKER
OK no newbies have posted... answer away guys... then I will post my own and award points.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think there are a number of interesting approaches. There are a number of applications for this logic since a lot of holidays follow rules along the same lines. Hope this thread helps people in the future.
I found Nightman's approach the most elegant, though it did require setting the datefirst value.
Imran's approach avoided the need for the datefirst but requires the loop, which may be slower though I did not benchmark it.
Scott's approach (which works if you combine the select statement in the second post with the logic of the first) uses both the loop and the datefirst - I'll attribute that to his understandable desire to get home!
I'll be awarding 200 to Nightman, 150 to Imran, and 100 to scott. Thanks guys! My own response - which I also rushed - was a giant case statement that did not require datefirst but is so ugly that having seen Nightman's post I am to embarrassed to post.
I found Nightman's approach the most elegant, though it did require setting the datefirst value.
Imran's approach avoided the need for the datefirst but requires the loop, which may be slower though I did not benchmark it.
Scott's approach (which works if you combine the select statement in the second post with the logic of the first) uses both the loop and the datefirst - I'll attribute that to his understandable desire to get home!
I'll be awarding 200 to Nightman, 150 to Imran, and 100 to scott. Thanks guys! My own response - which I also rushed - was a giant case statement that did not require datefirst but is so ugly that having seen Nightman's post I am to embarrassed to post.
ASKER
Sorry - I forgot to acknowledge Mike's contribution early in the thread... catch you next time.
Thanks for the compliments Aaron - I have done a fair amount of work with date theory (both client side and in T-SQL) having workedon glabalised systems with different user requirements (such as which is the first working week of the year, etc) so this was right up my alley. I almost feel like I've cheated ;)
>> Scott's approach (which works if you combine the select statement in the second post with the logic of the first) uses both the loop and the datefirst - I'll attribute that to his understandable desire to get home! <<
What "loop"??? That's what I was thinking so hard and long to *avoid*. You just put the newer version of the SELECT in place of the original one.
Not to say it's the best code (yet), but it does *not* have a loop.
What "loop"??? That's what I was thinking so hard and long to *avoid*. You just put the newer version of the SELECT in place of the original one.
Not to say it's the best code (yet), but it does *not* have a loop.
Having had the time to read Scott's now, I think he has it correct - the second post has no loop. Because it still uses local variables the assumption was probably to simply replace the select IN the loop instead of the entire loop.
Scott's (without the local variables) doesn't have the overhead of the set datefirst OR the loop, so is the most efficient - if not the prettiest ;)
SELECT
DATEADD(DAY,9 -
CASE
WHEN ((@@DATEFIRST + DATEPART(dw, CAST(YEAR(GETDATE()) AS CHAR(4)) + '1101') + 5) % 7 + 1) = 1 THEN 8
ELSE ((@@DATEFIRST + DATEPART(dw, CAST(YEAR(GETDATE()) AS CHAR(4)) + '1101') + 5) % 7 + 1) END,
CAST(YEAR(GETDATE()) AS CHAR(4)) + '1101')
AS [Election Day]
Scott's (without the local variables) doesn't have the overhead of the set datefirst OR the loop, so is the most efficient - if not the prettiest ;)
SELECT
DATEADD(DAY,9 -
CASE
WHEN ((@@DATEFIRST + DATEPART(dw, CAST(YEAR(GETDATE()) AS CHAR(4)) + '1101') + 5) % 7 + 1) = 1 THEN 8
ELSE ((@@DATEFIRST + DATEPART(dw, CAST(YEAR(GETDATE()) AS CHAR(4)) + '1101') + 5) % 7 + 1) END,
CAST(YEAR(GETDATE()) AS CHAR(4)) + '1101')
AS [Election Day]
The local variable was simply to store Nov 01 of the desired year. It could be done without a variable by simply hard-coding the current year -- I was making it easier for others to test.
>> if not the prettiest ;) <<
Let's be honest -- none of them are particularly pretty :-)
>> if not the prettiest ;) <<
Let's be honest -- none of them are particularly pretty :-)
ASKER
sorry - i missed that! I saw the while loop - now that I think of it I was wondering why I got 7 results back! We're all busy. To atone, I will post my solution ... save the critique - it was a total brute force approach I did as fast as I could - but I had no points to win!
select
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/2/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/3/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/4/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/5/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/6/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/7/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/8/'+convert(varchar,da
end
end
end
end
end
end
end
select
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/2/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/3/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/4/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/5/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/6/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/7/'+convert(varchar,da
else
case datepart(dw,convert(dateti
when 3 then convert(datetime, '11/8/'+convert(varchar,da
end
end
end
end
end
end
end
Sorry, you're right, I have did a dopey loop in there just to show that the DATEFIRST setting didn't affect the result (so you wouldn't have to check each setting yourself :-) ).
I should have posted a sample **without any loop** to show that no looping was required by the main logic I provided. My two main goals were:
1) no dependency on DATEFIRST
2) no looping.
I should have posted a sample **without any loop** to show that no looping was required by the main logic I provided. My two main goals were:
1) no dependency on DATEFIRST
2) no looping.
Wht is the formula for that?