Skip to Content
avatar image
Former Member

How do I set that I want the last month data only in this query

SELECTT0.DocEntry,T0.DocNum,T0.ObjType,T0.DocDate,(T0.DocTotal-T0.VatSum)AsDocTotal,T0.TrnspCode,T1.[TrnspName],T2.[GroupCode],T3.[GroupName]

FROMOINVT0INNERJOINOSHPT1ONT0.[TrnspCode]=T1.[TrnspCode]

INNERJOINOCRDT2ONT0.[CardCode]=T2.[CardCode]

INNERJOINOCRGT3ONT3.[GroupCode]=T2.[GroupCode]

UNIONALL

SELECTT0.DocEntry,T0.DocNum,T0.ObjType,T0.DocDate,(T0.DocTotal-T0.VatSum)*-1 AsDocTotal,T0.TrnspCode,T1.[TrnspName],T2.[GroupCode],T3.[GroupName]

FROMORINT0INNERJOINOSHPT1ONT0.[TrnspCode]=T1.[TrnspCode]

INNERJOINOCRDT2ONT0.[CardCode]=T2.[CardCode]

INNERJOINOCRGT3ONT3.[GroupCode]=T2.[GroupCode]




I want to see the sales depening on the Delivery style every month and set an alerte on the 1st of the month for the month that just past

I've tried this :

DECLARE @MyDate DATE
SET @MyDate = DATEADD(Month, -1, GETDATE());

but can't make the rest work

Thank You

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • May 16, 2017 at 06:33 AM

    Hi Pascal,

    You were on the right track with the date, but you do not necessarily have to use a variable. To easily apply this parameter, you can encapsulate your query in an outer query:

    SELECT *
    FROM
    (SELECT T0.DocEntry
           ,T0.DocNum
           ,T0.ObjType
           ,T0.DocDate
           ,(T0.DocTotal-T0.VatSum) As DocTotal
           ,T0.TrnspCode
           ,T1.[TrnspName]
           ,T2.[GroupCode]
           ,T3.[GroupName]
     FROM OINV T0
          INNER JOIN OSHP T1 ON T0.[TrnspCode]=T1.[TrnspCode]
          INNER JOIN OCRD T2 ON T0.[CardCode]=T2.[CardCode]
          INNER JOIN OCRG T3 ON T3.[GroupCode]=T2.[GroupCode]
     UNION ALL
     SELECT T0.DocEntry
           ,T0.DocNum
           ,T0.ObjType
           ,T0.DocDate
           ,(T0.DocTotal-T0.VatSum) * -1 As DocTotal
           ,T0.TrnspCode
           ,T1.[TrnspName]
           ,T2.[GroupCode]
           ,T3.[GroupName]
     FROM ORIN T0
          INNER JOIN OSHP T1 ON T0.[TrnspCode]=T1.[TrnspCode] 
          INNER JOIN OCRD T2 ON T0.[CardCode]=T2.[CardCode]
          INNER JOIN OCRG T3 ON T3.[GroupCode]=T2.[GroupCode]) X
     WHERE X.DocDate >= DATEADD(Month, -1, GETDATE())

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks a lot for your help Johan

      I feel like we are a lot closer but it give me data From april 18 to May 16


      I'd like to get all the data from the previous month :April 1st to April 30th
      Or March 1st to March 31th
      Thank You

  • avatar image
    Former Member
    May 16, 2017 at 08:04 PM

    Thanks a lot for your help Johan

    I feel like we are a lot closer but it give me data From april 18 to May 16


    I'd like to get all the data from the previous month :April 1st to April 30th
    Or March 1st to March 31th
    Thank You

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Pascal,

      I understand what you mean, the query above gives you the data for one month worth of days backwards. It will work for you if you only run it on the first day of the month as you described, but now when you test it somewhere in the middle of the month, you will indeed get unexpected results.

      Please Use the query as is, and only run it on the first day of the month. This will work for your alert, if you run it at the end of business on the last day of the month, or right at the start of business on the first day of the month.

      Regards,

      Johan