MS-SQL Stored procedure predefined for day's of the week.

I want to create a stored procedure on a MS-SQL database so that when it's called it is pre-defined to select data based on whatever day of the week it is. My issue is with when the report is run on a Monday I need to see data that may have been update during the last 3 days (Friday, Saturday and/or Sunday) all other days I only need the previous day.  I have the rest of the stored procedure worked out I'm just having trouble wrapping my head around this one exception. Here is the select code without the date parameters.  The date field I'm looking at is: "OrdDate" alias of the "dbo.OrderHeader.DateTimeLastModified" (native format DateTime)
SELECT CAST(dbo.OrderHeader.DateTimeLastModified As Date)AS OrdDate, dbo.OrderHeader.OrderNumber, dbo.OrderLine.LineNumber, dbo.AVOOption.Name AS [Installer Name], dbo.OrderLineAVO.CostPrice, dbo.OrderLineAVO.SellPrice, dbo.OrderLineAVO.TotalCostPrice, dbo.OrderLineAVO.TotalSellPrice, dbo.OrderHeader.OrderType, dbo.AVOOption.AVOID
FROM ((dbo.OrderHeader WITH(NOLOCK) INNER JOIN dbo.OrderLine WITH(NOLOCK) ON dbo.OrderHeader.OrderID = dbo.OrderLine.OrderID) INNER JOIN dbo.OrderLineAVO WITH(NOLOCK) ON dbo.OrderLine.OrderLineID = dbo.OrderLineAVO.OrderLineID) INNER JOIN dbo.AVOOption WITH(NOLOCK) ON dbo.OrderLineAVO.AVOOptionID = dbo.AVOOption.AVOOptionID
WHERE (((dbo.OrderHeader.OrderType)=5) AND ((dbo.AVOOption.AVOID)=5) AND ((dbo.OrderHeader.Deleted)=0) AND ((dbo.AVOOption.Deleted)=0)) OR (((dbo.OrderHeader.OrderType)=6) AND ((dbo.AVOOption.AVOID)=6));

Open in new window

BobIaAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
Ah, just noticed how the OR was used in your original query.  We need to add an extra set of parentheses around the original WHERE conditions, then add the date check:

WHERE ((((dbo.OrderHeader.OrderType)=5) AND ((dbo.AVOOption.AVOID)=5) AND ((dbo.OrderHeader.Deleted)=0) AND ((dbo.AVOOption.Deleted)=0)) OR (((dbo.OrderHeader.OrderType)=6) AND ((dbo.AVOOption.AVOID)=6)))
AND dbo.OrderHeader.DateTimeLastModified >= DATEADD(DAY, CASE WHEN DATEDIFF(DAY, 0, GETDATE()) % 7 = 0 THEN -3 ELSE -1 END, CAST(GETDATE() AS date))
AND dbo.OrderHeader.DateTimeLastModified < DATEADD(DAY, 1, CAST(GETDATE() AS date))
0
 
Scott PletcherSenior DBACommented:
Add this condition to your WHERE clause.  Technically you don't need the "<" part, but if you ever want to run for an earlier date, it could be useful to already have in there:

AND dbo.OrderHeader.DateTimeLastModified >= DATEADD(DAY, CASE WHEN DATEDIFF(DAY, 0, GETDATE()) % 7 = 0 THEN -3 ELSE -1 END, CAST(GETDATE() AS date))
AND dbo.OrderHeader.DateTimeLastModified < DATEADD(DAY, 1, CAST(GETDATE() AS date))

[Btw, note that this code works correctly under any DATEFIRST / language setting.]
0
 
BobIaAuthor Commented:
Scott,
This seems to return anything earlier than "today" when I run it in a select statement.  It does not seem to limit for last three days since today is Monday.

Bob
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
BobIaConnect With a Mentor Author Commented:
Scott,
Thanks!  Had to futz with the parenthesis count but got it.  Nice that it includes antthing from today also, hadn't thought of that but good Idea.  Here is the final code of the "WHERE" code that worked:
WHERE ((((dbo.OrderHeader.OrderType)=5) AND ((dbo.AVOOption.AVOID)=5) AND ((dbo.OrderHeader.Deleted)=0) AND ((dbo.AVOOption.Deleted)=0)) OR (((dbo.OrderHeader.OrderType)=6)
AND ((dbo.AVOOption.AVOID)=6)))
AND (dbo.OrderHeader.DateTimeLastModified >= DATEADD(DAY, CASE WHEN DATEDIFF(DAY, 0, GETDATE()) % 7 = 0 THEN -3 ELSE -1 END, CAST(GETDATE() AS date)) 
AND dbo.OrderHeader.DateTimeLastModified < DATEADD(DAY, 1, CAST(GETDATE() AS date)));

Open in new window

0
 
BobIaAuthor Commented:
This works great and also speeds the query quite a bit. Thanks Scott
0
 
Scott PletcherSenior DBACommented:
You're welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.