sql - All data 10 days past from today, excluding weekends, and starting 2 WORKDAYS prior - Stack Overflow

admin管理员组

文章数量:1353657

I need the above info.

I have a date column DATETIME.

I need all data 10 days prior to now, excluding weekends, and starting two WORKDAYS prior.

This is the problem I'm having, because with weekends involved, the following query does not work with regard to pulling data on Mondays and Tuesdays (the starting date of which would have to be Thursday and Friday of the prior week respectively).

Any ideas?

Select * from prtran00 

where DATEPART(dw, DateTime) NOT IN (7, 1)
and DateTime >= Dateadd(day, -8, cast(getdate() as date))
and DateTime <= Dateadd(day, -1, cast(getdate() as date))

*This query works with all other days of the week besides Mon, Tues.

I need the above info.

I have a date column DATETIME.

I need all data 10 days prior to now, excluding weekends, and starting two WORKDAYS prior.

This is the problem I'm having, because with weekends involved, the following query does not work with regard to pulling data on Mondays and Tuesdays (the starting date of which would have to be Thursday and Friday of the prior week respectively).

Any ideas?

Select * from prtran00 

where DATEPART(dw, DateTime) NOT IN (7, 1)
and DateTime >= Dateadd(day, -8, cast(getdate() as date))
and DateTime <= Dateadd(day, -1, cast(getdate() as date))

*This query works with all other days of the week besides Mon, Tues.

• sql
• sql-server
• datetime

Share Improve this question Follow edited Mar 31 at 17:54 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Mar 31 at 16:41 MICHAELG1117MICHAELG1117 1111 silver badge33 bronze badges 7

• 5 A calendar table is what you really need. – Thom A Commented Mar 31 at 16:50
• @Thom A - I got it in Excel, looks like this =IF(WEEKDAY(AA5)>3,CEILING(AA5-2,1), CEILING(AA5-4,1)) Basically, when it's Monday or Tuesday then subtract 4 days but if it's all other days then start with - 2 days. Don't know if I could use a CASE WHEN in a where clause to do something like this. I tried but it's giving me a BOOLEAN error. Where DATEPART(dw, DateTime) > 1 then getdate()-2 when DATEPART(dw, DateTime) <=1 then getdate()-4 else 0 end Thanks - – MICHAELG1117 Commented Mar 31 at 17:14
• This fiddle might help you get started dbfiddle.uk/UB2VsGhz – Bart McEndree Commented Mar 31 at 17:30
• Some example data and expected results would really help clarify your needs. – Bart McEndree Commented Mar 31 at 18:47
• @Bart McEndree - cool, give me a sec -- appreciate all of the answers ! – MICHAELG1117 Commented Mar 31 at 18:50

 |  Show 2 more comments

3 Answers 3

Sorted by: Reset to default 0

Joining your data with a recursively generated calendar might help solve your problem.

DECLARE @StartDate  date = GetDate()
  
;WITH seq(n,CalendarDate) AS 
(
  --Generate a series from 1 to 18
  SELECT 1, @StartDate CalendarDate
  UNION ALL 
  SELECT n + 1, DATEADD(DAY, -n,  @StartDate) CalendarDate
  FROM seq
  WHERE n < DATEDIFF(DAY, DateAdd(d,-18, @StartDate), @StartDate )  
),
NumberedWordays AS 
(
  --Keep only the workdays (Monday thru Friday) and number the rows
  SELECT ROW_NUMBER() OVER (ORDER BY seq.CalendarDate desc) rn,  seq.CalendarDate 
  FROM seq
  WHERE DATEPART(dw, seq.CalendarDate) NOT IN (7, 1)  --Skip weekends 
)
SELECT top 10 nw.rn DaysBack, nw.CalendarDate, DATENAME(dw,nw.CalendarDate) as DayOfWeek, p.DATETIME
FROM NumberedWordays nw
--Join your data with the calendar
LEFT JOIN prtran00 p ON DATEADD(dd, 0, DATEDIFF(dd, 0, P.DATETIME))=nw.CalendarDate
WHERE nw.rn > 2  --Start 2 workdays ago (skip days with rn = 1 and 2)

fiddle

Simply differentiate mondays and tuesdays

The logics you put in your first comment ("I tried but it's giving me a BOOLEAN error") was right,
you just needed to apply it to the window (DateTime >= … and DateTime <= …) instead of to the where.

Here it is in your query, notice the window that changes from [-8;-1] to [-10;-3] on mondays and tuesdays
(theorically we would just need -1 for tuesdays, but -2 still works as the DATEPART(dw, DateTime) NOT IN (7, 1) will then eliminate the unwanted day)
(note that I transformed the <= to <, to avoid 00:00:00 being counted twice; else you could have simplified your DateTime >= … and DateTime <= … to DateTime between … and …):

Select * from prtran00
where DATEPART(dw, DateTime) NOT IN (7, 1)
and DateTime >= Dateadd(day, case when datepart(dw, today) in (2, 3) then -10 else -8 end, cast(getdate() as date))
and DateTime < Dateadd(day, case when datepart(dw, today) in (2, 3) then -3 else -1 end, cast(getdate() as date))

When simulating with future "getdate()s", this gets:

we would select from	until	If we were on
03-31 Monday	04-04 Friday	04-08 Tuesday
04-01 Tuesday	04-07 Monday	04-09 Wednesday
04-02 Wednesday	04-08 Tuesday	04-10 Thursday
04-03 Thursday	04-09 Wednesday	04-11 Friday
04-04 Friday	04-10 Thursday	04-14 Monday
04-07 Monday	04-11 Friday	04-15 Tuesday
04-08 Tuesday	04-14 Monday	04-16 Wednesday
04-09 Wednesday	04-15 Tuesday	04-17 Thursday
04-10 Thursday	04-16 Wednesday	04-18 Friday
04-11 Friday	04-17 Thursday	04-21 Monday

/!\ Although this solution strictly respects what you asked in the question, it will not handle public holidays;
if such a need arises, then a recursive or calendar table solution will be the only possibility.

Best approach is is using calendar table. If you want to take into account not only weekends but also holidays, you can't do without it.

Without calendar table you can calculate directly shift day count for every day of week.

Expression (datepart(dw,dt)+@@datefirst)%7+1 c is invariant number for day of week. This value does not depend on the settings of @@datefirst and the names of the days of the week.
So

   day of week Sat Sun Mon Tue Wed Thu Fri  -- day of week
   Constant c   1   2   3   4   5   6   7   -- day of week id (c)
   choose  (c,  2  ,3  ,4  ,4  ,2  ,2  ,2)  -- shift back 2 work day from current day
   choose  (c, 12 ,13 ,14 ,14 ,14 ,14 ,14)  -- shift back 10 work day from current day

Shift from date to 2 work days before is constant for every day of week. We take this shift as choose(c, 2 ,3 ,4 ,4 ,2 ,2 ,2) dif1.
Shift from date to 10 work days before is constant for every day of week. Use expression choose(c, 12 ,13 ,14 ,14 ,14 ,14 ,14) dif2.

Perhaps this is similar to your calculations in Excel.

select * ,datename(dw,startDt)startDn,datename(dw,endDt)endDn
from(
select id,dt,datename(dw,dt)dn,c
                       --Sat Sun Mon Tue Wed Thu Fri
                       -- 1   2   3   4   5   6   7
  ,dateadd(day,-choose(c, 12 ,13 ,14 ,14 ,14 ,14 ,14), dt) startDt
                       --Sat Sun Mon Tue Wed Thu Fri
                       -- 1   2   3   4   5   6   7
  ,dateadd(day,-choose(c, 2  ,3  ,4  ,4  ,2  ,2  ,2) , dt) endDt
from (select id,dt,datename(dw,dt)dn,(datepart(dw,dt)+@@datefirst)%7+1 c from test)t
)a

id	dt	dn	c	startDt	dif2	endDt	dif1	startDn	endDn
1	2025-03-31	Monday	3	2025-03-17	14	2025-03-27	4	Monday	Thursday
1	2025-03-30	Sunday	2	2025-03-17	13	2025-03-27	3	Monday	Thursday
1	2025-03-29	Saturday	1	2025-03-17	12	2025-03-27	2	Monday	Thursday
1	2025-03-28	Friday	7	2025-03-14	14	2025-03-26	2	Friday	Wednesday
1	2025-03-27	Thursday	6	2025-03-13	14	2025-03-25	2	Thursday	Tuesday
1	2025-03-26	Wednesday	5	2025-03-12	14	2025-03-24	2	Wednesday	Monday
1	2025-03-25	Tuesday	4	2025-03-11	14	2025-03-21	4	Tuesday	Friday
1	2025-03-24	Monday	3	2025-03-10	14	2025-03-20	4	Monday	Thursday

For test table

create table test (id int,dt date);
insert into test values
 (1,'2025-03-31')
,(1,'2025-03-30')
,(1,'2025-03-29')
,(1,'2025-03-28')
,(1,'2025-03-27')
,(1,'2025-03-26')
,(1,'2025-03-25')
,(1,'2025-03-24')
;

For one date ("current day")

declare @calcDate date=cast('2025-03-31' as date);

select * ,datename(dw,startDt)startDn,datename(dw,endDt)endDn
from(
select dt,datename(dw,dt)dn,c
  ,dateadd(day,-choose(c, 12 ,13 ,14 ,14 ,14 ,14 ,14), dt) startDt
  ,dateadd(day,-choose(c, 2  ,3  ,4  ,4  ,2  ,2  ,2) , dt) endDt
from (select @calcDate dt,datename(dw,@calcDate)dn
        ,(datepart(dw,@calcDate)+@@datefirst)%7+1 c )t
)a

dt	dn	c	startDt	dif2	endDt	dif1	startDn	endDn
2025-03-31	Monday	3	2025-03-17	14	2025-03-27	4	Monday	Thursday

Your query may be like

declare @calcDate date=cast('2025-03-31' as date);

select * 
from prtran00
cross apply
  (select dateadd(day,-choose(c, 12 ,13 ,14 ,14 ,14 ,14 ,14), dt) startDt
     ,dateadd(day,-choose(c, 2  ,3  ,4  ,4  ,2  ,2  ,2) , dt) endDt
   from (select @calcDate dt ,(datepart(dw,@calcDate)+@@datefirst)%7+1 c )t
)a
where DATEPART(dw, DateTime) NOT IN (7, 1)
  and datetime between startDt and endDt

fiddle

本文标签：