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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Scott PletcherSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

BobIaAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.