SQL Queries

[Former Member]

Hello Guys ,

May i ask a little bit of your help.

below queries are the process of our Re order level report.

Where our logistics team is calculating the order to be needed.

for example :

Item Available Re_order To be ordered

A 100 150 50

B 250 100 0 (will not show anything)

C 1500 2750 1250

if available (100) is lesser and re order(150) is greater then to be ordered result value should be 50.

and if Available (150) is greater and re order (100) is lesser the to be ordered result value will be Zero.

Below is my SQL code:

SELECT T0.[ItemCode], T0.[ItemName], T1.[OnHand] as 'In Stock', T1.[IsCommited] as 'Committed', T1.[OnOrder] as 'Ordered', T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] as 'Available', T1.[MinOrder] as 'Re-Order', T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] - T1.[MinOrder] as 'To be ordered', T0.[LstEvlPric], T0.[LastPurPrc], T0.[LastPurCur] as 'Currency', T2.[ItmsGrpNam] as 'Supplier' FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] UNION SELECT T0.[ItemCode], T0.[ItemName], T1.[OnHand] as 'In Stock', T1.[IsCommited] as 'Committed', T1.[OnOrder] as 'Ordered', T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] as 'Available', T1.[MinOrder] as 'Re-Order', T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] - T1.[MinOrder] as 'To be ordered', T0.[LstEvlPric], T0.[LastPurPrc], T0.[LastPurCur] as 'Currency', T2.[ItmsGrpNam] as 'Supplier' FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] INNER JOIN OITB T2 ON T0.[ItmsGrpCod] = T2.[ItmsGrpCod] WHERE T1.[MinOrder] >0 and T1.[OnHand] + T1.[OnOrder] - T1.[IsCommited] - T1.[MinOrder] <0

[anindya_bose]

You can use a Case statement like below .

SELECT 
ITEM, 
IN_STOCK, 
MIN_STOCK , 
(CASE WHEN  (MIN_STOCK - IN_STOCK) > 0 THEN (MIN_STOCK - IN_STOCK) ELSE 0 END ) AS RE_ORDER
FROM <YOUR_TABLE>

[Former Member]

thank you for the query.

Sir, can you help me provide if else condition for this. since case is coming correct but it requires also that

if the condition is 'AVAILABLE' is lesser than re-order then (Re-order minus Available ) as to be Ordered.

but if condition is Availble greater than Re-Order then ( result will be 0) as to be ordered.

Thank you for your time and effort.

appreciate your help

[anindya_bose]

(MIN_STOCK - IN_STOCK)>0 THEN (MIN_STOCK - IN_STOCK)

Above statement is basically doing the same .

If my understanding is not correct, can you put a table and explain what exactly you want the query to return .

Cheers

Anindya

[Former Member]

Dear Anindya Bose this is our query

[Former Member]

this is the sample

[Former Member]

actually our Query is showing proper result but the problem is it has something to do with the SQL since it is not showing all the items in which half of our items is not listed

[anindya_bose]

If you are missing some rows, then check INNER JOIN and WHERE Clause . I am not aware of the complete requirement here , but you can change INNER to LEFT OUTER JOIN and/or drop the WHERE Clause to understand where you are missing records .

To filter rows , you can use "WHERE [To Be Ordered] > 0 " clause

Regards

Anindya