Hello Experts,

I have wrote below Query for pending orders.

SELECT

(case when T1.[U_AMTUOM]='mtr' then T1.[U_TotalLength]

when T1.[U_AMTUOM]='nos' then T1.[U_Nos]

when T1.[U_AMTUOM]='kgs' then T1.[U_TotalWeight]

when T1.[U_AMTUOM]='Feet' then T1.[U_TotalLengthFt]

else T1.[Quantity] end) as 'Order Quantity',



(CASE When (T1.[Quantity]- T1.[OpenQty]) = 0 then (T1.[Quantity]- T1.[OpenQty]) else T4.[Quantity] END) as 'Dispatched QTY',

((case when T1.[U_AMTUOM]='mtr' then T1.[U_TotalLength]

when T1.[U_AMTUOM]='nos' then T1.[U_Nos]

when T1.[U_AMTUOM]='kgs' then T1.[U_TotalWeight]

when T1.[U_AMTUOM]='Feet' then T1.[U_TotalLengthFt] else T1.[Quantity] end) - (CASE When (T1.[Quantity]- T1.[OpenQty]) = 0 then (T1.[Quantity]- T1.[OpenQty])

else T4.[Quantity] END) ) as 'Pending',


(CASE WHEN T1.[U_AMTUOM]='kgs' THEN (T1.[OpenQty] * T1.[U_Kg_Mtr] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='mtr' THEN (T1.[OpenQty] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='nos' THEN T1.[OpenQty] WHEN T1.[U_AMTUOM]='Feet' THEN (T1.[OpenQty] * 20) ELSE T1.[OpenQty] END) as 'Pending Quantity ',


(T1.[Price] * (CASE WHEN T1.[U_AMTUOM]='kgs' THEN (T1.[OpenQty] * T1.[U_Kg_Mtr] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='mtr' THEN (T1.[OpenQty] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='nos' THEN T1.[OpenQty] WHEN T1.[U_AMTUOM]='Feet' THEN (T1.[OpenQty] * 20) ELSE T1.[OpenQty] END) ) as 'Amount equivalent as per Pending Qty',

T1.[U_TOTALWEIGHT] AS 'ORDERED QTY IN KGS' ,

(CASE When (T1.[Quantity]- T1.[OpenQty]) = 0 then (T1.[Quantity]- T1.[OpenQty]) else T4.[U_TotalWeight] END) as 'Dispatched QTY In KGs',

( (T1.[U_TOTALWEIGHT]) - (CASE When (T1.[Quantity]- T1.[OpenQty]) = 0 then (T1.[Quantity]- T1.[OpenQty]) else T4.[U_TotalWeight] END) ) AS 'PENDING QTY IN KGS',


FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod] left join DLN1 T4 ON T1.DocEntry = T4.BaseEntry


WHERE (CASE WHEN T1.[U_AMTUOM]='kgs' THEN (T1.[OpenQty] * T1.[U_Kg_Mtr] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='mtr' THEN (T1.[OpenQty] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='nos' THEN T1.[OpenQty] WHEN T1.[U_AMTUOM]='Feet' THEN (T1.[OpenQty] * 20 ) ELSE T1.[OpenQty] END) > 0 AND T3.[ItmsGrpNam] <> 'COIL'

When i am running it, it shows exact report for those pending orders which has 0 dispatch till date, but for orders which has partial dispatch it shows multiple lines which is unnecessary for the report.

Pl help

Regards

Ambesh Negi