Going back in time from today's date, but not including weekend saturday and sunday dates

Transact-SQL
1

Awhile back, I asked about finding the number of working days between 2 dates , with the total not including Saturday and sunday dates; now I have to merely go back in time from say today getdate() and gather all the records from 10 working days as an example; so if I were to say today april3 is my getdate() date , I would need to have as the starting date march 21 2017

select * from mytable where timeofevent >= " 10 working days" ,

but now I am getting confused with this DATEDIFF logic, I need something that goes back to 10 days (ideally march 21, 2017):

DATEADD(DAY, -10 + CASE DATEDIFF(DAY, 0, GETDATE()) % 7 WHEN 0 THEN 1 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END, GETDATE())

???
thanks in advance

2

Something like:

dateadd(day,(datediff(day,0,current_timestamp)*5/7-10)*7/5,0)
3

For just calendar days, not worried about holidays, then this should do it:

DECLARE @base_date date
DECLARE @10_days_ago date

SET @base_date = GETDATE()
SET @10_days_ago = DATEADD(DAY, CASE DATEDIFF(DAY, 0, @base_date) % 7 
    WHEN 4 THEN -11 WHEN 5 THEN -12 ELSE -13 END, @base_date)

SELECT @base_date, @10_days_ago
4

This returns the last 10 weekdays:

WITH TenBusDays(DayDate)
AS
(
SELECT Cast(GetDate() AS date)
UNION ALL
SELECT DateAdd(dd, -1*number, DayDate)
FROM TenBusDays
CROSS APPLY master.dbo.spt_values sv
WHERE sv.type='P'
AND sv.number BETWEEN 1 AND 20
)
SELECT TOP 10
   DayDate
FROM
   TenBusDays
WHERE
   DatePart(dw, DayDate) NOT IN (1, 7);
5

sql2017:
If you insist of using a looping-style method of coding for this,I'd very strongly urge you not to read from the master db, or expect it to always contain what's currently available for you to see in it. Obviously you have no control over the views or the data in that db. Instead, use your own inline or physical tally table.

6

0% nested loops

1290×347 30 KB

You have a point a tally table would remove the dependency in a system table

Here's a version using a Tally table.

WITH Tally(TallyNumber)
AS
(
SELECT n FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14))t(n)
)
,TenBusDays(DayDate)
AS
(
SELECT DateAdd(dd, -1*TallyNumber, Cast(GetDate() AS date))
FROM Tally

)
SELECT TOP 10
   DayDate
FROM
   TenBusDays
WHERE
   DatePart(dw, DayDate) NOT IN (1, 7);

1 Like