关闭

K3账龄分析表SQL

730人阅读 评论(0) 收藏 举报

Set NoCount On
 SET ANSI_WARNINGS OFF
 Create Table #Happen2(
        FItemID int Null,
        FStockID int Null,
        FBatchNo NVARCHAR(200),
        FQty decimal(28,10) Null,
        FCUUnitQty decimal(28,10) Null,
        FQty1 Decimal(28,10),
        FCUUnitQty1 Decimal(28,10),
        FAmount1 Decimal(28,10),
        FQty2 Decimal(28,10),
        FCUUnitQty2 Decimal(28,10),
        FAmount2 Decimal(28,10),
        FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))
 Create Table #Happen(
        FItemID int Null,
        FStockID int Null,
        FBatchNo NVARCHAR(200),
        FQty decimal(28,10) Null,
        FCUUnitQty decimal(28,10) Null,
        FQty1 Decimal(28,10),
        FCUUnitQty1 Decimal(28,10),
        FAmount1 Decimal(28,10),
        FQty2 Decimal(28,10),
        FCUUnitQty2 Decimal(28,10),
        FAmount2 Decimal(28,10),
        FTemp bit ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10))
 Create Table #Happen1(
        FItemID int Null,
        FStockID int Null,
        FBatchNo NVARCHAR(200),
        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 ,FPrice Decimal(28,10),FCUPrice Decimal(28,10),FAmount Decimal(28,10) )
  Insert Into #Happen1
  Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty1,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty1,
(Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t5.FRob*t6.FQty Else 0 End)  As FQty2,
((Case When t5.FDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t5.FDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then CAST(t5.FRob*t6.FQty AS DECIMAL(28,10)) Else 0 End))/t7.FCoefficient  As FCUUnitQty2,
1 ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
 FROM ICStockBill t11 
 INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID 
     WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
         AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
         (t11.FCheckerID IS NULL OR t11.FCheckerID=0)
         AND t11.FTrantype IN(1,2,5,10,40,41) 
     ORDER BY t11.FDate DESC),0),0,0
 From t_ICItem t1
Join ICStockBill t5 On (t5.FStatus > 0 Or (t5.FUpStockWhenSave > 0 And t5.FCancellation <1 ))
Join ICStockBillEntry t6 On t5.FInterID=t6.FInterID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = (case when t5.ftrantype=24 then t6.FSCStockID else t6.FDCStockID end)
 Where t1.FItemID = t6.FItemID
 And ((t5.FTrantype In (1,2,5,10,40) And t5.FRob =1) Or (t5.FTrantype In(21,24,28,29) And t5.FRob=-1))
 AND (NOT (t5.FTrantype In (1) AND t5.FPOMode = 36681 ))
 And t2.FTypeID NOT IN (504)


  Insert Into #Happen1
  Select t1.FItemID,t2.FItemID As FStockID,t6.FBatchNo,0,0,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t6.FBegQty Else 0 End)  As FQty1,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-6,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-0,'2015-09-21'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty1,
(Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t6.FBegQty Else 0 End)  As FQty2,
((Case When t6.FStockInDate>=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) And t6.FStockInDate<=Convert(DateTime,Convert(Varchar,DATEADD(dd,-7,'2015-09-21'),101)) Then t6.FBegQty Else 0 End))/t7.FCoefficient  As FCUUnitQty2,
1  ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
 FROM ICStockBill t11 
 INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID 
     WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
         AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
         (t11.FCheckerID IS NULL OR t11.FCheckerID=0)
         AND t11.FTrantype IN(1,2,5,10,40,41) 
     ORDER BY t11.FDate DESC),0),0,0

From t_ICItem t1
Join ICInvInitial t6 On t1.FItemID = t6.FItemID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = t6.FStockID
 Where 1=1  And t2.FTypeID NOT IN (504)


CREATE TABLE #InventoryHanppen
(FItemID    INT           NOT NULL,
 FStockID   INT           NULL,
 FBatchNo   Varchar(255)  NULL,
 FQty     DECIMAL(28,10)  NOT NULL,
 FAmount  DECIMAL(28,10)  NOT NULL )
INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,u1.FStockID,u1.FBatchNo,u1.FBegQty,u1.FBegBal
FROM t_ICItem t1
INNER JOIN ICInvBal u1 ON t1.FItemID=u1.FItemID
LEFT  JOIN t_Stock  t2 ON t2.FItemID = u1.FStockID
WHERE u1.FYear=2015 AND u1.FPeriod=9 And t2.FTypeID NOT IN (504)

INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,t2.FItemID,u1.FBatchNo, CASE WHEN v1.FTranType IN (1,2,5,10,40,41,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FQty ELSE -1 * u1.FQty END,
CASE WHEN v1.FTranType IN (1,2,5,10,40,101,102) OR (v1.FTranType=100 AND v1.FBillTypeID=12542) THEN u1.FAmount WHEN v1.FTranType=41 THEN u1.FAmtRef ELSE -1 * u1.FAmount END
FROM t_ICItem t1
INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID
INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID
LEFT  JOIN t_Stock  t2 ON ((v1.FTrantype=24 AND u1.FSCStockID=t2.FItemID) OR (v1.FTranType IN (1,2,5,10,21,41,28,29,43,40,100,101,102) AND u1.FDCStockID=t2.FItemID ))
WHERE v1.FDate>='2015/09/01' AND v1.FDate<='2015/09/21' And t2.FTypeID NOT IN (504)

AND v1.Ftrantype In (1,2,5,10,21,24,41,28,29,43,40,100,101,102) AND (NOT (v1.FTrantype In (1) AND isnull(v1.FPOMode,0) = 36681 ))
AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))
INSERT INTO #InventoryHanppen(FItemID,FStockID,FBatchNo,FQty,FAmount)
SELECT u1.FItemID,t2.FItemID,u1.FBatchNo,-1 * u1.FQty ,-1 * u1.FAmount
FROM t_ICItem t1
INNER JOIN ICStockBillEntry u1 ON t1.FItemID=u1.FItemID
INNER JOIN ICStockBill v1 ON u1.FInterID=v1.FInterID
LEFT  JOIN t_Stock  t2 ON  u1.FSCStockID=t2.FItemID
WHERE v1.FDate>='2015/09/01' AND v1.FDate<='2015/09/21' And t2.FTypeID NOT IN (504)

AND v1.Ftrantype=41
AND (v1.FStatus > 0 Or (v1.FUpStockWhenSave > 0 And v1.FCancellation <1 ))
SELECT FItemID,FStockID,FBatchNo,SUM(FQty) AS FQty,SUM(FAmount) AS FAmount INTO #INVENTORY
FROM #InventoryHanppen
GROUP BY FItemID,FStockID,FBatchNo
DROP TABLE #InventoryHanppen
DELETE FROM #INVENTORY WHERE FQty<=0
 Insert Into #Happen1
Select t1.FItemID,t2.FItemID As FStockID,t3.FBatchNo,(t3.FQTY) As FQTY,CAST(t3.FQTY AS DECIMAL(28,10))/t7.FCoefficient As FCUUnitQty,0,0,0,0,1  ,ISNULL((SELECT TOP 1 (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)
 FROM ICStockBill t11 
 INNER JOIN ICStockBillEntry t12 ON t11.FInterID=t12.FInterID 
     WHERE (CASE WHEN t11.FTranType<>41 THEN t12.FPrice ELSE t12.FPriceRef END)<>0
         AND t12.FItemID=t1.FItemID AND t11.FCancellatiON=0 AND NOT
         (t11.FCheckerID IS NULL OR t11.FCheckerID=0)
         AND t11.FTrantype IN(1,2,5,10,40,41) 
     ORDER BY t11.FDate DESC),0),0,0

From t_ICItem t1
 Join #INVENTORY t3 On t1.FItemID = t3.FItemID
Left Join t_MeasureUnit t7 On t1.FStoreUnitID=t7.FMeasureUnitID
Left Join t_Stock t2 On t2.FItemID = t3.FStockID
Where 1=1
 And t2.FTypeID NOT IN (504)


 Insert Into #HAPPEN2
Select t1.FITEMID,FStockID,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,
Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,0,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,0,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY)
From #HAPPEN1 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID 
GROUP By t1.FITEMID,t1.FBatchNo,FStockID Update #Happen2 Set FQty1= FQty,FQty2=0
 Where  FQty-FQty1<0
 Update #Happen2 Set FQty2= FQty-FQty1
Update #Happen2 Set FAmount1=FPrice*FQty1
Update #Happen2 Set FAmount2=FPrice*FQty2

 Update #Happen2 Set FCUUnitQty1= FCUUnitQty,FCUUnitQty2=0
 Where  FCUUnitQty-FCUUnitQty1<0
 Update #Happen2 Set FCUUnitQty2= FCUUnitQty-FCUUnitQty1

 Insert Into #HAPPEN
