Is there a way to subtract a number whenever a value is between two other values on SQL

Question

I'm working on a custom column on SQL. We want to see the average datediff but we need to exclude the weekend. In my opinion, subtracting 2 from any value between 7 and 13, subtracting 4 from 14 and 20 etc would be the best way of doing this.

Is there any way to do this?

I figured this would be the query but it's not working.

WHEN TimeToInspect Between 7 and 13 THEN..... not sure how to subtract the two but I figured it would go after that.

Any ideas??

Answer 1

Your method will produce incorrect results. The datediff from Friday to Monday is 3. Since that is less than 7, you wouldn't omit the weekend.

If you want to calculate the number of weekdays between two dates, you need to identify which days are weekdays. The following script will count the number of weekdays between two dates.

declare @StartDateRange date
declare @EndDateRange date
set @StartDateRange = {d '2019-09-01'}
set @EndDateRange = {d '2019-12-31'}
declare @StartDate date
declare @EndDate date
set @StartDate = {d '2019-12-20'}
set @EndDate = {d '2019-12-23'}
--set @StartDate = {d '2019-12-23'}
--set @EndDate = {d '2019-12-25'}

;
with days (i
     , s
     , dt) as 

(
  select 1
  , @StartDateRange
  , @StartDateRange

  union all
  select i + 1
  , d.s
  , dateadd(day, 1, dt)
  from days d
  where dateadd(day, 1, dt) < @EndDateRange
), 
[Date] as (
SELECT dt as FullDate
, case when datepart(weekday, dt) in (1, 7) then 'Weekend'
            else 'Weekday'
    end as WeekdayWeekend
        
FROM days
)

select @StartDate as StartDate
, datename(weekday, @StartDate) as StartDayName
, @EndDate as EndDate
, datename(weekday, @EndDate) as EndDayName
, datediff(day, @StartDate, @EndDate) as DateDiff
, datediff(day, @StartDate, @EndDate) + 1 as DaysBetweenInclusive
, (   select count(*) as weekenddays
    from [Date]
    where FullDate between @StartDate and @EndDate
      and WeekdayWeekend = 'Weekend') as WeekendDays
, (   select count(*) as weekenddays
    from [Date]
    where FullDate between @StartDate and @EndDate
      and WeekdayWeekend = 'Weekday') as WeekDays

option (maxrecursion 0)

The count includes both the begin and end of the requested range. For example, it will return :

StartDate	StartDayName	EndDate	EndDayName	DateDiff	DaysBetweenInclusive	WeekendDays	WeekDays
2019-12-20	Friday	2019-12-23	Monday	3	4	2	2
2019-12-23	Monday	2019-12-25	Wednesday	2	3	0	3
2019-12-21	Saturday	2019-12-28	Saturday	7	8	3	5

You'll need to decide how to adjust this to handle the edge cases.

I'm guessing you'll also want to adjust this to omit other days. I've done this for holidays using the same methodology. My Date table includes

, case
  when datepart(month, dt) = 1 and datepart(day, dt) = 1 /*and datepart(weekday, dt) between 2 and 6*/ then 'New Year''s Day'
  when datepart(month, dt) = 1 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 and datepart(year, dt) > 1985 then 'Martin Luther King Day'
  when datepart(month, dt) = 2 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 then 'Presidents'' Day'
  when datepart(month, dt) = 5 and datepart(day, dt) > 24 and datepart(weekday, dt) = 2 then 'Memorial Day'
  when datepart(month, dt) = 6 and datepart(day, dt) = 19 /*and datepart(weekday, dt) between 2 and 6*/ and datepart(year, dt) > 2021 then 'Juneteenth'
  when datepart(month, dt) = 7 and datepart(day, dt) = 4 /*and datepart(weekday, dt) between 2 and 6*/ then 'Independence Day'
  when datepart(month, dt) = 9 and datepart(day, dt) between 1 and 7 and datepart(weekday, dt) = 2 then 'Labor Day'
  when datepart(month, dt) = 10 and datepart(day, dt) between 8 and 14 and datepart(weekday, dt) = 2 then 'Columbus Day'
  when datepart(month, dt) = 11 and datepart(day, dt) = 11 /*and datepart(weekday, dt) between 2 and 6*/ then 'Veterans Day'
  when datepart(month, dt) = 11 and datepart(day, dt) between 22 and 28 and datepart(weekday, dt) = 5 then 'Thanksgiving Day'
  when datepart(month, dt) = 11 and datepart(day, dateadd(day, -1, dt)) between 22 and 28 and datepart(weekday, dateadd(day, -1, dt)) = 5 and datepart(year, dt) < 2014 then 'Thanksgiving, Day After'
  when datepart(month, dt) = 11 and datepart(day, dateadd(day, -1, dt)) between 22 and 28 and datepart(weekday, dateadd(day, -1, dt)) = 5 and datepart(year, dt) > 2013 then 'Native American Heritage Day'
  when datepart(month, dt) = 12 and datepart(day, dt) = 25 /*and datepart(weekday, dt) between 2 and 6*/ then 'Christmas Day'
  end as Holiday

Excluding holidays (or whatever your other omissions may be) would change the calculation:

, datediff(day, @StartDate, @EndDate) + 1
  - (   select count(*) as weekenddays
    from [Date]
    where FullDate between @StartDate and @EndDate
      and WeekdayWeekend = 'Weekend')
  - (   select count(*) as holidays
    from [Date]
    where FullDate between @StartDate and @EndDate
      and Holiday is not null) as WorkDaysBetween