February 17, 2009 at 4:05 pm
Hello all, I have no idea on how to do this. What I would like to do is modify a query slighty based on what day of the week it is. I.E if I run it on Thurday this is what criteria I want to use:HAVING (EXPC_SHIP_DATE = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) + 1) if I run it on a Friday then I want to run this: HAVING (EXPC_SHIP_DATE = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) + 3) What I am trying to do is look at the next days orders (all week), but on Fridays I want to look at Mondays orders not Saturdays.
Thanks in advance
February 17, 2009 at 4:49 pm
use a DATEPART function to determine the dayofweek and use CASE statement to act accordingly
something like this
(EXPC_SHIP_DATE =CASE DATEPART (dw, GETDATE())
WHEN 7
THENCAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) + 2
WHEN 6
THENCAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) + 3
ELSE
CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) + 1
END)
February 17, 2009 at 5:55 pm
What about public holidays?
DECLARE @NextWorkingDay datetime
SELECT @NextWorkingDay = MIN(Date)
FROM dbo.Calendar
WHERE IsWorkingDay = 1 AND Date > GETDATE()
SELECT ...
FROM ...
EXPC_SHIP_DATE = @NextWorkingDay
_____________
Code for TallyGenerator
February 17, 2009 at 10:02 pm
BTW,
there is a good explanation why you need Calendar table in one of the recent posts here:
_____________
Code for TallyGenerator
February 18, 2009 at 2:39 pm
Thanks Gopi, that was enough to point me in the right direction. I guess I'll work on that Calendar table next.
Thanks again everybody.
February 19, 2009 at 7:36 am
In addition to Sergiy's recommendation, check this article out, and the discussion that follows. I particularly like JimFive's suggestion near the very end.
http://www.sqlservercentral.com/articles/T-SQL/65423/
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply