btamulis 6/22/2022 11:08:39 AM

SQL Date question

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.

 

Version: All
Section: Dynamics GP, SQL for Beginners, SQL Scripts


Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables
3