Open documents exclude all orders with date 01-01 (all years)

[jeroenw]

Hi,

I am working on a query of the open customer orders.

I want this query not to show the orders with date DAY 01 MONTH 01. So all orders from January 1 (every year) should not be visible.

I have tried the options below. These work, but do not show all orders that start with DAY 01. So all orders from February 1, March 1, and April 1 are not visible. I only want all the orders from 1 january not to be visible.

SELECT 
T0.[DocNum],
T1.[Quantity],
T1.[ItemCode],
T1.[Dscription],
T0.[DocDate],
T0.[CardCode], 
T0.[CardName]

FROM 
ORDR T0  
INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 

WHERE 
T0.[CardCode] =[%0] AND
T0.[DocDate] <=[%1] AND
T1.[LineStatus]= 'O' AND
month(T0.[DocDate]) <> '1' and day(T0.[DocDate]) <> '1'

ORDER BY
T1.[ItemCode]

SELECT 
T0.[DocNum],
T1.[Quantity],
T1.[ItemCode],
T1.[Dscription],
T0.[DocDate],
T0.[CardCode], 
T0.[CardName]

FROM 
ORDR T0  
INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 

WHERE 
T0.[CardCode] =[%0] AND
T0.[DocDate] <=[%1] AND
T1.[LineStatus]= 'O' AND
Datepart(DD,T0.DocDate) <> '01' And DatePart(MM,T0.DocDate) <> '01'

ORDER BY
T1.[ItemCode]

[RaymondGiuseppi]

Try something such as 

WHERE 
T0.[CardCode] =[%0] AND
T0.[DocDate] <=[%1] AND
T1.[LineStatus]= 'O' AND
( month(T0.[DocDate]) <> '1' OR day(T0.[DocDate]) <> '1')

[jeroenw]

Thanks, I can't believe the answer was so simple. I thought I need to use AND to combine DAY and MONTH. That sounds more logical.

[RaymondGiuseppi]

The contrary of 'A and B' is 'not A or not B' 😎

[jeroenw]

I still don't get it...

[RaymondGiuseppi]

Logic and Mathematical Statements 

[Maya_Shiff]

hi @jeroenw , 

If you have version 10, you may filter the list of "Open Sales Orders" by "Posting Date" field, using the following syntax:

here i listed 3 years only, but you can apply as many as you have in your DB. 

In general - the analytics and data filtering capabilities of the web client are highly sophisticated and flexible - and at the same time user-friendly

More information is available here:

 https://learning.sap.com/learning-journeys/exploring-sap-business-one-web-client 

regards,

Maya

[LoHa]

Hi Jeroen,

I would do it in that way

AND
DAY(T0.DocDate) + Month(T0.DocDate) <> 2

otherwise you wouldn't get every first of a month or january

regards Lothar