K3庫齡分析帶金額

1、SET NOCOUNT ON CREATE TABLE #Happen( FItemID int Null, FStockID int Null, FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FTemp bit ) CREATE TABLE #Happen1( FItemID int Null, FStockID int Nu

2、ll, FQty decimal(28,10) Null, FCUUnitQty decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FTemp bit ) INSERT INTO #Happen1 Select t1.FItemID,t2.FItemID As FStockID,0,0,(case when t5.FDate>=convert(datetime,convert(varchar,DAT

3、EADD(dd,-89,'2011-03-31'),101) AND t5.FDate<=convert(datetime,convert(varchar,DATEADD(dd,-0,'2011-03-31'),101) then t6.FQty else 0 END) As FQty1,(case when t5.FDate>=convert(datetime,convert(varchar,DATEADD(dd,-89,'2011-03-31'),101) AND t5.FDate<=convert(datetime,con

4、vert(varchar,DATEADD(dd,-0,'2011-03-31'),101) then t6.FQty else 0 END)/t7.FCoefficient As FCUUnitQty1,(case when t5.FDate>=convert(datetime,convert(varchar,DATEADD(dd,-90,'2011-03-31'),101) AND t5.FDate<=convert(datetime,convert(varchar,DATEADD(dd,-90,'2011-03-31'),101)

5、 then t6.FQty else 0 END) As FQty2,(case when t5.FDate>=convert(datetime,convert(varchar,DATEADD(dd,-90,'2011-03-31'),101) AND t5.FDate<=convert(datetime,convert(varchar,DATEADD(dd,-90,'2011-03-31'),101) then t6.FQty else 0 END)/t7.FCoefficient As FCUUnitQty2,1 From t_ICItem t1

6、,t_Stock t2,ICStockBill t5,ICStockBillEntry t6,t_MeasureUnit t7 Where t5.FInterID=t6.FInterID And t5.FStatus>0 AND t5.FTrantype In (1,2,5,10,40) AND t1.FItemID = t6.FItemID And t2.FItemID = t5.FDCStockID AND t1.FCUUnitID=t7.FItemID INSERT INTO #Happen1 SELECT t1.FItemID,t2.FItemID As FStockID,(t3

7、.FQTY) AS FQTY,(t3.FQTY)/t7.FCoefficient as FCUUnitQty,0,0,0,0,1 From t_ICItem t1,t_Stock t2,ICINVENTORY t3 ,t_MeasureUnit t7where t1.FItemID = t3.FItemID And t2.FItemID = t3.FStockID AND t1.FCUUnitID=t7.FItemID INSERT INTO #HAPPENSELECT FITEMID,FSTOCKID,SUM(FQTY)AS FQTY,SUM(FCUUnitQTY)AS FCUUnitQTY

8、,sum(fqty1) as FQty1 ,sum(fCUUnitqty1) as FCUUnitQty1 ,sum(fqty2) as FQty2 ,sum(fCUUnitqty2) as FCUUnitQty2 ,1FROM #HAPPEN1GROUP BY FITEMID,FSTOCKIDhaving sum(FQty)>0 Update #Happen Set FQty1= FQty,FQty2=0 Where FQty-FQty1<0 Update #Happen Set FQty2= FQty-FQty1Update #Happen Set FCUUnitQty1= F

9、CUUnitQty,FCUUnitQty2=0 Where FCUUnitQty-FCUUnitQty1<0 Update #Happen Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1SET NOCOUNT ONCREATE TABLE #ItemLevel( FNumber4 Varchar(355), FName4 Varchar(355), FItemID int, FNumber Varchar(355) INSERT INTO #ItemLevel SELECT CASE WHEN CHARINDEX('.',FFullNumb

10、er)-1= -1 or FLevel<5 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1) END, '', FItemID,FNumber FROM t_Item WHERE FItemClassID=4 AND FDetail=1 AND FItemID In (Select Distinct FItemID from #Happen ) UPDATE t1 SET t1.FName4=t2.FName FROM #ItemLevel t1,t_Item t2 WHER

11、E t1.FNumber4=t2.FNumber AND t2.FItemClassID=4 AND t2.FDetail=0 CREATE TABLE #DATA(FName4 Varchar(355) Null, FNumber Varchar(355) null, FShortNumber Varchar(355) null, FName Varchar(355) null, FModel Varchar(355) null, FUnitName Varchar(355) null, FCUUnitName Varchar(355) null, FQtyDecimal smallint

12、null, FPriceDecimal smallint null, FQty Decimal(28,10) Null, FCUUnitQty Decimal(28,10) Null, FQty1 Decimal(28,10), FCUUnitQty1 Decimal(28,10), FQty2 Decimal(28,10), FCUUnitQty2 Decimal(28,10), FSumSort smallint not null Default(0), FID int IDENTITY)INSERT INTO #DATA SELECT tt1.FName4,t1.FNumber,'

13、;','','','','',6,4,Sum(FQty),Sum(FCUUnitQty), SUM(FQty1), SUM(FCUUnitQty1), SUM(FQty2), SUM(FCUUnitQty2),CASE WHEN Grouping(tt1.FName4)=1 THEN 109 WHEN Grouping(t1.FNumber)=1 THEN 110 ELSE 0 END FROM #Happen v2,t_ICItem t1,t_Stock t2 ,#ItemLevel tt1 Where v2.FStoc

14、kID=t2.FItemID And v2.FItemID=t1.FItemID AND t1.FItemID=tt1.FItemID Group By tt1.FName4,t1.FNumber WITH ROLLUP Having Sum(FQty)>0 UPDATE t1 SET t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FUnitName=t3.FName,t1.FCUUnitName=t4.FName ,t1.FQtyDecimal=t2.FQtyDecimal,t1.FP

15、riceDecimal=t2.FPriceDecimal FROM #DATA t1,t_ICItem t2,t_MeasureUnit t3,t_MeasureUnit t4 WHERE t1.FNumber=t2.FNumber AND t2.FUnitGroupID=t3.FUnitGroupID AND t2.FCUUnitID=t4.FMeasureUnitID AND t3.FStandard=1Update #Data Set FName4=FName4+'(小計)' WHERE FSumSort=110Update #Data Set FName4='合

16、計' WHERE FSumSort=109Update #Data Set FSumSort=101 WHERE FSumSort=109-CREATE TABLE #Temp( FItemID int, FSupplyID int, FQty decimal(28,10), FAmount decimal(28,10), FPrice decimal(28,10), FInterID int, FDate datetime)CREATE TABLE #T123( FItemID int, FSupplyID int, FQty decimal(28,10), FAmount deci

17、mal(28,10), FMaxPrice decimal(28,10), FAvgPrice decimal(28,10), FMinPrice decimal(28,10), FNewPrice decimal(28, 10), FDate datetime)Insert into #Temp( FInterID,FDate,FItemID,FSupplyID,FQty,FAmount,FPrice)Select v1.FInterID,v1.FDate,v2.FItemID,v1.FSupplyID,v2.FQty,case when t12.FOperator='*'

18、then v2.FAmount*t12.FExchangeRate else v2.FAmount/t12.FExchangeRate end,case when t12.FOperator='*' then v2.FPrice*t12.FExchangeRate else v2.FPrice/t12.FExchangeRate end From ICPurchase v1,ICPurchaseEntry v2,t_Item t4,t_Supplier t8,t_Currency t12 Where v1.FInterID=v2.FInterID and v2.FItemID=

19、t4.FItemID and v1.FSupplyID=t8.FItemID and v1.FCurrencyID=t12.FCurrencyID And v1.FCancelLation=0 Insert into #T123( FItemID,FSupplyID,FQty,FAmount,FMaxPrice,FAvgPrice,FMinPrice,FNewPrice,fdate) Select FItemID,FSupplyID,sum(FQty), sum(FAmount),0,0,0,0,'2000-01-01' from #Temp Group by FItemID,

20、FSupplyID update t1 set t1.FMaxPrice=(select max(FPrice) from #Temp v1 Where v1.FItemID=t1.FItemID And v1.FSupplyID=t1.FSupplyID) from #T123 t1 update t1 set t1.FMinPrice=(select min(FPrice) from #Temp v1 Where v1.FItemID=t1.FItemID And v1.FSupplyID=t1.FSupplyID) from #T123 t1 update t1 set t1.FNewP

21、rice=(select top 1 FPrice from #Temp v1 Where v1.FItemID=t1.FItemID And v1.FSupplyID=t1.FSupplyID order by v1.FDate desc,v1.FInterID desc) from #T123 t1 update t1 set t1.fdate=(select top 1 fdate from #Temp v1 Where v1.FItemID=t1.FItemID And v1.FSupplyID=t1.FSupplyID order by v1.FDate desc,v1.FInter

22、ID desc) from #T123 t1 select t1.FShortNumber as FNumber,t1.FNumber as FMatFullNumber, t1.FName, t1.FModel,t3.FName as FUnitName, t8.FShortNumber as FSupplyNumber,t8.FNumber as FSupplyFullNumber, t8.FName As FSupplyName, t2.FQty ,t2.FAmount, t2.FMaxPrice,(case t2.FQty when 0 then 0 Else t2.FAmount/t2.FQty End) as FAvgPrice