Skip to Content

select a.ItemCode,a.[Name],SUM(a.[OB-Qty]) [OB-Qty],SUM(a.[OB-Value]) [OB-Value],

sum(a.[Issue]) [Issue] ,sum(a.[Receipt]) [Receipt],

SUM(a.[Cls-Qty]) [Cls-Qty], SUM([ClsValue]) [ClsValue]  from(

select t1.ItemCode,max(t1.dscription) [Name],

(sum(isnull(t1.inqty,0)) – sum(isnull(t1.outqty,0)) ) [OB-Qty],

sum(isnull(t1.transvalue,0)) [OB-Value],0 [Issue], 0 [Receipt],

0 [Cls-Qty],0 [ClsValue]

from OINM t1 where

t1.docdate < ‘[%0]’ and t1.[Warehouse] = ‘[%3]’ group by t1.ItemCode

union all

select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],

(sum(isnull(t1.outqty,0))) [Issue],

(sum(isnull(t1.inqty,0))) [Receipt],

0 [Cls-Qty],0 [ClsValue]

from OINM t1

where t1.docdate >= ‘[%0]’ and  t1.DocDate <= ‘[%1]’ and t1.[Warehouse] = ‘[%3]’

group by t1.ItemCode

union all

select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],

0 [Issue],0 [Receipt],

(sum(isnull(t1.inqty,0)) – sum(isnull(t1.outqty,0)) ) [Cls-Qty],

sum(isnull(t1.transvalue,0))  [ClsValue]

from OINM t1

where t1.DocDate <= ‘[%1]’ and t1.[Warehouse] = ‘[%3]’

group by t1.ItemCode)a

group by a.ItemCode,a.[Name]

—————————————————————————————————————————————————————–

Declare @ToDate Datetime

set @FromDate =

    (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]’)

set @ToDate =

    (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]’)

select * from

(

    SELECT T0.itemcode,

    min(T0.Dscription) as ‘Item Description’,

    min(B1.ItmsGrpNam) as ‘Item Group’, W1.Whscode, C1.Location,

    (isnull((

        Select sum(isnull(inqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode

        and O1.Warehouse=W1.Whscode

        and O1.docdate<@FromDate ),0)-

    isnull((

        Select sum(isnull(outqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode

        and O1.Warehouse=W1.Whscode

        and O1.docdate<@FromDate),0)

    ) as [Opening Stock],

    isnull((

        Select sum(isnull(inqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode

        and O1.Warehouse=W1.Whscode

        and O1.docdate>=@FromDate

        and O1.docdate<=@ToDate and O1.inqty>0

        and O1.transtype in (20,18)),0

    ) as [Purchase Quantity],

    isnull((

        Select sum(isnull(outqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

        and O1.docdate>=@FromDate and O1.docdate<=@ToDate

        and O1.outqty>0 and O1.transtype in (21,19)),0

    ) as [Purchase Return Quantity],

   isnull((

        Select sum(isnull(outqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

        and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0

        and O1.transtype in (13,15)),0

    ) as [sale Quatity],

    (isnull

        ((

        Select sum(isnull(inqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

        and O1.docdate<=@ToDate),0

        )-

        isnull((

            Select sum(isnull(outqty,0))

            from OINM O1

            where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

            and O1.docdate<=@ToDate),0)

    ) as [Closing Stock]

    FROM OINM T0

    INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

    INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode

    INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod

    INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode

    INNER JOIN OLCT C1 ON W1.Location=C1.Code

    Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location

) a

where (a.[Opening Stock]

        +a.[Purchase Quantity]

        + a.[Purchase Return Quantity]

        +a.[sale Quatity]+a.[Closing Stock]

       ) !=0

——————————————————————————————————————————————————————————————————————

declare @a char(60)

set @a  = (select distinct t20.docdate from oinm t20 where t20.docdate = ‘[%2]’)

select  distinct c.code, c.name

,SUM(c.bal) [Bal],sum(c.Val) [Val],

(case when avg(c.[FullStock]) > 0 then avg(c.[Pric]) / avg(c.[FullStock]) end ) [AvgPrice],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then SUM(c.bal)  else SUM(c.[0-30 Days]) end) [0-30 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then (SUM(c.bal) – sum(c.[0-30 Days]))

else sum(c.[30-60 Days]) end)end)   [30-60 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then

(SUM(c.bal) – (sum(c.[0-30 Days]) + sum(c.[30-60 Days]))) else

sum(c.[60-90 Days])end)end) end)   [60-90 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then

(SUM(c.bal) – (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]))) else sum(c.[90-180 Days])

end)end) end) end)  [90-180 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) +

sum(c.[90-180 Days]) +  sum(c.[180-360 Days]) ) >= SUM(c.bal) then

(SUM(c.bal) – (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])))

