Skip to Content
avatar image
Former Member

Have date range auto-calc in query/alert

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 01, 2010 at 04:46 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 01, 2010 at 05:29 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded