Select Aging Data by Hour but Exclude Weekend Hours: SQL 2008R2

Question

I'm attempting to view records in a table for prioritizing. Ultimately, I'd like to view data records that are older than 72 hours old. However, I do not want Saturday and Sunday Hours to be calculated in the return.

I started with using datepart like this:

SELECT [Record] 
FROM [TableD] D 
WHERE DATEPART(w,D.[LastChange]) NOT IN (7,1) 
AND D.LastChange <= GETDATE()-3

The problem is, there could be a change on a weekend by someone updating a record or a system update. But for reporting, I do not want to calculate 00:00:00 to 11:59 Saturday or Sunday as part of the 72 hour window. So the above isn't quite what I need and I'm not sure what the best method is to get the return I'm looking for.

Adding additional clarification:

Let's say that I want to pull all the records in a table that have not been updated in 48 hours. Records in this table are supposed to be worked within a window of time and escalation needs to occur on those records that haven't been updated. However, the 48 Hours of Saturday and Sunday do not count. So I would not want a select result of a record that was updated at 7:00pm on Friday as my result when I ran the query Monday morning at 9:00am.

The question is quite vague. Do you want to exclude all records that have a `LastChange` value that is on a weekend? — Daniel B, Jul 17 '14 at 13:12
No, I don't want to exclude all records that have a lastchange date of a weekend because there could be a user that does something to the record/system update. BUT, Let's say I want to pull an escalation report so I want to see how many hours it has been since someone "last" changed the ticket. A ticket came in on Fri at 4:00pm. However, 00:00:00 to 11:59:59 (1) Sun and 00:00:00 to 11:59:59 (7) Sat should not be calculated into the return. So, if it is Mon Morning. I'd only see the hours of Friday till 11:59:59 counting toward hours. I wouldn't see the 48 of the weekend in my calculation. — SQL_Noob, Jul 17 '14 at 13:18

Kiran Hegde · Answer 1 · 2014-07-17T14:33:05.843

0

You can add case statement to your where query.

SELECT [Record] 
FROM [TableD] D 
WHERE  D.LastChange <=  CASE WHEN DATEPART(W,GETDATE()) = 1  
                                  THEN GETDATE() - 4
                             WHEN DATEPART(W,GETDATE()) <= 4   
                                  THEN GETDATE() - 5
                             ELSE GETDATE() - 3 END

edited Jul 17 '14 at 14:33

answered Jul 17 '14 at 13:20

Kiran Hegde

3,651
1
16
14

score 0 · Answer 2 · edited May 23 '17 at 12:13

You have to change your WHERE like this:

SELECT [Record]
FROM [TableD] D
WHERE ((DATEPART(DW, D.[LastChange]) + @@DATEFIRST) % 7) NOT IN (0, 1) AND
    DATEDIFF(DAY, D.[LastChange], GETDATE()) <
        (CASE WHEN ((DATEPART(DW, GETDATE()) + @@DATEFIRST) % 7) IN (2, 3) THEN 5 ELSE 3 END)

First part of WHERE excludes weekend days in a sql server query using @@DATEFIRST.

Second part of WHERE is used to cover the exact number of days on MONDAY and TUESDAY.

As stated in the first link that I posted "when dealing with day-of-week calculations, it's important to take account of the current DATEFIRST settings."

Select Aging Data by Hour but Exclude Weekend Hours: SQL 2008R2

2 Answers2