If current date is a certain day of the week, pull results from a different day

Question

I'm working on a SQL query that pulls results for everyone who had appointments at a clinic on a given day. I run this query daily for all appointments that took place the previous business day. The query is pretty basic:

SELECT
[a bunch of demographic and appointment-related fields]

FROM Demographics d JOIN ApptInfo ai ON d.PatientID = ai.PatientID

WHERE (ai.ApptDate >= DATEADD(dd,DATEDIFF(dd,1,GETDATE()),0)
      AND ai.ApptDate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))

This works great so far, unless I'm running the query on a Monday. In that case, I need to be able to pull all the results from the previous Friday and Saturday, since the clinic is also open for half days on Saturdays. Obviously I can't do that by only looking at the previous day, since that would be a Sunday.

I can see the logic pretty clearly in my head for what I want to do, but I'm unsure how exactly to set it up. It needs to be something along these lines:

WHERE
    (CASE
        WHEN DATENAME(WEEKDAY, GETDATE()) = 'Monday' THEN [pull results where ApptDate is 3 days ago OR 2 days ago]
        ELSE [the same code shown above that pulls the previous day]
     END)

Am I on the right track? The alternative of course would be to just manually plug in whatever dates I'm trying to look at, but it would be awesome to be able to run this query daily and have it be completely automated, even on Mondays.

Answer 1

Honestly, parametrising the dates would likely be better for readability. You can still do it in the same batch though.

I use a somewhat "round about" way (see this answer by Greg Hewgill) to check what weekday it is because I want to ensure that the same result is given regardless of the language/DATEFIRST setting:

DECLARE @DateFrom date = CASE (DATEPART(WEEKDAY,GETDATE()) + @@DATEFIRST + 5) % 7 + 1 WHEN 1 THEN DATEADD(DAY, -3,GETDATE()) ELSE DATEADD(DAY, -1,GETDATE()) END,
        @Dateto date = GETDATE(); --Considering there is no data on a Sunday, this is "fine"

--SELECT @DateFrom, @Dateto

SELECT [a bunch of demographic and appointment-related fields]
FROM dbo.Demographics D
     JOIN dbo.ApptInfo AI ON D.PatientID = AI.PatientID
WHERE AI.ApptDate >= @DateFrom
  AND AI.ApptDate < @Dateto;

If you "must" do it without variables, then it would look something like this:

SELECT [a bunch of demographic and appointment-related fields]
FROM dbo.Demographics D
     JOIN dbo.ApptInfo AI ON D.PatientID = AI.PatientID
WHERE AI.ApptDate >= CONVERT(date,CASE (DATEPART(WEEKDAY,GETDATE()) + @@DATEFIRST + 5) % 7 + 1 WHEN 1 THEN DATEADD(DAY, -3,GETDATE())
                                                                                                ELSE DATEADD(DAY, -1,GETDATE())
                                  END)
  AND AI.ApptDate < CONVERT(date,GETDATE());