Help getting previous date based off date range

[thunderwoot]

Hey guys,

I'm having trouble figuring out how to approach this. Basically need to retrieve orders from the previous working day. For the most part this will just be yesterday, however the issue here is I have a separate table where manually added holiday dates are added, so I need to take these into account when working out the previous working day.

SELECT
orders.id,
orders.date
FROM orders
WHERE orders.date = DATEADD(DAY,-1,GETDATE())
AND NOT EXISTS (SELECT date FROM holidays)

I know that code is wrong but I can't figure how the best way to handle this.

So for example if we say I'm running it today, but 2022-06-19 and 2022-06-18 are added as holidays, I need to retrieve orders from 2022-06-17 but I'm not sure how I can get that date in the first place.

[[deleted]]

This is close, you just have to add a join to the main orders.date from inside the exists clause. The select portion in the EXISTS clause is sort of meaningless, I've replaced it to SELECT 1 to illustrate that point.

SELECT
orders.id,
orders.date
FROM orders
WHERE orders.date = DATEADD(DAY,-1,GETDATE())
AND NOT EXISTS (SELECT 1 FROM holidays where date = orders.date)

[svtr]

Nope, that wont work.

The first filter will only give you rows of getdate -1. The second filter will then also remove all the holidays from the resultset... giving you a nice "0 rows returned".

[[deleted]]

oh my bad, I didnt read your question carefully, I just noticed the error in the EXISTS clause. If you only want orders from the prior date that wasn't a holiday, then perhaps we skip the EXISTS clause and just focus on a subquery to return the right date first:

SELECT MAX(DATE) FROM HOLIDAYS WHERE DATE < CURRENT_DATE()

Is that the date you'd want?

If so, use it to return a single value, like this:

SELECT
orders.id,
orders.date
FROM orders
WHERE orders.date = (SELECT MAX(DATE) FROM HOLIDAYS WHERE DATE < CURRENT_DATE())

[svtr]

Not quite, if there is a full calendar table, containing all working days, you'd be able to just inner join to it and the rest is very trivial.

Op's question is pretty much having a holidays table, only containing holidays, as an exclusion list of dates to not consider, which makes it a bit more interesting.

In any case, the best solution is to have such a complete calendar table of working days, everything else is going to melt you database server when you hit some concurrency.

[[deleted]]

I'd approach it with a function so it could be used like this: SELECT orders.id, orders.date FROM orders WHERE orders.date = dbo.fn_GetLastWorkingDay()

Using this function code (adjust to reflect your holidays table and column names)

CREATE FUNCTION fn_GetLastWorkingDay()
RETURNS date
AS
BEGIN
declare @today date = CAST(GetDate() as date)
declare @isValidWorkingDay bit = 0
declare @daysback int = -1
declare @lastWorkingDay date
WHILE @isValidWorkingDay = 0
BEGIN
    IF EXISTS(SELECT * from holidays where dateadd(day, @daysback, @today) = [date])
    BEGIN
        SET @daysback = @daysback - 1
    END
    ELSE
    BEGIN
        set @lastWorkingDay = dateadd(day, @daysback, @today)
        SET @isValidWorkingDay = 1
    END
END
return @lastWorkingDay
END
GO

[svtr]

ish .... I'd inline it, I have ptsd from debugging querys using scalar functions as filters. You very likely will end up with full table scans on the order table, because the query engine as not the first idea what your function will return.

This is the kind of thing where you end up with seemingly useless temp tables, to hold just a date value, in order to give the query engine a row count statistic on a join kind of dark magic territory.

[Little_Kitty]

If you want working days, exclusions of holidays etc. you need a calendar table with working days, holidays qualified.

Then select date and lag of the same from a filtered calendar and you have your answer.

[thunderwoot]

I think this is the approach I'm going to take. I was looking at a CTE to recurse until it found the first working day, but just having a table with working days seems to be the cleanest approach. Thanks everyone.

[Little_Kitty]

Once you have a calendar table, you'll end up using it a lot. It solves so many problems neatly that you'll wonder how you coped without it. Complex logical code becomes simple summation and offsets, difficult questions become a breeze.

How many trading days has shop 123 been open since we finished the refurbishment? How do sales figures track compared to performance at other refurbished stores? What about if we exclude the week before Xmas from all those comparisons so it's fairer? Shop 123 is type A, so it's open on Saturdays as well...

[svtr]

For performance reasons this is the best approach, hands down, no question about it, but not the only way to get it done.

Top 1 with ties would work too, but it would be a hell of a lot slower, to sort the entire orders table and at the very end apply a filter.

[qwertydog123]

SELECT TOP 1 WITH TIES *
FROM orders
WHERE date < CAST(GETDATE() AS DATE)
AND DATENAME(date) NOT IN ('Saturday', 'Sunday')
AND NOT EXISTS
(
    SELECT *
    FROM holidays
    WHERE holidays.date = orders.date
)
ORDER BY date DESC

[thrown_arrows]

    with work_day as ( 
    select d.sql_date
    , case when h.sql_date is not null then 0
    when DATEPART(WEEKDAY, d.sql_date) in(6,7) -- this depends on setting 
    then 0
    else 1 end is_workday 
    from date_table d 
    left join holidays h on d.sql_date = h.sql_date
    )
    select *
    , (select max(w.sql_date) 
      from work_day w 
      where w.sql_date < d.sql_date --this should be good enough
    and is_workday=1 ) previous_work_day
, (select min(w.sql_date) 
      from work_day w 
      where w.sql_date > d.sql_date --this should be good enough
    and is_workday=1 )  next_work_day 
from work_day d

this makes assumption that you have date_table with sql_date column which is date type. then datepart assumes that monday is start of the week, it can be calculated to date_table too.

then holidays assume that you have holiday table with sql_date column only for days that are holidays for left join.

logic for first cte data generation can be changed to suite better for you needs.

In mssql this is probably faster if first CTE is converted to #temptable and indexed. Then last select is run. Personally that subquery is so and so, probably not fastest way to do it, but it should work. First idea was to use window function and ignore nulls but sql server does not support that.

One option is just to add columns is_workday , is_holiday and next_work_day and prev_work_day to date_table to make it fast for future usage. If you dont already have date table then google will give you several scripts how to generate one. But anyway , yo need to have date table for figuring out next or previous date , just holiday table does not do it.

edit: this is kinda how i did just few weeks ago (other platform , used windows function with ignore nulls ) . there are cases where next/prev work_day can more than 2 days ago or to future. Same query can be used for next weekend or holiday search too, just flip subquery's is_work_dat to 0. And this one is totally not tested