Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Use last working day's date

Posted on 2014-10-15
4
Medium Priority
?
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 69

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 69

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

730 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