Have date range auto-calc in query/alert

[Former Member]

Hi,

Each Monday, I want a list of the previous week's sales orders. I have the query below and enter the date range value. What I need is the date to be calculated so I can use it for an alert. I want the date range to be based on the current date less 7 (days) and current date less 1 day. I tried with the following but can't figure out the syntax for the date part:

SELECT T0.[DocNum] as 'SO No.', T0.[DocDate] as 'Date', T0.[CardCode] as 'CustCode', T0.[CardName] as 'Custome Name', T0.[NumAtCard] as 'Ref/PO No.', T0.[DocCur] as 'Currency', (T0.[DocTotal] - T0.[VatSum]) as 'SO Amt'

FROM ORDR T0

WHERE T0.[DocDate] >= Date1 and T0.[DocDate] <= Date2

Date1 = currentdate - 7 days

Date2 = currentdate - 1 day

Any assistance would be appreciated.

Thank you,

Heather

[Former Member · Accepted solution]

Hi Heather,

Try this one:

SELECT T0.DocNum as 'SO No.', T0.DocDate as 'Date', T0.CardCode as 'CustCode', T0.CardName as 'Customer Name',

T0.NumAtCard as 'Ref/PO No.', T0.DocCur as 'Currency', (T0.DocTotal - T0.VatSum) as 'SO Amt'

FROM dbo.ORDR T0

WHERE DateDiff(DD, T0.DocDate, GetDate()) > 0 AND DateDiff(DD, T0.DocDate, GetDate()) < 8

Thanks,

Gordon

[Former Member]

Hi Heather,

Yes, Gordon is correct.

Try this,

SELECT T0.DocNum as 'SO No.', T0.DocDate as 'Date', DateDiff(DD, T0.DocDate, 
GetDate()) AS 'DayDiff', T0.CardCode as 'Customer Code', T0.CardName as 'Customer Name', 
T0.NumAtCard as 'Ref/PO No.', T0.DocCur as 'Currency', (T0.DocTotal - T0.VatSum) as 'SO Amt'
FROM OINV T0
WHERE 
DateDiff(DD, T0.DocDate, GetDate()) >= '1' AND DateDiff(DD, T0.DocDate, GetDate()) <= '7'

Regards,

Madhan.