金蝶K3 销售毛利润表,是根据一段时间内的销售收入-发货数量*单位成本,这样无法分析单个产品面向客户的销售毛利润,
所以写了这个毛利润表,销售毛利润=销售收入-销售数量*单位成本,直接在BOS直接SQL语句就可以,不妥的地方还请大家
指正.谢谢,希望对大家有用.
Set NoCount On
Create Table #Data2(
FItemID Int Null,
FCustID Int Null,
FDeptID Int Null,
FEmpID Int Null,
FQtySale Decimal(28,10) Default(0),
FIncomeSale Decimal(28,10) Default(0),
FQtySend Decimal(28,10) Default(0),
fcostsale Decimal(28,10) Default(0),
funitcost Decimal(28,10) Default(0) )
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySend)
Select v2.FItemID, v1.FSupplyID,v1.FDeptID, v1.FEmpID,v2.FQty
From ICStockBill v1
Inner Join ICStockBillEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Organization t2 On v1.FSupplyID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FSaleStyle<>20296 And v1.FTranType=21
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySend)
Select v2.FItemID, v1.FSupplyID,v1.FDeptID,v1.FEmpID, v2.FQty
From ICPurchase v1
Inner Join ICPurchaseEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Supplier t2 On v1.FSupplyID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FPOStyle=20300 And v1.FSubSystemID In (-1,0)
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySale,FIncomeSale,fcostsale, funitcost)
Select v2.FItemID,v1.FCustID,v1.FDeptID,v1.FEmpID,v2.FQty,Round(Case v1.FTranType When 80 Then v2.FAmount * (IsNull(v1.FExchangeRate,1)+0.0000000001) Else (v2.FAmount -
v2.FTaxAmount)* (IsNull(v1.FExchangeRate,1)+0.0000000001) End,2),v2.fentryselfi0457*v2.FQty,v2.fentryselfi0457
From ICSale v1
Inner Join ICSaleEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Organization t2 On v1.FCustID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FTranType in (80,86)
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
CREATE TABLE #ItemLevel(
FNumber1 Varchar(355),
FName1 Varchar(355),
FNumber2 Varchar(355),
FName2 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,
'',
FItemID,FNumber From t_Item
Where FItemClassID=4
AND FDetail=1 And FItemID In (Select Distinct FItemID From #Data2 )
Update t0 SET t0.FName1=t1.FName,t0.FName2=t2.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
Create Table #Data(
FName1 VarChar(355) Null,
FName2 VarChar(355) Null,
FCustName 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,
FQtySale Decimal(28,10),
FIncomeSale Decimal(28,10),
FQtySend Decimal(28,10),
FCostSale Decimal(28,10),
FProfit Decimal(28, 10),
FCUUnitName VarChar(355) Null,
FCUUnitQtySale Decimal(28,10),
FCUUnitQtySend Decimal(28,10),
FUnitCost Decimal(28,10),
FSumSort SmallInt Not Null Default 0,
FID Int IDENTITY
)
Insert Into #Data
Select tt1.FName1,tt1.FName2,t2.FName,t1.FNumber,'','','','',Max(t1.FQtyDecimal),Max(t1.FPriceDecimal),Sum(IsNull(v1.FQtySale,0)),Sum(Round(v1.FIncomeSale,2)),
Sum(IsNull(v1.FQtySend,0)),Sum(isnull(v1.fcostsale,0)),Sum(v1.FIncomeSale)-Sum(v1.fcostsale) ,
'', Sum(IsNull(v1.FQtySale,0)/(Case When IsNull(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )) ,
Sum(IsNull(v1.FQtySend,0)/(Case When IsNull(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )) ,
sum(v1.funitcost),
Case When Grouping(tt1.FName1)=1 Then 106
When Grouping(tt1.FName2)=1 Then 107
When Grouping(t2.FName)=1 Then 108 When Grouping(t1.FNumber)=1 Then 109 Else 0 End
From #Data2 v1 Join t_ICItem t1 On v1.FItemID=t1.FItemID
Left Join t_Organization t2 On v1.FCustID=t2.FItemID
Left Join t_MeasureUnit t3 On t1.FSaleUnitID = t3.FMeasureUnitID
Join #ItemLevel tt1 On t1.FItemID=tt1.FItemID
Where 1=1
Group By tt1.FName1,tt1.FName2,t2.FName,t1.FNumber With RollUp
Drop Table #Data2
Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FCUUnitName = t4.FName,
t1.FUnitName=t3.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.FSaleUnitID=t4.FMeasureUnitID And t3.FStandard=1
Update #Data Set FName1=FName1+'(小计)' Where FSumSort=107
Update #Data Set FName2=FName2+'(小计)' Where FSumSort=108
Update #Data Set FCustName=FCustName+'(小计)' Where FSumSort=109
Update #Data Set FName1='合计' Where FSumSort=106
Update #Data Set FSumSort=101 Where FSumSort=106
Select *,Case When FIncomeSale=0 Then '' Else LTrim(CAST(CAST(Round(CAST(FProfit AS FLOAT)/CAST(FIncomeSale AS FLOAT)*100,2) AS Decimal(28,2)) AS VarChar(50))) +'%' End
As FRate From #Data
Order By FID
Drop Table #Data
Drop Table #ItemLevel
-----解决了单位成本的错误问题
Set NoCount On
Create Table #Data2(
FItemID Int Null,
FCustID Int Null,
FDeptID Int Null,
FEmpID Int Null,
FQtySale Decimal(28,10) Default(0),
FIncomeSale Decimal(28,10) Default(0),
FQtySend Decimal(28,10) Default(0),
fcostsale Decimal(28,10) Default(0),
funitcost Decimal(28,10) Default(0) )
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySend)
Select v2.FItemID, v1.FSupplyID,v1.FDeptID, v1.FEmpID,v2.FQty
From ICStockBill v1
Inner Join ICStockBillEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Organization t2 On v1.FSupplyID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FSaleStyle<>20296 And v1.FTranType=21
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySend)
Select v2.FItemID, v1.FSupplyID,v1.FDeptID,v1.FEmpID, v2.FQty
From ICPurchase v1
Inner Join ICPurchaseEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Supplier t2 On v1.FSupplyID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FPOStyle=20300 And v1.FSubSystemID In (-1,0)
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySale,FIncomeSale,fcostsale, funitcost)
Select v2.FItemID,v1.FCustID,v1.FDeptID,v1.FEmpID,v2.FQty,Round(Case v1.FTranType When 80 Then v2.FAmount * (IsNull(v1.FExchangeRate,1)+0.0000000001) Else (v2.FAmount -
v2.FTaxAmount)* (IsNull(v1.FExchangeRate,1)+0.0000000001) End,2),v2.fentryselfi0457*v2.FQty,v2.fentryselfi0457
From ICSale v1
Inner Join ICSaleEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Organization t2 On v1.FCustID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FTranType in (80,86)
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
CREATE TABLE #ItemLevel(
FNumber1 Varchar(355),
FName1 Varchar(355),
FNumber2 Varchar(355),
FName2 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,
'',
FItemID,FNumber From t_Item
Where FItemClassID=4
AND FDetail=1 And FItemID In (Select Distinct FItemID From #Data2 )
Update t0 SET t0.FName1=t1.FName,t0.FName2=t2.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
Create Table #Data(
FName1 VarChar(355) Null,
FName2 VarChar(355) Null,
FCustName 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,
FQtySale Decimal(28,10),
FIncomeSale Decimal(28,10),
FQtySend Decimal(28,10),
FCostSale Decimal(28,10),
FProfit Decimal(28, 10),
FCUUnitName VarChar(355) Null,
FCUUnitQtySale Decimal(28,10),
FCUUnitQtySend Decimal(28,10),
FUnitCost Decimal(28,10),
FSumSort SmallInt Not Null Default 0,
FID Int IDENTITY
)
Insert Into #Data
Select tt1.FName1,tt1.FName2,t2.FName,t1.FNumber,'','','','',Max(t1.FQtyDecimal),Max(t1.FPriceDecimal),Sum(IsNull(v1.FQtySale,0)),Sum(Round(v1.FIncomeSale,2)),
Sum(IsNull(v1.FQtySend,0)),Sum(isnull(v1.fcostsale,0)),Sum(v1.FIncomeSale)-Sum(v1.fcostsale) ,
'', Sum(IsNull(v1.FQtySale,0)/(Case When IsNull(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )) ,
Sum(IsNull(v1.FQtySend,0)/(Case When IsNull(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )) ,
0,
Case When Grouping(tt1.FName1)=1 Then 106
When Grouping(tt1.FName2)=1 Then 107
When Grouping(t2.FName)=1 Then 108 When Grouping(t1.FNumber)=1 Then 109 Else 0 End
From #Data2 v1 Join t_ICItem t1 On v1.FItemID=t1.FItemID
Left Join t_Organization t2 On v1.FCustID=t2.FItemID
Left Join t_MeasureUnit t3 On t1.FSaleUnitID = t3.FMeasureUnitID
Join #ItemLevel tt1 On t1.FItemID=tt1.FItemID
Where 1=1
Group By tt1.FName1,tt1.FName2,t2.FName,t1.FNumber With RollUp
Drop Table #Data2
Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FCUUnitName = t4.FName,
t1.FUnitName=t3.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.FSaleUnitID=t4.FMeasureUnitID And t3.FStandard=1
Update #Data Set FName1=FName1+'(小计)' Where FSumSort=107
Update #Data Set FName2=FName2+'(小计)' Where FSumSort=108
Update #Data Set FCustName=FCustName+'(小计)' Where FSumSort=109
Update #Data Set FName1='合计' Where FSumSort=106
Update #Data Set FSumSort=101 Where FSumSort=106
-----更新单位成本
Update #Data Set FUnitCost=(Case When FQtysale=0 Then Null Else FCostSale/FQtySale End)
Select *,Case When FIncomeSale=0 Then '' Else LTrim(CAST(CAST(Round(CAST(FProfit AS FLOAT)/CAST(FIncomeSale AS FLOAT)*100,2) AS Decimal(28,2)) AS VarChar(50))) +'%' End
As FRate From #Data
Order By FID
Drop Table #Data
Drop Table #ItemLevel
------增加了新列，销售单价
Set NoCount On
Create Table #Data2(
FItemID Int Null,
FCustID Int Null,
FDeptID Int Null,
FEmpID Int Null,
FQtySale Decimal(28,10) Default(0),
FIncomeSale Decimal(28,10) Default(0),
FQtySend Decimal(28,10) Default(0),
fcostsale Decimal(28,10) Default(0),
funitcost Decimal(28,10) Default(0),
fprice Decimal(28,10) Default(0) )
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySend)
Select v2.FItemID, v1.FSupplyID,v1.FDeptID, v1.FEmpID,v2.FQty
From ICStockBill v1
Inner Join ICStockBillEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Organization t2 On v1.FSupplyID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FSaleStyle<>20296 And v1.FTranType=21
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySend)
Select v2.FItemID, v1.FSupplyID,v1.FDeptID,v1.FEmpID, v2.FQty
From ICPurchase v1
Inner Join ICPurchaseEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Supplier t2 On v1.FSupplyID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FPOStyle=20300 And v1.FSubSystemID In (-1,0)
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
Insert Into #Data2(FItemID,FCustID,FDeptID,FEmpID,FQtySale,FIncomeSale,fcostsale, funitcost,fprice)
Select v2.FItemID,v1.FCustID,v1.FDeptID,v1.FEmpID,v2.FQty,Round(Case v1.FTranType When 80 Then v2.FAmount * (IsNull(v1.FExchangeRate,1)+0.0000000001) Else (v2.FAmount -
v2.FTaxAmount)* (IsNull(v1.FExchangeRate,1)+0.0000000001) End,2),v2.fentryselfi0457*v2.FQty,v2.fentryselfi0457,v2.fprice
From ICSale v1
Inner Join ICSaleEntry v2 On v1.FInterID = v2.FInterID
Inner Join t_ICItem t1 On v2.FItemID = t1.FItemID
Left Join t_Organization t2 On v1.FCustID=t2.FItemID
Left Join t_Department TD On v1.FDeptID = TD.FItemID
Left Join t_Emp TE On v1.FEmpID=TE.FItemID
Where v1.FTranType in (80,86)
And v1.FCancelLation=0 And v1.FHookStatus > 0
AND v1.FDate>='********'
And v1.FDate<='########'
AND t2.fnumber>='*CustNo*'
AND t2.fnumber<='#CustNo#'
AND t1.fnumber>='*ItemNo*'
AND t1.fnumber<='#ItemNo#'
CREATE TABLE #ItemLevel(
FNumber1 Varchar(355),
FName1 Varchar(355),
FNumber2 Varchar(355),
FName2 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,
'',
FItemID,FNumber From t_Item
Where FItemClassID=4
AND FDetail=1 And FItemID In (Select Distinct FItemID From #Data2 )
Update t0 SET t0.FName1=t1.FName,t0.FName2=t2.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
Create Table #Data(
FName1 VarChar(355) Null,
FName2 VarChar(355) Null,
FCustName 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,
FQtySale Decimal(28,10),
fprice Decimal(28,10),
FIncomeSale Decimal(28,10),
FQtySend Decimal(28,10),
FCostSale Decimal(28,10),
FProfit Decimal(28, 10),
FCUUnitName VarChar(355) Null,
FCUUnitQtySale Decimal(28,10),
FCUUnitQtySend Decimal(28,10),
FUnitCost Decimal(28,10),
FSumSort SmallInt Not Null Default 0,
FID Int IDENTITY
)
Insert Into #Data
Select tt1.FName1,tt1.FName2,t2.FName,t1.FNumber,'','','','',Max(t1.FQtyDecimal),Max(t1.FPriceDecimal),Sum(IsNull(v1.FQtySale,0)),0,Sum(Round(v1.FIncomeSale,2)),
Sum(IsNull(v1.FQtySend,0)),Sum(isnull(v1.fcostsale,0)),Sum(v1.FIncomeSale)-Sum(v1.fcostsale) ,
'', Sum(IsNull(v1.FQtySale,0)/(Case When IsNull(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )) ,
Sum(IsNull(v1.FQtySend,0)/(Case When IsNull(t3.FCoefficient,0)=0 Then 1 Else t3.FCoefficient End )) ,
0,
Case When Grouping(tt1.FName1)=1 Then 106
When Grouping(tt1.FName2)=1 Then 107
When Grouping(t2.FName)=1 Then 108 When Grouping(t1.FNumber)=1 Then 109 Else 0 End
From #Data2 v1 Join t_ICItem t1 On v1.FItemID=t1.FItemID
Left Join t_Organization t2 On v1.FCustID=t2.FItemID
Left Join t_MeasureUnit t3 On t1.FSaleUnitID = t3.FMeasureUnitID
Join #ItemLevel tt1 On t1.FItemID=tt1.FItemID
Where 1=1
Group By tt1.FName1,tt1.FName2,t2.FName,t1.FNumber With RollUp
Drop Table #Data2
Update t1 Set t1.FName=t2.FName,t1.FShortNumber=t2.FShortNumber,t1.FModel=t2.FModel, t1.FCUUnitName = t4.FName,
t1.FUnitName=t3.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.FSaleUnitID=t4.FMeasureUnitID And t3.FStandard=1
Update #Data Set FName1=FName1+'(小计)' Where FSumSort=107
Update #Data Set FName2=FName2+'(小计)' Where FSumSort=108
Update #Data Set FCustName=FCustName+'(小计)' Where FSumSort=109
Update #Data Set FName1='合计' Where FSumSort=106
Update #Data Set FSumSort=101 Where FSumSort=106
Update #Data Set FUnitCost=(Case When FQtysale=0 Then Null Else FCostSale/FQtySale End)
---单价
Update #Data Set Fprice=(Case When FQtysale=0 Then Null Else FincomeSale/FQtySale End)
Select *,Case When FIncomeSale=0 Then '' Else LTrim(CAST(CAST(Round(CAST(FProfit AS FLOAT)/CAST(FIncomeSale AS FLOAT)*100,2) AS Decimal(28,2)) AS VarChar(50))) +'%' End
As FRate From #Data
Order By FID
Drop Table #Data
Drop Table #ItemLevel |
发表于 2009-9-19 12:07:57
