Okay - I have a report that automatically runs every Monday. The dataset is all Invoices from the previous Saturday (9 days ago) thru last Friday (3 days ago).
it's only accurate on Monday because I am using the Get Date minus days formula.
Currently:
Select
'APFM' Company,
B.USERDEF2 YGLMasterNumber, A.CUSTNMBR as GPCustId, A.CUSTNAME as CustomerName,
Case when A.SOPTYPE='3' then 'Invoice'
when A.SOPTYPE='4' then 'Return' else 'NA' end as Type,
B.CPRCSTNM as ParentID,
A.SOPNUMBE DocNumber,
CAST(A.DOCDATE as Date) DocDate,
'Posted' Status
from APFM.dbo.SOP30200 A
left outer join APFM.dbo.RM00101 B
on A.CUSTNMBR=B.CUSTNMBR
where DOCDATE BETWEEN dateadd(day, -9, cast(GETDATE() as date)) AND dateadd(day, -3, cast(GETDATE() as date))
AND B.USERDEF2 in ('167','7885','890','9834','1440954','17','157351') and A.SOPTYPE in ('3','4')
AND A.VOIDSTTS='0'
My question is - Can I change the following where clause to work based on days - Basically last saturday to last friday regardless of what day of week I run it?
"where DOCDATE BETWEEN dateadd(day, -9, cast(GETDATE() as date)) AND dateadd(day, -3, cast(GETDATE() as date))"
Any guidance would be appreciated.........I researched it and could not find a way to do it in a where clause. Everything seemed to suggest using stored procedure - declaring a variable etc.