Skip to Content
0
Former Member
Aug 17, 2015 at 11:46 AM

Query Correction

28 Views

Hi friends kindly correct below query

Below Query i need ware house wise stock also where i consider particular warehouses (ExRm','ExWip','Rework','ExSc')

Already i have taken instock(total onhand stock) details for these four warehouse (ExRm','ExWip','Rework','ExSc') and i also need separate warehouse stock also . how i modify below query...

DECLARE @RunDate as Varchar(100)

SET @RunDate=(Select GETDATE())

SELECT T0.[ItemCode], T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,T1.[CardName],

SUM( T3.[OnHand]) as 'Instock', T0.[MinLevel], T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti],

(CASE WHEN (T0.[U_MinInvDays] > 0 AND T0.[MinLevel] > 0) THEN ((Sum(T3.[OnHand]))/(T0.[MinLevel]/T0.[U_MinInvDays])) ELSE 0 END)[Coverage]

FROM OITM T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

inner join OCYC T2 ON T0.[OrdrIntrvl]=T2.Code

inner join OITW T3 ON T3.ItemCode=t0.ItemCode

WHERE T0.[ItmsGrpCod] ='109' and T0.ValidFor='Y' and t0.U_pgmname='Gear' and T0.ItemCode='1234'

and T3.WhsCode in ('ExRm','ExWip','Rework','ExSc')

group by T0.[ItemCode], T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,

T1.[CardName],T0.[MinLevel],T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti], T0.[MinOrdrQty], T0.[U_MinInvDays]