Select t1.FITEMID,t1.FStockID,t1.FBatchNo,Sum(FQTY)As FQTY,Sum(FCUUnitQTY)As FCUUnitQTY,
Sum(fqty1) As FQty1 ,Sum(fCUUnitqty1) As FCUUnitQty1 ,Sum(FAmount1) As FAmount1,Sum(fqty2) As FQty2 ,Sum(fCUUnitqty2) As FCUUnitQty2 ,Sum(FAmount2) As FAmount2,1,Min(FPrice),case Sum(FCUUnitQTY) when 0 then 0 else (Min(FPrice)*Sum(FQTY))/Sum(FCUUnitQTY) end,Min(FPrice)*Sum(FQTY)
From #HAPPEN2 t1 INNER JOIN t_ICItem t2 ON t1.FItemID=t2.FItemID
GROUP By t1.FITEMID,t1.FBatchNo,t1.FStockID

HAVING (SUM(FQTY)>=0)
SET NOCOUNT ON
CREATE TABLE #ItemLevel(
 FNumber1 Varchar(355),
 FName1 Varchar(355),
 FNumber2 Varchar(355),
 FName2 Varchar(355),
 FNumber3 Varchar(355),
 FName3 Varchar(355),
 FItemID int,
 FNumber Varchar(355))

 INSERT INTO #ItemLevel SELECT 
 CASE WHEN CHARINDEX('.',FFullNumber)-1= -1 or FLevel<2 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber)-1)  END,
 '',
 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1= -1 or FLevel<3 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)-1)  END,
 '',
 CASE WHEN CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1= -1 or FLevel<4 THEN NULL ELSE SUBSTRING(FNumber, 1,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber,CHARINDEX('.',FFullNumber)+1)+1)-1)  END,
 '',
 FItemID,FNumber FROM t_Item
 WHERE FItemClassID=4
 AND FDetail=1  AND FItemID In (Select Distinct FItemID from #Happen )
 UPDATE t0 SET t0.FName1=t1.FName,t0.FName2=t2.FName,t0.FName3=t3.FName
  FROM #ItemLevel t0 left join t_Item t1 On t0.FNumber1=t1.FNumber  AND t1.FItemClassID=4 AND t1.FDetail=0
 left join t_Item t2 On t0.FNumber2=t2.FNumber  AND t2.FItemClassID=4 AND t2.FDetail=0
 left join t_Item t3 On t0.FNumber3=t3.FNumber  AND t3.FItemClassID=4 AND t3.FDetail=0

CREATE TABLE #DATA(
FName1 Varchar(355) Null,
FName2 Varchar(355) Null,
FName3 Varchar(355) Null,

FStockName 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 null,
     FPriceDecimal smallint null,
     FQty Decimal(28,10) Null,
     FCUUnitQty Decimal(28,10) Null,
     FPrice Decimal(28,10) NULL,
     FCUPrice Decimal(28,10) Null,
     FAmount Decimal(28,10) Null,
 FQty1 Decimal(28,10),
 FCUUnitQty1 Decimal(28,10),
 FAmount1 Decimal(28,10),
 FQty2 Decimal(28,10),
 FCUUnitQty2 Decimal(28,10),
 FAmount2 Decimal(28,10),
     FSumSort smallint not null Default(0),
Flevel0 Decimal(10,3),
Flevel1 Decimal(10,3),
Flevel2 Decimal(10,3),
Flevel3 Decimal(10,3),
Flevel4 Decimal(10,3),
Flevel5 Decimal(10,3),
Flevel6 Decimal(10,3),

     FID int IDENTITY)
INSERT INTO #DATA
SELECT tt1.FName1,tt1.FName2,tt1.FName3,t2.FName,t1.FNumber,'','','','','',MAX(t1.FQtyDecimal),MAX(t1.FPriceDecimal),Sum(FQty),Sum(FCUUnitQty), case Sum(FQty) when 0 then 0 else Sum(FAmount)/Sum(FQty) end,(CASE Sum(FCUUnitQty) WHEN 0 THEN 0 ELSE Sum(FAmount)/Sum(FCUUnitQty) END), sum(FAmount), SUM(FQty1),
 SUM(FCUUnitQty1),
 SUM(FAmount1),
 SUM(FQty2),
 SUM(FCUUnitQty2),
 SUM(FAmount2),
CASE   WHEN   Grouping(tt1.FName1)=1 THEN 106
  WHEN   Grouping(tt1.FName2)=1 THEN 107
  WHEN   Grouping(tt1.FName3)=1 THEN 108
  WHEN   Grouping(t2.FName)=1 THEN  109  WHEN   Grouping(t1.FNumber)=1 THEN 110  ELSE   0 END
