Closing Stock Store Procedure

Friends Today i share With You Closing Stock Report Example


ALTER PROCEDURE [dbo].[ClosingStock]

      -- Add the parameters for the stored procedure here

      @startdate datetime,

      @enddate datetime,

      @than nvarchar(1)='<'

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      select itemname ,opening,inwardqty,outwardqty ,

(isnull(opening,0) + isnull(inwardqty,0) - isnull(outwardqty,0)) as closing

from (

select m.Itemname as itemname,sum(isnull(openinward,0)-isnull(openoutward,0)) as opening,

isnull((inwardqty),0)as inwardqty,isnull((outwardqty),0)as outwardqty

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

 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

 group by itemid

) oo

on  m.itemid = oo.itemid

-----------Inward qty---------------------------------------

left outer join(

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

 group by itemid

) i

 on m.itemid = i.itemid

------------------Outward qty--------------------------------

left outer join( 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 

 group by itemid

) o

on  m.itemid = o.itemid

group by m.Itemname,inwardqty,outwardqty ) t

where

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

or

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

End