CenterWise Stock Store Procedure…

ALTER PROCEDURE [dbo].[CenterWiseStock]

      -- Add the parameters for the stored procedure here

      @startdate datetime,

      @enddate datetime,

      @centerid int,

      @than nvarchar(1)='<'

AS

BEGIN

      select      itemname,

                  opening,

                  inwardqty,

                  InwardTransfer,

                  outwardqty,

                  OutwardTransfer,

(isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-

isnull(outwardqty,0)-isnull(OutwardTransfer,0) ) as closing

From (

select m.Itemname as itemname,

sum(isnull(openinward,0) + isnull(ITopen,0) - isnull(openoutward,0)- isnull(OTopen,0))

 as opening,

isnull((inwardqty),0)as inwardqty,

isnull((TInward),0)as InwardTransfer,

isnull((outwardqty),0)as outwardqty,isnull((TOutward),0)as OutwardTransfer

from itemmaster m

--------Opening---------------------------------------

left outer join(select id.itemid, isnull(sum(id.qty),0) as openinward  from inwarddetail id

left outer join inward i on i.inwardid = id.inwardid

where  inwarddate < @startdate And i.CenterId =@centerid

 group by itemid) k

on m.itemid = k.itemid left outer join

( select od.itemid, isnull(sum(od.qty),0) as openoutward  from outwarddetail od

left outer join outward i on i.outwardid = od.outwardid

where  outwarddate < @startdate And i.CenterId =@centerid

 group by itemid

) oo

on  m.itemid = oo.itemid left outer join

(

select itemid, sum(qty) as OTopen  from Transfer T

left outer join TransferDetail TD on TD.TransterId = T.TransferId

where  Transferdate < @startdate And T.SourceCenterId =@centerid

 group by itemid

) ITO

on  m.itemid = ITO.itemid

left outer join

(

select itemid, sum(qty) as ITopen  from Transfer T

left outer join TransferDetail TD on TD.TransterId = T.TransferId

where  Transferdate < @startdate And T.DestinationCenterId =@centerid

 group by itemid

) OTo

on  m.itemid = OTo.itemid

left outer join

-------------------inwardqty----------------------------

(

select itemid, sum(qty) as inwardqty  from inwarddetail id

left outer join inward i on i.inwardid = id.inwardid

where inwarddate >= @startdate  and inwarddate <= @enddate And i.CenterId =@centerid

group by itemid

) i

 on m.itemid = i.itemid left outer join

--------------------------outwardqty----------------------

( select itemid, sum(qty) as outwardqty  from outwarddetail od

left outer join outward i on i.outwardid = od.outwardid

where outwarddate >= @startdate and outwarddate <= @enddate And i.CenterId =@centerid

 group by itemid

) o

on  m.itemid = o.itemid left outer join

--------------------------Transfer outward------------------

(

select itemid, sum(qty) as TOutward  from Transfer T

left outer join TransferDetail TD on TD.TransterId = T.TransferId

where Transferdate >= @startdate and Transferdate <= @enddate And T.SourceCenterId =@centerid

 group by itemid

) IT

on  m.itemid = IT.itemid left outer join

------------------------Transfer Inward-------------------------

(

select itemid, sum(qty) as TInward  from Transfer T

left outer join TransferDetail TD on TD.TransterId = T.TransferId

where Transferdate >= @startdate and Transferdate <= @enddate And T.DestinationCenterId =@centerid

 group by itemid

) OT

on  m.itemid = OT.itemid

group by m.Itemname,inwardqty,outwardqty,TOutward,TInward )

Temp

where

(case when @than ='<' Then (isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-

isnull(outwardqty,0)-isnull(OutwardTransfer,0) )End) <= 0 

or

(case when @than ='>' Then (isnull(opening,0) + isnull(inwardqty,0) + isnull(InwardTransfer,0)-

isnull(outwardqty,0)-isnull(OutwardTransfer,0))End) > 0

END