,0 ,0 ,0 ,0 ,0 ,0 ,0  FROM #Happen v2
 Inner Join t_ICItem t1 On v2.FItemID=t1.FItemID
 Left Join t_Stock t2 On v2.FStockID=t2.FItemID
Inner Join #ItemLevel tt1

 On t1.FItemID=tt1.FItemID Where 1=1


  Group By tt1.FName1,tt1.FName2,tt1.FName3,t2.FName,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.FPriceDecimal=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.FStoreUnitID=t4.FMeasureUnitID  And t3.FStandard=1


SELECT IDENTITY(INT,1,1) AS FLevel0,FNAME1
 INTO #LEVEL0 FROM #DATA
Where FSumSort=107


UPDATE a3 SET a3.FLevel0=lv.FLevel0
FROM #DATA a3
INNER JOIN #LEVEL0 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0)

Where  Not( FSumSort=106)
DROP TABLE #LEVEL0
 Update  #DATA set FLevel0=(Select max(ISNULL(FLevel0,0))+1 From #DATA) Where 1=1 AND FSumSort=106

SELECT IDENTITY(INT,1,1) AS FLevel1,FNAME1 , FNAME2
 INTO #LEVEL1 FROM #DATA
Where FSumSort=108


ORDER BY  FLEVEL0
UPDATE a3 SET a3.FLevel1=lv.FLevel1
FROM #DATA a3
INNER JOIN #LEVEL1 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0)

Where  Not( FSumSort=106)
DROP TABLE #LEVEL1

SELECT IDENTITY(INT,1,1) AS FLevel2,FNAME1 ,FNAME2 , FNAME3
 INTO #LEVEL2 FROM #DATA
Where FSumSort=109


ORDER BY  FLEVEL0 ,  FLEVEL1
UPDATE a3 SET a3.FLevel2=lv.FLevel2
FROM #DATA a3
INNER JOIN #LEVEL2 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0) AND ISNULL(lv.FNAME3,0)=ISNULL(a3.FNAME3,0)

Where  Not( FSumSort=106)
DROP TABLE #LEVEL2

SELECT IDENTITY(INT,1,1) AS FLevel3,FNAME1 ,FNAME2 ,FNAME3 , FSTOCKNAME
 INTO #LEVEL3 FROM #DATA
Where FSumSort=110


ORDER BY  FLEVEL0 ,  FLEVEL1,  FLEVEL2
UPDATE a3 SET a3.FLevel3=lv.FLevel3
FROM #DATA a3
INNER JOIN #LEVEL3 lv ON ISNULL(lv.FNAME1,0)=ISNULL(a3.FNAME1,0) AND ISNULL(lv.FNAME2,0)=ISNULL(a3.FNAME2,0) AND ISNULL(lv.FNAME3,0)=ISNULL(a3.FNAME3,0) AND ISNULL(lv.FSTOCKNAME,0)=ISNULL(a3.FSTOCKNAME,0)

Where  Not( FSumSort=106)
DROP TABLE #LEVEL3

 Update #DATA Set FLevel0=FLevel0+0.9   Where FSumSort=107
 Update #DATA Set FLevel1=FLevel1+0.9   Where FSumSort=108
 Update #DATA Set FLevel2=FLevel2+0.9   Where FSumSort=109
 Update #DATA Set FLevel3=FLevel3+0.9   Where FSumSort=110
Update #Data Set  FName1=isnull(FName1,'')+'(小计)'  WHERE FSumSort=107
Update #Data Set  FName2=isnull(FName2,'')+'(小计)'  WHERE FSumSort=108
Update #Data Set  FName3=isnull(FName3,'')+'(小计)'  WHERE FSumSort=109
Update #Data Set FStockName=Rtrim(ISNULL(FStockName,''))+'(小计)' WHERE FSumSort=110
Update #Data Set FName1='合计' WHERE FSumSort=106
Update #Data Set FSumSort=101   WHERE FSumSort=106

SELECT * FROM #DATA 
 Order By  FLevel0,  FLevel1,  FLevel2,  FLevel3
DROP TABLE #DATA 
DROP TABLE #ItemLevel
 Drop Table #Happen
 Drop Table #Happen1
 Drop Table #Happen2
 DROP TABLE #INVENTORY

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:361704次
    • 积分:3109
    • 等级:
    • 排名:第11534名
    • 原创:18篇
    • 转载:150篇
    • 译文:1篇
    • 评论:33条
    文章分类