else sum(c.[180-360 Days])end)end)end)end)end) [180-360 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else sum(c.[Above 360 Days])

end)end)end) [Above 360 Days]

from (

select b.code, b.name,

b.Bal, b.Val,b.[Pric],b.[FullStock],

isnull(case when b.days <=30 then b.Bal end,0)[0-30 Days] ,

isnull(case when b.days <= 30 then b.val end,0)[0-30 Days-Value] ,

isnull(case when b.days between 31 and 60 then b.Bal end,0) [30-60 Days],

isnull(case when b.days between 61 and 90 then b.Bal end,0) [60-90 Days],

isnull(case when b.days between 91 and 180 then b.Bal end,0) [90-180 Days],

isnull(case when b.days between 181 and 360 then b.Bal end,0) [180-360 Days],

isnull(case when b.days >= 361 then b.Bal end,0) [Above 360 Days]

from

(

select a.code,a.name,

a.bal,a.val,datediff(dd,dt,@a)’days’,a.[Receipt],a.[Pric],a.[FullStock]

from

(

select max(t0.itemcode)’Code’,max(t0.Dscription)’Name’,

(select sum(isnull(t11.transvalue,0))  from oinm t11 where (t11.itemcode = (t0.itemcode))

and (t11.docdate <= ‘[%2]’ )) [Pric],

(select (sum(isnull(t11.inqty,0)) – sum(isnull(t11.outqty,0)) ) from oinm t11 where (t11.itemcode = (t0.itemcode))

and (t11.docdate <= ‘[%2]’ )) [FullStock],

–(t0.Warehouse)’Wh’,

(sum(isnull(t0.inqty,0))) [Receipt],

(sum(isnull(t0.inqty,0)) – sum(isnull(t0.outqty,0)) )’Bal’,sum(isnull(t0.transvalue,0))’Val’,(t0.docdate)’dt’

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode inner join oitb ob on ob.itmsgrpcod = t1.itmsgrpcod

where  (ob.itmsgrpnam = ‘[%1]’)

and (t0.docdate <= ‘[%2]’)

group by t0.itemcode,t0.docdate

)a

)b

)c where c.bal>0

group by c.code, c.name

Hope Helpful

Regards

