Solved

SQL Use last working day's date

Posted on 2014-10-15
4
217 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:Scott Pletcher
Scott Pletcher 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 32

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:Scott Pletcher
Scott Pletcher 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select single row of data for each ID in Select Statement 7 28
SQL Quer 4 23
SQL Group By Question 4 20
Help With SQL Query 9 33
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

828 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