Solved

SQL Use last working day's date

Posted on 2014-10-15
4
182 Views
Last Modified: 2014-10-16
Hi

I have a table called Performance, which has a date column called PDate.
I want to select all columns (Select * From Performance)
for the last working day
So if today is a Monday it will look at the last Friday and for all
other day's it will look one day back
How will the the SQL Statement look
0
Comment
Question by:murbro
  • 2
4 Comments
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 333 total points
ID: 40382539
SELECT *
FROM dbo.Performance
WHERE
    PDate >= DATEADD(DAY, CASE DATEDIFF(DAY, 0, GETDATE()) % 7 WHEN 0 THEN -3 ELSE -1 END, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AND
    PDate < DATEADD(DAY, 1, DATEADD(DAY, CASE DATEDIFF(DAY, 0, GETDATE()) % 7 WHEN 0 THEN -3 ELSE -1 END, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)))


Btw, if that is typically how you query this table, by PDate, you should cluster the table on PDate.
0
 
LVL 31

Accepted Solution

by:
awking00 earned 167 total points
ID: 40382552
select * from performance where PDate =
case when datename(dw,getdate()) = 'Monday' then dateadd(day,-3,getdate())
     when datename(dw,getdate()) = 'Sunday' then dateadd(day,-2,getdate())
     else dateadd(d,-1,getdate())
end;
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 333 total points
ID: 40382585
I'd probably add a CTE (or CROSS APPLY) to do the date calculation, both to make the code more self-documenting and to keep the final query to only its core elements.

I don't use setting-dependent values, such as 'Monday', if I can avoid it.  The technique I use is valid for any SQL settings, of COLLATION/case sensitivity, LANGUAGE, DATEFIRST, etc..

For date/datetime data types, I use the >= and < approach, as it's accurate even if the underlying data type changes, such as from date to datetime, or datetime to datetime2, etc..


;WITH cteLastBusinessDay (
    SELECT DATEADD(DAY,
        CASE DATEDIFF(DAY, 0, GETDATE()) % 7
            WHEN 6 THEN -2 --Sunday
            WHEN 0 THEN -3 --Monday
            ELSE -1 END,
       DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS LastBusinessDay
)
SELECT *
FROM dbo.Performance
 WHERE
     PDate >= LastBusinessDay AND
     PDate < DATEADD(DAY, 1, LastBusinessDay)
0
 

Author Closing Comment

by:murbro
ID: 40383807
Thanks very much
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now