CODEWITHSUNDEEP
sqlsql-servert-sqlcoalesce
jimmy
jimmy

Reputation: 9

Join to previous weekday skipping weekends

I am trying to join data based on the weekday prior to the [Date_Pulled] column. For instance, on Mondays[Date_Pulled] I want to pull Fridays[date] data and Tuesdays[date] I want to pull Monday's[Date_pulled] data.

I do this to avoid nulls, since we do not generate rates on weekends.

Heres what I've tried:

select a.lane, a.them, b.us a.date_pulled, b.date
from table a
left join table2 b on a.lane = b.lane    
    AND A.[DATE_PULLED] = COALESCE(DATEADD(DAY,+1,B.[DATE]),(DATEADD(DAY,+3,B.[DATE])))

enter image description here

Upvotes: 0

Views: 218

Answers (2)

Dale K
Dale K

Reputation: 27202

You can use a case expression to work out how many days back to look e.g.

select a.lane, a.them, b.us a.date_pulled, b.[date]
from a
inner join b on a.lane = b.lane
    and b.[date] = dateadd(day, case when datepart(weekday, a.date_pulled) = 2 /*Monday*/ then -3 else -1 end, a.date_pulled)

Note: assumes DATEFIRST set to 7, Sunday. Adjust to suit your system.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269533

Something like this:

from a join
     b
     on (b.date = dateadd(day, -1, a.date) and datename(weekday, a.date) <> 'Monday') or
        (b.date = dateadd(day, -3, a.date) and datename(weekday, a.date) = 'Monday')

Note that this assumes English internationalization settings for the weekday name.

Upvotes: 1

Related Questions