Skip to Content
0

Query to check pending orders

Jul 19, 2017 at 04:33 AM

73

avatar image

Hello Experts

I have written a query to check for open orders - 4 days from curdate, but I am getting an incorrect syntax error

Can someone please help me, where am I going wrong?

SELECT T0.[DocNum] as 'S/Order', T0.[CardName], T0.[NumAtCard] as 'P/Order', (T0.[DocTotal] + T0.[DiscSum] -  T0.[VatSum]) as 'Total', T1.[ItemCode], T1.[Dscription], T1.[OpenSum] as 'Line Total', T1.[LineNum], T0.[DocDate], GetDate(), DATEPART(w, T0.DocDate) 
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry 
WHERE 
(T0.[DocDueDate] = '03.03.2033' OR T0.[DocDueDate] = '04.04.2044')
AND
(T1.[U_DelDate] = '03.03.2033' OR T1.[U_DelDate] = '04.04.2044')
AND
T0.[DocDate] <= getdate()-4 
AND
T0.[DocStatus] = 'O'
AND 
case when (([DATEPART])-4) <=1 THEN T0.[DocDate] <= getdate()-6
ELSE T0.[DocDate] <= getdate()-4 
ORDER BY 
T0.[DocNum]

Thanks

Regards

Rahul

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

3 Answers

Johan Hakkesteegt Jul 19, 2017 at 08:48 AM
0

Hi Rahul,

Your query looks good otherwise, but this part is a big mess:

case
       when (([DATEPART])-4) <=1 THEN T0.[DocDate] <= getdate() -6
       ELSE T0.[DocDate] <= getdate()-4 

To get transactions that happened within a certain recent time limit, please replace it with this method:

T0.[DocDate] >= DATEADD(DAY, -4, GETDATE()) /* this gives order that were created during the last 4 days */

or to get transactions that were added with a future DocDate within 4 days:

T0.[DocDate] BETWEEN DATEADD(DAY, 1, GETDATE()) AND DATEADD(DAY, 4, GETDATE()) /* this gives orders that were created with a future DocDate within 4 days from now */

Regards,

Johan

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Jul 19, 2017 at 02:12 PM
0

Hi,

Try this query based on row delivery date,

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[ShipDate] FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] WHERE T1.[ShipDate] >= datediff(day, 4, getdate())

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Rahul Fern Jul 21, 2017 at 03:26 AM
0

Hi Nagarajan

What I am looking for is in the results of the query

Orders that are in SAP => 4 days and haven't been scheduled (Orders with '03.03.2033'OR '04.04.2044')

So if an order has been entered in the system on the 21/7/2017 and the query is run on 24/7/17

The results should only show order that has been entered on or before the 18/7/17 as its four days minus the weekend =>6 days

and if the query is run on the 28/7/17 it should only show the order on or before the 24/7/17

Thanks

Rahul

Share
10 |10000 characters needed characters left characters exceeded