Kennedy

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Rajesh Rana

    Hi Kennedy,

    Stock Aging MIS is throwing error as –

    Msg 241, Level 16, State 1, Line 2

    Conversion failed when converting date and/or time from character string.

    Hence please resolve the issue.

    Rgds,

    Rajesh Rana

    (0) 
      1. Rajesh Rana

        thanks the error resolved but it is showing only recd qty while we are looking for balance qty as on particular date. also find modified query with comment as –RAJESH as –

        Declare @a DateTime

        Set @a  = (select distinct t20.docdate from oinm t20 where t20.docdate = ‘20140122’) — RAJESH

        Select  distinct c.code, c.name, SUM(c.bal) [Bal],sum(c.Val) [Val],

        (case when avg(c.[FullStock]) > 0 then avg(c.[Pric]) / avg(c.[FullStock]) end ) [AvgPrice],

        (case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then SUM(c.bal)  else SUM(c.[0-30 Days]) end) [0-30 Days],

        (case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then (SUM(c.bal) – sum(c.[0-30 Days]))

        else sum(c.[30-60 Days]) end)end)   [30-60 Days],

        (case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then

        (SUM(c.bal) – (sum(c.[0-30 Days]) + sum(c.[30-60 Days]))) else

        sum(c.[60-90 Days])end)end) end)   [60-90 Days],

        (case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then

        (SUM(c.bal) – (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]))) else sum(c.[90-180 Days])

        end)end) end) end)  [90-180 Days],

        (case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) +

        sum(c.[90-180 Days]) +  sum(c.[180-360 Days]) ) >= SUM(c.bal) then

        (SUM(c.bal) – (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])))

        else sum(c.[180-360 Days])end)end)end)end)end) [180-360 Days],

        (case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

        (case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else sum(c.[Above 360 Days])

        end)end)end) [Above 360 Days]

        from (

        select b.code , b.name,

        b.Bal, b.Val,b.[Pric],b.[FullStock],

        isnull(case when b.days <=30 then b.Bal end,0)[0-30 Days] ,

        isnull(case when b.days <= 30 then b.val end,0)[0-30 Days-Value] ,

        isnull(case when b.days between 31 and 60 then b.Bal end,0) [30-60 Days],

        isnull(case when b.days between 61 and 90 then b.Bal end,0) [60-90 Days],

        isnull(case when b.days between 91 and 180 then b.Bal end,0) [90-180 Days],

        isnull(case when b.days between 181 and 360 then b.Bal end,0) [180-360 Days],

        isnull(case when b.days >= 361 then b.Bal end,0) [Above 360 Days]

        from

        (select a.code,a.name,

        a.bal,a.val,datediff(dd,dt,’20140122′)’days’,a.[Receipt],a.[Pric],a.[FullStock]

        from

        (

        select max(t0.itemcode)’Code’,max(t0.Dscription)’Name’,

        (select sum(isnull(t11.transvalue,0))  from oinm t11 where (t11.itemcode = (t0.itemcode))

        and (t11.docdate <= ‘20140122’ )

        And t11.Warehouse = ‘RM-WHDD’ — RAJESH

        ) [Pric],

        (select (sum(isnull(t11.inqty,0)) – sum(isnull(t11.outqty,0)) ) from oinm t11 where (t11.itemcode = (t0.itemcode))

        and (t11.docdate <= ‘20140122’ )

        And t11.Warehouse = ‘RM-WHDD’ — RAJESH

        ) [FullStock],

        —–

        (T0.Warehouse)’Wh’, — RAJESH

        (sum(isnull(t0.inqty,0))) [Receipt],

        (sum(isnull(t0.inqty,0)) – sum(isnull(t0.outqty,0)) )’Bal’,sum(isnull(t0.transvalue,0))’Val’,(t0.docdate)’dt’

        from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode inner join oitb ob on ob.itmsgrpcod = t1.itmsgrpcod

        where  (ob.itmsgrpnam = ‘SA ITEMS’)

        and (t0.docdate <= ‘20140122’)

        And t0.Warehouse = ‘RM-WHDD’  — RAJESH

        group by t0.itemcode,t0.docdate,t0.Warehouse

        )a

        )b

        )c where c.bal>0 And C.Code = ‘SA00000226’

        group by c.code, c.name

        Result is as

        code    name    Bal    Val    AvgPrice    0-30 Days    30-60 Days    60-90 Days    90-180 Days    180-360 Days    Above 360 Days

        SA00000226    MOUNTED PCB MC FOR EDFA-15-23-04    24.000000    2774.690000    116.359166    10.000000    0.000000    0.000000    14.000000    0.000000    0.000000

        (0) 
  2. Bhupendra Tailor

    Hi

    The below script works for me, but I also need to show Item Group from  OITM table. please can you help. I’m new to sql.

    Thanks.

    select b.code, b.name, b.Wh, b.Bal, b.Val,

    isnull(case when b.days <30 then b.bal end,0)’0-30 Days’ ,

    isnull(case when b.days between 30 and 60 then b.bal end,0) ’30-60 Days’,

    isnull(case when b.days between 60 and 90 then b.bal end,0) ’60-90 Days’,

    isnull(case when b.days between 90 and 120 then b.bal end,0) ’90-120 Days’,

    isnull(case when b.days between 120 and 150 then b.bal end,0) ‘120-150 Days’,

    isnull(case when b.days between 150 and 180 then b.bal end,0) ‘150-180 Days’,

    isnull(case when b.days >180 then b.bal end,0) ‘Above 180 Days’

    from (

    select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())’days’

    from (

    select max(t0.itemcode)’Code’,max(t0.Dscription)’Name’,

    max(t0.Warehouse)’Wh’,

    sum(t0.inqty-t0.outqty)’Bal’,sum(t0.transvalue)’Val’,max(t0.docdate)’dt’

    from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

    –where t0.warehouse='[%1]’

    where t0.warehouse IN(‘SK’,’EU’)

    group by t0.warehouse,t0.itemcode

    )a

    )b order by b.wh,code

    (0) 

Leave a Reply