679
社区成员
发帖
与我相关
我的任务
分享
DECLARE @SDate DATETIME
DECLARE @EDate DATETIME
DECLARE @Supplier NVARCHAR(20)
DECLARE @SSupplierNumber NVARCHAR(20)
DECLARE @ESupplierNumber NVARCHAR(20)
DECLARE @Client NVARCHAR(20)
DECLARE @SClientNumber NVARCHAR(20)
DECLARE @EClientNumber NVARCHAR(20)
DECLARE @FName NVARCHAR(200)
DECLARE @FBatchNo NVARCHAR(200)
SET @SDate='2015-01-01'
SET @EDate='2015-11-01'
SET @Supplier=''
SET @SSupplierNumber='07.01.01.001'
SET @ESupplierNumber='07.01.01.001'
SET @Client=''
SET @SClientNumber=''
SET @EClientNumber=''
SET @FName=''
SET @FBatchNo=''
SELECT
rn,
rn1,
采购单号,
CASE
WHEN rn IS NULL AND 采购单号<>'合计' THEN 采购单号+' 小计'
WHEN rn IS NULL AND 采购单号='合计' THEN '合计'
ELSE 采购入库摘要
END 采购入库摘要,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE 对比编号 END 对比编号,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE 供应商 END 供应商,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE 供应商编码 END 供应商代码,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 入库日期 END 入库日期,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE 采购物料名称 END 采购物料名称,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE 采购批号 END 采购批号,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 采购单价 END 采购单价,
采购数量,
采购金额,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 换单费 END 换单费,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 港杂费 END 港杂费,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 卫生费 END 卫生费,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 检疫费 END 检疫费,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 冷藏费 END 冷藏费,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 代理费 END 代理费,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 其他费 END 其他费,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 港上费用小计 END 港上费用小计,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE 客户 END 客户,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE 客户编码 END 客户编码,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE 物料名称 END 物料名称,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN '' ELSE FBatchNo END FBatchNo,
CASE WHEN rn IS NULL AND 采购单号<>'合计' THEN NULL ELSE 单价 END 单价,
件数,
金额,
CASE WHEN rn IS NULL AND 采购单号='合计' THEN 金额-采购金额-港上费用小计 END AS 毛利
FROM(
--------------------------------------------------------------------
--------------------------------------------------------------------
SELECT
top 100000
--rn,
MAX(rn1) rn1,
CASE WHEN(GROUPING(采购单号)=1) THEN '合计' ELSE ISNULL(采购单号,'') END AS 采购单号,
CASE WHEN(GROUPING(rn)=1) THEN null ELSE ISNULL(rn,0) END AS rn,
MAX(采购入库摘要) 采购入库摘要,
MAX(对比编号) 对比编号,
MAX(供应商) 供应商,
MAX(供应商编码) 供应商编码,
MAX(入库日期) 入库日期,
MAX(采购物料名称) 采购物料名称,
MAX(采购批号) 采购批号,
MAX(采购单价) 采购单价,
SUM(采购数量) 采购数量,
SUM(采购金额) 采购金额,
MAX(换单费) 换单费,
MAX(港杂费) 港杂费,
MAX(卫生费) 卫生费,
MAX(检疫费) 检疫费,
MAX(冷藏费) 冷藏费,
MAX(代理费) 代理费,
MAX(其他费) 其他费,
MAX(港上费用小计) 港上费用小计,
MAX(客户) 客户,
MAX(客户编码) 客户编码,
MAX(物料名称) 物料名称,
MAX(FBatchNo) FBatchNo,
MAX(单价) 单价,
SUM(件数) 件数,
SUM(金额) 金额
FROM
(
--------------------------------------------------------------------
SELECT top 1000000
rn,
rn1,
采购单号,
CASE WHEN rn=1 THEN FExplanation ELSE NUll END 采购入库摘要,
CASE WHEN rn=1 THEN 对比编号 ELSE NUll END 对比编号,
CASE WHEN rn=1 THEN 供应商 ELSE NUll END 供应商,
CASE WHEN rn=1 THEN 供应商编码 ELSE NUll END 供应商编码,
CASE WHEN rn=1 THEN 入库日期 ELSE NUll END 入库日期,
CASE WHEN rn1=1 THEN 采购物料名称 ELSE NUll END 采购物料名称,
CASE WHEN rn1=1 THEN 采购批号 ELSE NUll END 采购批号,
CASE WHEN rn1=1 THEN 采购单价 ELSE NUll END 采购单价,
CASE WHEN rn1=1 THEN 采购数量 ELSE NUll END 采购数量,
CASE WHEN rn1=1 THEN 采购金额 ELSE NUll END 采购金额,
CASE WHEN rn=1 THEN
(SELECT SUM(换单费) FROM v_ICSaleCostDataNote t1 WHERE t1.FBillNo LIKE ''+ t0.对比编号 +'%') ELSE NULL END 换单费,
CASE WHEN rn=1 THEN
(SELECT SUM(港杂费) FROM v_ICSaleCostDataNote t2 WHERE t2.FBillNo LIKE ''+ t0.对比编号 +'%') ELSE NULL END 港杂费,
CASE WHEN rn=1 THEN
(SELECT SUM(卫生费) FROM v_ICSaleCostDataNote t3 WHERE t3.FBillNo LIKE ''+ t0.对比编号 +'%') ELSE NULL END 卫生费,
CASE WHEN rn=1 THEN
(SELECT SUM(检疫费) FROM v_ICSaleCostDataNote t4 WHERE t4.FBillNo LIKE ''+ t0.对比编号 +'%') ELSE NULL END 检疫费,
CASE WHEN rn=1 THEN
(SELECT SUM(冷藏费) FROM v_ICSaleCostDataNote t5 WHERE t5.FBillNo LIKE ''+ t0.对比编号 +'%') ELSE NULL END 冷藏费,
CASE WHEN rn=1 THEN
(SELECT SUM(代理费) FROM v_ICSaleCostDataNote t6 WHERE t6.FBillNo LIKE ''+ t0.对比编号 +'%') ELSE NULL END 代理费,
CASE WHEN rn=1 THEN
(SELECT SUM(其他费) FROM v_ICSaleCostDataNote t7 WHERE t7.FBillNo LIKE ''+ t0.对比编号 +'%') ELSE NULL END 其他费,
CASE WHEN rn=1 THEN
(SELECT SUM(港上费用合计) FROM v_ICSaleCostDataNote t8 WHERE t8.FBillNo LIKE ''+ t0.对比编号 +'%') ELSE NULL END 港上费用小计,
客户,
客户编码,
物料名称,
FBatchNO,
单价,
件数,
金额
FROM
(SELECT top 100000 ROW_NUMBER() OVER (partition BY 采购物料名称,采购单号 ORDER BY FEntryID) rn1, *
FROM (
SELECT ROW_NUMBER() OVER (partition BY 采购单号 ORDER BY FInterID,FEntryID) rn, *
FROM
v_QianKuan
WHERE (入库日期>=@SDate AND 入库日期<=@EDate)
AND 供应商 LIKE '%'+ @Supplier +'%'
AND 客户 LIKE '%'+@Client+'%'
AND 采购物料名称 LIKE '%'+ @FName +'%'
AND FBatchNo LIKE '%'+ @FBatchNo +'%'
AND 供应商编码>=@SSupplierNumber AND 供应商编码<=@ESupplierNumber
) s ORDER BY 采购单号,rn) t0
--------------------------------------------------------------------
) tt GROUP BY 采购单号, rn WITH ROLLUP
--------------------------------------------------------------------
--------------------------------------------------------------------
) ttt