Skip to Content
0

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

May 15, 2017 at 10:34 PM

103

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Johan Hakkesteegt May 16, 2017 at 06:33 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Pascal Brien May 16, 2017 at 08:04 PM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0