Skip to Content
0

Shipped vs Lead Time Query

Dec 13, 2016 at 02:03 AM

63

avatar image
Rahul Fern

Hello Experts

I have written a query to check for order that have been shipped against lead time

When I use only one card code to check with a date range, it works

but as soon as I added it to check a range of card code with a date range, it didn't work and I don't get any results

Where am I going wrong?

Please help

SELECT 
T0.[DocNum] as 'Sales Order No', 
T0.[CardName] as 'Sales Partner', 
T0.[NumAtCard] as 'Cust Ord No', 
T1.[U_Prod_Order_No] as 'Prodn Ord No', 
T1.[ItemCode] as 'Stock Code', 
T1.[Dscription] as 'Stock Description', 
T1.[Quantity], 
T1.[U_DelDate] as 'Lead Time', 
T1.[ShipDate] as 'Actual Delivery Date' 

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

WHERE 
T0.[CardCode] =[%0] AND 
T0.[CardCode] =[%1] AND 
T0.[DocStatus] = 'c' AND  
T0.[DocStatus]  !=  'L' AND   
T1.[ShipDate]  >=[%2] AND   
T1.[ShipDate]  <=[%3] 

ORDER BY T1.[ShipDate]

Thanks and Regards

Rahul

SAP Business One
Answers Answers and Comments
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Sort by:
Best Answer
avatar image
DIEGO LOTHER Answered Dec 13, 2016 at 03:25 AM
0

Hi Rahul,

For a range of Cardcode, you need use >= for the first Cardcode parameter and <= for the second Cardcode parameter, like you did with date range.

T0.[Cardcode] >= [%0] AND T0.[Cardcode] <= [%1]

Kind Regards,
Diego Lother
Show 3 Share
10 |10000 characters needed characters left characters exceeded
▼ Viewable by all users
Rahul Fern
Dec 13, 2016 at 06:21 AM

Hi Diego

Now I am embarrassed. That was a stupid mistake and Ishould have picked it up

Thanks for that

Rahul

0
Rahul Fern
Jan 15, 2017 at 10:55 PM

Hi Diego

Could you please help me with the below.

The query results in SAP display the result with the date in 16.01.2017 (dot) format, how do i get the query to display the result with the date as 16/01/2017 (Slashes)?

SELECT 
T0.[DocNum] as 'Sales Order No', 
T0.[CardName] as 'Sales Partner', 
T0.[NumAtCard] as 'Cust Ord No', 
T1.[U_Prod_Order_No] as 'Prodn Ord No', 
T1.[ItemCode] as 'Stock Code', 
T1.[Dscription] as 'Stock Description', 
T1.[Quantity], 

--(In the below two date fields SAP date format is 12.01.2017, how do I change it to display with slashes instead of dots 12/01/2017 in the query results)
T1.[U_DelDate] as 'Lead Time', 
T1.[ShipDate] as 'Actual Delivery Date' 

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

WHERE 
T0.[Cardcode] >= [%0] AND 
T0.[Cardcode] <= [%1] AND
T0.[DocStatus] = 'C' AND  
T0.[DocStatus]  != 'L' AND   
T1.[ShipDate]  >=[%2] AND   
T1.[ShipDate]  <=[%3] 

ORDER BY T1.[ShipDate]

Thanks

Rahul

0
DIEGO LOTHER
Rahul Fern
Jan 15, 2017 at 11:59 PM

Hi Rahul,

I answered in your original post.

Kind Regards,

Diego Lother

0
Comment