Query Correction

[Former Member]

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]

[former_member212181 · Accepted solution]

Hi Ramesh,

Please try 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 = '[%0]'

--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]

Thanks

Unnikrishnan

[Former Member · Accepted solution]

please find out my attachment you can also understand what kind of result i need

[KennedyT21 · Accepted solution]

Try this

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],

       (

           SELECT SUM(c.onhand)

           FROM   oitw c

           WHERE  c.itemcode = t3.itemcode

                  AND c.whscode = 'ExRm'

       ) AS ExRm,

       (

           SELECT SUM(d.onhand)

           FROM   oitw d

           WHERE  d.itemcode = t3.itemcode

                  AND d.whscode = 'ExWip'

       ) AS ExWip,

       (

           SELECT SUM(e.onhand)

           FROM   oitw e

           WHERE  e.itemcode = t3.itemcode

                  AND e.whscode = 'Rework'

       ) AS Rework,

       (

           SELECT SUM(f.onhand)

           FROM   oitw f

           WHERE  f.itemcode = t3.itemcode

                  AND f.whscode = 'ExSc'

       ) AS ExSc,

       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]

Regards

Kennedy