请教KIS专业版用SQL查询不含税金额的问题

情归何处

请教KIS专业版用SQL查询不含税金额的问题

我创建一个表
CREATE TABLE #DATA(
FBatchNo   VARCHAR(355) null,
     FNumber  VARCHAR(355) null,
     FShortNumber  VARCHAR(355) null,
     FName  VARCHAR(355) null,
     FModel  VARCHAR(355) null,
     FUnitName  VARCHAR(355) null,
     FQtyDecimal smallint null,
     FPriceDecimal smallint null,
     FBegQty decimal(28,10),
     FBegPrice decimal(28,10),
     FBegBal decimal(28,10),
     FBegDiff decimal(28,10),
     FInQty  decimal(28,10),
     FInPrice  decimal(28,10),
     FInAmount decimal(28,10),
     FInDiff decimal(28,10),
     FOutQty decimal(28,10),
     FOutPrice decimal(28,10),
     FOutAmount decimal(28,10),
     FOutDiff decimal(28,10),
     FEndQty decimal(28,10),
     FEndPrice decimal(28,10),
     FEndAmount decimal(28,10),
     FEndDiff decimal(28,10),
     FSumSort smallint not null Default(0),
     FID int IDENTITY)
然后
使用这个表里的FInAmount除以ICStockBillEntry表里的自定义出来的一个列FEntrySelfA0131，
和这个表里的FOutAmount除以ICStockBillEntry表里的自定义出来的一个列FEntrySelfB0425，
但这两个表之间关联，又要与T_ICITEM物料表的ID相关联。。
不知道怎么样UPDATE
---------------------------------
FEntrySelfA0131
是自定义的入库单里的税率，
FEntrySelfA0132
是自定义的入库单里的不含税金额。
FEntrySelfB0425
是自定义的出库单里的税率。
FEntrySelfB0424
是自定义的出库单里的不含税金额。
---------------------------------
UPDATE 表#DATA 到最后，能得出这样的一个不含税的表：
     FBegQty AS '期初数量',
     FBegPrice AS '期初单价',
     FBegBal AS '期初金额',
     FInQty AS '本期收入数量',
     FInPrice AS '本期收入单价',
     FInAmount AS '本期收入金额',
     FOutQty AS '本期支出数量',
     FOutPrice AS '本期支出单价',
     FOutAmount AS '本期支出金额',
     FEndQty AS '期未结存数',
     FEndPrice AS '期未结存单价',
     FEndAmount AS '期未结存金额'
整个脚本我发在楼下：

情归何处

CREATE TABLE #Happen(
        FStockid  int Null,
        FItemID int Null,
        FQtyDecimal int Null,
        FPriceDecimal int Null,
        FBatchNo Varchar(355),
        FBegQty decimal(28,10),
        FBegBal decimal(28,10),
        FBegDiff decimal(28,10),
        FInQty  decimal(28,10),
        FInAmount decimal(28,10),
        FInDiff decimal(28,10),
        FOutQty decimal(28,10),
        FOutAmount decimal(28,10),
        FOutDiff decimal(28,10))
  INSERT INTO #Happen(FStockid,FItemID,FBatchNo,FBegQty,FBegBal,
  FBegDiff,FInQty,FInAmount,FInDiff,FOutQty,FOutAmount,FOutDiff)
  SELECT v1.FStockid,v1.FItemID,v1.FBatchNo,v1.FBegQty,
  CASE WHEN t1.FTrack=81 THEN round(v1.FBegBal,2)-round(v1.FBegDiff,2) else round(v1.FBegBal,2) end,v1.FBegDiff,0,0,0,0,0,0
  FROM ICinvBal v1,t_ICItem t1,t_stock

  WHERE v1.FYear=2008
  AND  v1.FPeriod=11
  AND  v1.FItemID=t1.FItemID
  AND  v1.FStockID=t_stock.FItemID

  AND  Not (v1.FBegQty=0  AND v1.FBegBal=0 AND v1.FBegDiff=0)  
  INSERT INTO #Happen(FStockid,FItemID,FBatchNo,FBegQty,FBegBal,FBegDiff,FInQty,FInAmount,FInDiff,FOutQty,FOutAmount,FOutDiff)
