库龄查询

USE [DEV_WXT]
GO
/****** Object:  StoredProcedure [dbo].[usp_GetInventory_Age]    Script Date: 08/19/2010 17:19:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetInventory_Age](
@strCustCode nvarchar(20),
@strSiteCode nvarchar(20)
)


AS

select 10 as seq,'STK' as SUBINV,
0 as QTY3,convert(decimal(19,2),0) as AMT3,
0 as QTY6,convert(decimal(19,2),0) as AMT6,
0 as QTY12,convert(decimal(19,2),0) as AMT12,
0 as QTY24,convert(decimal(19,2),0) as AMT24,
0 as QTYOVER24,convert(decimal(19,2),0) as AMTOVER24 into #temp

insert into #temp
select 30 as seq,'DMG' as SUBINV,
0 as QTY3,convert(decimal(19,2),0) as AMT3,
0 as QTY6,convert(decimal(19,2),0) as AMT6,
0 as QTY12,convert(decimal(19,2),0) as AMT12,
0 as QTY24,convert(decimal(19,2),0) as AMT24,
0 as QTYOVER24,convert(decimal(19,2),0) as AMTOVER24


SELECT BRAND,ItemNo,SUBINV,COST,QTY,DATEDIFF(month,InboundDate,getdate()) as month1 into #temp1
 FROM SYS_Inventory WHERE ItemNo=@strCustCode and  SITECODE=@strSiteCode AND QTY>0



UPDATE #temp1 set month1=3 where month1>0 and month1<=3
UPDATE #temp1 set month1=6 where month1>3 and month1<=6
UPDATE #temp1 set month1=12 where month1>6 and month1<=12
UPDATE #temp1 set month1=24 where month1>12 and month1<=24
UPDATE #temp1 set month1=999 where month1>24

select subinv,month1,sum(qty) as qty,sum(cost*qty) as amt into #temp2 from #temp1 group by SUBINV,month1

update #temp set QTY3=b.qty,AMT3=b.amt from #temp a,#temp2 b
where a.subinv=b.subinv and month1=3

update #temp set QTY6=b.qty,AMT6=b.amt from #temp a,#temp2 b
where a.subinv=b.subinv and month1=6

update #temp set QTY12=b.qty,AMT12=b.amt from #temp a,#temp2 b
where a.subinv=b.subinv and month1=12

update #temp set QTY24=b.qty,AMT24=b.amt from #temp a,#temp2 b
where a.subinv=b.subinv and month1=24

update #temp set QTYOVER24=b.qty,AMTOVER24=b.amt from #temp a,#temp2 b
where a.subinv=b.subinv and month1=999

insert into #temp
select 40,'总计',sum(QTY3),sum(AMT3),sum(QTY6),sum(AMT6),
sum(QTY12),sum(AMT12),sum(QTY24),sum(AMT24),sum(QTYOVER24),sum(AMTOVER24) from #temp

select * from #temp order by seq

drop table #temp,#temp1,#temp2