Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Use last working day's date

Posted on 2014-10-15
4
Medium Priority
?
287 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:Murray Brown
  • 2
4 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1332 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 32

Accepted Solution

by:
awking00 earned 668 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1332 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:Murray Brown
ID: 40383807
Thanks very much
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

885 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