SELECT  Case When v2.FDCStockID in(Select FItemID From t_stock ) Then v2.FDCStockID else v2.FSCStockID End, v2.FItemID,v2.FBatchNo,0,0,0,
CASE WHEN v1.FTranType IN(1,2,5,10,40,101,102) or (v1.FTranType=100 and v1.FBillTypeID=12542) THEN  v2.FQty ELSE 0 END ,
CASE WHEN (v1.FTranType IN(1,2,5,10,40,101,102) or (v1.FTranType=100 and v1.FBillTypeID=12542)) AND t1.FTrack<>81 THEN  round(v2.FAmount,2)
WHEN (v1.FTranType IN(1,2,5,10,40,100,101,102) or (v1.FTranType=100 and v1.FBillTypeID=12542)) AND t1.FTrack=81 THEN v2.FPlanAmount ELSE 0 END ,
CASE WHEN v1.FTranType IN(1,2,5,10,40,101,102) or (v1.FTranType=100 and v1.FBillTypeID=12542) THEN v2.FAmount- v2.FPlanAmount ELSE 0 END ,
CASE WHEN v1.FTranType IN(21,24,28,29,43) or (v1.FTranType=100 and v1.FBillTypeID=12541) THEN  v2.FQty ELSE 0 END ,
CASE WHEN (v1.FTranType IN(21,24,28,29,43) or (v1.FTranType=100 and v1.FBillTypeID=12541)) AND t1.FTrack<>81 THEN  round(v2.FAmount,2)
WHEN (v1.FTranType IN(21,24,28,29,43)  or (v1.FTranType=100 and v1.FBillTypeID=12541))  AND t1.FTrack=81 THEN v2.FPlanAmount ELSE 0 END ,
CASE WHEN v1.FTranType IN(21,24,28,29,43) or (v1.FTranType=100 and v1.FBillTypeID=12541)  THEN v2.FAmount-v2.FPlanAmount ELSE 0 END
FROM ICStockBill v1,ICStockBillEntry v2,t_ICItem t1

WHERE v1.FInterID=v2.FInterID
AND v2.FItemID=t1.FItemID

AND v1.FDate >='2008-10-26'
AND v1.FDate <'2008-11-26'
AND v1.FTranType<>41 AND (v1.FDCStockID=v2.FDCStockID Or v1.FSCStockID=v2.FSCStockID)  And v1.FCancelLation=0  union all SELECT Case When v2.FDCStockID in(Select FItemID From t_stock ) Then v2.FDCStockID else v2.FSCStockID End,v2.FItemID,v2.FBatchNo,0,0,0,
CASE WHEN v1.FTranType IN(1,2,5,10,40,100,101,102,41) THEN  v2.FQty ELSE 0 END ,
CASE WHEN v1.FTranType IN(1,2,5,10,40,100,101,102,41) AND t1.FTrack<>81 THEN  round(v2.FAmtRef,2)
WHEN v1.FTranType IN(1,2,5,10,40,100,101,102,41) AND t1.FTrack=81 THEN v2.FPlanAmount ELSE 0 END ,
CASE WHEN v1.FTranType IN(1,2,5,10,40,100,101,102,41) THEN v2.FAmtRef-v2.FPlanAmount ELSE 0 END ,
0 ,
0 ,
0
FROM ICStockBill v1,ICStockBillEntry v2,t_ICItem t1

WHERE v1.FInterID=v2.FInterID
AND v2.FItemID=t1.FItemID

AND v1.FDate >='2008-10-26'
AND v1.FDate <'2008-11-26'
AND v1.FTranType=41  And v1.FCancelLation=0  union all SELECT Case When v2.FSCStockID in(Select FItemID From t_stock ) Then v2.FSCStockID else v2.FDCStockID End,v2.FItemID,v2.FBatchNo,0,0,0,
0,
0,
0,
CASE WHEN v1.FTranType IN(21,24,28,29,43,41) THEN  v2.FQty ELSE 0 END ,
CASE WHEN v1.FTranType IN(21,24,28,29,43,41) AND t1.FTrack<>81 THEN  v2.FAmount
WHEN v1.FTranType IN(21,24,28,29,43,41) AND t1.FTrack=81 THEN v2.FPlanAmount ELSE 0 END ,
CASE WHEN v1.FTranType IN(21,24,28,29,43,41) THEN  v2.FAmount- v2.FPlanAmount ELSE 0 END
FROM ICStockBill v1,ICStockBillEntry v2,t_ICItem t1

WHERE v1.FInterID=v2.FInterID
AND v2.FItemID=t1.FItemID

AND v1.FDate >='2008-10-26'
AND v1.FDate <'2008-11-26'
AND v1.FTranType=41  And v1.FCancelLation=0  
UPDATE #Happen SET FQtyDecimal=t_ICItem.FQtyDecimal,FPriceDecimal=t_ICItem.FPriceDecimal FROM #Happen inner join t_ICItem on  #Happen.FItemID=t_ICItem.FItemID
UPDATE #Happen SET FBatchNo='' FROM #Happen inner join t_ICItem on  #Happen.FItemID=t_ICItem.FItemID where t_ICItem.Ftrack<>80

