库龄查询

代码

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

 

转载于:https://www.cnblogs.com/dthom/articles/1803731.html