SET NOCOUNT ON
CREATE TABLE #DATA(
FBatchNo   VARCHAR(355) null,
     FNumber  VARCHAR(355) null,
     FShortNumber  VARCHAR(355) null,
     FName  VARCHAR(355) null,
     FModel  VARCHAR(355) null,
     FUnitName  VARCHAR(355) null,
     FQtyDecimal smallint null,
     FPriceDecimal smallint null,
     FBegQty decimal(28,10),
     FBegPrice decimal(28,10),
     FBegBal decimal(28,10),
     FBegDiff decimal(28,10),
     FInQty  decimal(28,10),
     FInPrice  decimal(28,10),
     FInAmount decimal(28,10),
     FInDiff decimal(28,10),
     FOutQty decimal(28,10),
     FOutPrice decimal(28,10),
     FOutAmount decimal(28,10),
     FOutDiff decimal(28,10),
     FEndQty decimal(28,10),
     FEndPrice decimal(28,10),
     FEndAmount decimal(28,10),
     FEndDiff decimal(28,10),
     FSumSort smallint not null Default(0),
     FID int IDENTITY)

INSERT INTO #DATA ( FBatchNo,FNumber,FShortNumber,FName,FModel,FUnitName,
FQtyDecimal,FPriceDecimal,FBegQty,FBegPrice,
FBegBal,FBegDiff,FInQty,FInPrice,FInAmount,FInDiff,FOutQty,
FOutPrice,FOutAmount,FOutDiff,FEndQty,FEndPrice,FEndAmount,FEndDiff,FSumSort)
SELECT CASE  WHEN GROUPING(t1.FNumber)=1  THEN '合计'
WHEN GROUPING(v2.FBatchNo)=1 THEN  v2.FBatchNo+'小计'
ELSE v2.FBatchNo END,
t1.FNumber,'','','','',Max(v2.FQtyDecimal),Max(v2.FPriceDecimal), SUM(isNull(v2.FBegQty,0)),0,SUM(isNull(v2.FBegBal,0)),SUM(isNull(v2.FBegDiff,0)),SUM(isNull(FInQty,0)),0,SUM(isNull(FInAmount,0)),SUM(isNull(v2.FInDiff,0)),SUM(isNull(FOutQty,0)), 0,SUM(isNull(FOutAmount,0)),SUM(isNull(v2.FOutDiff,0)),0,0,0,0, CASE WHEN GROUPING(t1.FNumber)=1 THEN 101
WHEN  GROUPING(v2.FBatchNo)=1 THEN 102 ELSE 0 END  FROM #Happen v2,t_ICItem t1  WHERE  v2.FItemID=t1.FItemID


GROUP BY t1.FNumber,v2.FBatchNo WITH ROLLUP
UPDATE t1 SET t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FUnitName=t3.FName,t1.FQtyDecimal=t2.FQtyDecimal,t1.FPriceDecimal=t2.FPriceDecimal  FROM #DATA t1,t_ICItem t2,t_MeasureUnit t3  WHERE t1.FNumber=t2.FNumber  AND t2.FUnitGroupID=t3.FUnitGroupID  AND t3.FStandard=1
UPDATE #Data SET FEndQty=FBegQty+FInQty-FOutQty, FEndAmount=ROUND(FBegBal,2)+ROUND(FInAmount,2)-ROUND(FOutAmount,2) Update #Data SET FBegPrice=FBegBal/FBegQty WHERE FBegQty<>0
Update #Data SET FInPrice=FInAmount/FInQty WHERE FInQty<>0
Update #Data SET FOutPrice=FOutAmount/FOutQty WHERE FOutQty<>0
Update #Data SET FEndPrice=FEndAmount/FEndQty WHERE FEndQty<>0
GO
Update #Data SET #Data.FInAmount=#Data.FInAmount/ICStockBillEntry.FEntrySelfA0131    FROM #Data , ICStockBillEntry WHERE #Data.FInAmount<>0  and ICStockBillEntry.FEntrySelfA0131 is not null
GO
Update #Data SET #Data.FOutAmount=#Data.FOutAmount/ICStockBillEntry.FEntrySelfB0425  FROM #Data , ICStockBillEntry WHERE #Data.FOutAmount<>0 and ICStockBillEntry.FEntrySelfB0425 is not null

SELECT * FROM #DATA  WHERE FSumSort>100 ORDER BY FID

DROP TABLE #DATA  
DROP TABLE #Happen



请高人指教。。。。。。。。。。。

情归何处

怎么没有高手出手呢？

情归何处

到这里还是没人会呀？