进销存中的复杂报表之黄金钻石区间段分析表
1.效果
2.报表简介：查询分销商下类别组对应的区间范围内数量统计。表头是根据条件动态变化的。
3.SQL
DECLARE @stime datetime,
@etime Datetime;
SET @stime='2015/11/1 0:00:00'
SET @etime='2015/12/4 23:59:59'
--------------------------------------------------------------天平均算法计算库存---------------------------------------------
--1.每个分销商、每一天 每一分数段、类别 的变化量 #temp-->
SELECT Sum(num) AS Num,
agentid AS agentid,
CateGDGroupID,
CateGDGroupName,
GoodAttrValGroup,
Wrdname,
MAX(wrid) AS wrid,
DAY AS DAY,
Max(addtime) AS AddTime INTO #temp
FROM
(SELECT *,
Dateadd(DAY, Datediff(DAY, 0, Dateadd(dd, 0, addtime)), 0) AS DAY
FROM
(SELECT rbarcode AS barcode,
orderdetail_r.quantity AS num,
fromagentid AS fromagentid,
addtime
FROM order_r
INNER JOIN orderdetail_r ON orderno = rorderno
WHERE addtime <= @etime
AND addtime > @stime
UNION ALL SELECT fbarcode AS barcode,
orderdetail_f.quantity AS num,
fagentid AS agentid,
addtime AS AddTime
FROM order_f
INNER JOIN orderdetail_f ON orderno = forderno
AND order_f.status in(3,4)
WHERE AddTime <= @etime
AND AddTime > @stime
UNION ALL SELECT fbarcode AS barcode, -orderdetail_f.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_f
INNER JOIN orderdetail_f ON orderno = forderno
AND order_f.status >= 2
WHERE addtime <= @etime
AND addtime > @stime
UNION ALL SELECT dbarcode AS barcode,
orderdetail_d.quantity AS num,
dagentid AS agentid,
addtime AS addtime
FROM order_d
INNER JOIN orderdetail_d ON orderno = dorderno
AND order_d.status IN(3,4)
WHERE AddTime <= @etime
AND AddTime > @stime
UNION ALL SELECT dbarcode AS barcode, -orderdetail_d.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_d
INNER JOIN orderdetail_d ON orderno = dorderno
AND order_d.status >= 2
WHERE addtime <= @etime
AND addtime > @stime
UNION ALL SELECT tbarcode AS barcode,
orderdetail_t.quantity AS num,
tagentid AS agentid,
addtime AS addtime
FROM order_t
INNER JOIN orderdetail_t ON orderno = torderno
AND order_t.status = 4
WHERE AddTime <= @etime
AND AddTime > @stime
UNION ALL SELECT tbarcode AS barcode, -orderdetail_t.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_t
INNER JOIN orderdetail_t ON orderno = torderno
AND order_t.status >= 2
WHERE addtime <= @etime
AND addtime > @stime
UNION ALL SELECT ktbarcode AS barcode, -orderdetail_kt.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_kt
INNER JOIN orderdetail_kt ON orderno = ktorderno
AND order_kt.status >= 2
WHERE addtime <= @etime
AND addtime > @stime
UNION ALL SELECT odbarcode AS barcode, -orderdetail_s.odquantity AS num ,
sagentid AS agentid,
addtime
FROM order_s
INNER JOIN orderdetail_s ON orderno = odorderno
WHERE addtime <= @etime
AND addtime > @stime) AS tt
INNER JOIN
(SELECT adminuserauthrelid
FROM adminuserauth
WHERE adminuserauthreltype = 4
AND adminuserid =36) AS x ON x.adminuserauthrelid = tt.fromagentid
INNER JOIN agent ON agent.agentid = tt.fromagentid
WHERE agent.agentid IN (637,
647,
654,
655,
660,
669,
670,
671,
680,
687)) AS te
LEFT JOIN good ON good.goodbarcode = te.barcode
INNER JOIN
(SELECT CateGDGroupID,
CateGDCateID,
CateGDGroupName
FROM categroupdetail
WHERE CateGDGroupID=157) AS categroupdetail ON good.GoodCateID=categroupdetail.CateGDCateID
INNER JOIN WeightRDetail ON Good.GoodGoldWeight>=WRDFrom
AND Good.GoodGoldWeight <= WRDTo
INNER JOIN GoodAttrVal ON GoodDecoName=GoodAttrVal.GoodAttrValue
WHERE WRID = 21
GROUP BY agentid,CateGDGroupID,DAY,CateGDGroupName,
WRDName,
GoodAttrValGroup
ORDER BY agentid,DAY,CateGDGroupID,
CateGDGroupName
--2.生成全集 ##temp_kc_day-->
SELECT TOP(Datediff(d, Dateadd(dd, -1, @stime), @etime)) IDENTITY(int, 1, 1) AS id INTO #date
FROM syscolumns
SELECT agent.agentid AS AgentID,
agent.agentname,
agent.agentbigarea,
WeightRDetail.WRDName,
CateGroupDetail.CateGDGroupID,
CateGroupDetail.CGName,
goodattrvalgroup,
CateGroupDetail.CGRank,
CateGroupDetail.CGType,
CONVERT(DATETIME, Dateadd(d, #date.id, Dateadd(dd, -1, @stime)), 112) AS date,
Cast(0 AS NUMERIC(12, 2)) AS stock,
Cast(0 AS NUMERIC(12, 2)) AS stocks INTO #temp_kc_day
FROM #date
LEFT JOIN
(SELECT CateGDGroupID,
categroup.CGName,
categroup.CGRank,
categroup.CGType
FROM categroupdetail
INNER JOIN CateGroup ON categroup.CateGroupID=CateGroupDetail.CateGDGroupID
WHERE CateGDGroupID=157
GROUP BY CateGDGroupID,
categroup.CGName,
categroup.CGRank,
categroup.CGType) AS categroupdetail ON 1=1
LEFT JOIN agent ON agent.agentid IN
(SELECT adminuserauthrelid
FROM adminuserauth
JOIN agent ON adminuserauth.adminuserauthrelid = agent.agentid
WHERE adminuserauthreltype = 4
AND adminuserid = 36
AND adminuserauth.adminuserauthrelid IN (637,
647,
654,
655,
660,
669,
670,
671,
680,
687))
LEFT JOIN WeightRDetail ON 1=1
AND WRID =21
LEFT JOIN
(SELECT goodattrvalgroup
FROM goodattrval AS x
WHERE 1=1
AND goodattrcode='DecoName'
AND goodattrvalgroup !=''
AND goodattrvalgroup IS NOT NULL
GROUP BY goodattrvalgroup) AS x ON 1=1 DECLARE @AddTime_FromT DATETIME;
--3.根据历史库存计算期初 ##temp_begin-->
SET @AddTime_FromT=
(SELECT Isnull(Max(addtime), '2014/6/1 0:00:00')
FROM goodshis
WHERE addtime <= @stime);
SELECT * INTO #temp_begin
FROM
(SELECT SUM (num) AS num,
t.agentid AS AgentID,
CateGDGroupID,
CateGDGroupName,
WRDName,
goodattrvalgroup
FROM
(SELECT *
FROM
(SELECT od.barcode AS Barcode, od.agentid AS agentid, SUM(num) AS num,
Max(addtime) AS addtime
FROM
(SELECT barcode,
num,
agentid,
addtime
FROM goodshis
WHERE addtime = @AddTime_FromT
UNION ALL SELECT fbarcode AS barcode,
orderdetail_f.quantity AS num,
fagentid AS agentid,
AddTime
FROM order_f
INNER JOIN orderdetail_f ON orderno = forderno
WHERE ((order_f.status = 3
AND addtime <= @AddTime_FromT)
OR (order_f.status = 4
AND addtime <= @AddTime_FromT
AND confitime > @AddTime_FromT))
UNION ALL SELECT dbarcode AS barcode,
orderdetail_d.quantity AS num,
dagentid AS agentid,
AddTime
FROM order_d
INNER JOIN orderdetail_d ON orderno = dorderno
WHERE ((order_d.status = 3
AND addtime <= @AddTime_FromT)
OR (order_d.status = 4
AND addtime <= @AddTime_FromT
AND confitime > @AddTime_FromT))
UNION ALL SELECT tbarcode AS barcode,
orderdetail_t.quantity AS num,
tagentid AS agentid,
AddTime
FROM order_t
INNER JOIN orderdetail_t ON orderno = torderno
WHERE ((order_t.status = 3
AND addtime <= @AddTime_FromT)
OR (order_t.status = 4
AND addtime <= @AddTime_FromT
AND confitime > @AddTime_FromT))
UNION ALL SELECT rbarcode AS barcode,
orderdetail_r.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_r
INNER JOIN orderdetail_r ON orderno = rorderno
WHERE addtime <= @stime
AND addtime >= @stime
UNION ALL SELECT fbarcode AS barcode,
orderdetail_f.quantity AS num,
fagentid AS agentid,
AddTime AS AddTime
FROM order_f
INNER JOIN orderdetail_f ON orderno = forderno
AND order_f.status IN(3,
4)
WHERE AddTime <= @stime
AND AddTime >= @AddTime_FromT
UNION ALL SELECT fbarcode AS barcode, -orderdetail_f.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_f
INNER JOIN orderdetail_f ON orderno = forderno
AND order_f.status >= 2
WHERE addtime <= @stime
AND addtime >= @AddTime_FromT
UNION ALL SELECT dbarcode AS barcode,
orderdetail_d.quantity AS num,
dagentid AS agentid,
addtime AS addtime
FROM order_d
INNER JOIN orderdetail_d ON orderno = dorderno
AND order_d.status in(3,4)
WHERE AddTime <= @stime
AND AddTime >= @AddTime_FromT
UNION ALL SELECT dbarcode AS barcode, -orderdetail_d.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_d
INNER JOIN orderdetail_d ON orderno = dorderno
AND order_d. status >= 2
WHERE addtime <= @stime
AND addtime >= @AddTime_FromT
UNION ALL SELECT tbarcode AS barcode,
orderdetail_t.quantity AS num,
tagentid AS agentid,
addtime AS addtime
FROM order_t
INNER JOIN orderdetail_t ON orderno = torderno
AND order_t.status in(3,4)
WHERE AddTime <= @stime
AND AddTime >= @AddTime_FromT
UNION ALL SELECT tbarcode AS barcode, -orderdetail_t.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_t
INNER JOIN orderdetail_t ON orderno = torderno
AND order_t.status >= 2
WHERE addtime <= @stime
AND addtime >= @AddTime_FromT
UNION ALL SELECT ktbarcode AS barcode, -orderdetail_kt.quantity AS num,
fromagentid AS agentid,
addtime
FROM order_kt
INNER JOIN orderdetail_kt ON orderno = ktorderno
AND order_kt.status >= 2
WHERE addtime <= @stime
AND addtime >= @AddTime_FromT
UNION ALL SELECT odbarcode AS barcode, -orderdetail_s.odquantity AS num,
sagentid AS agentid,
addtime
FROM order_s
INNER JOIN orderdetail_s ON orderno = odorderno
WHERE addtime <= @stime
AND addtime >= @AddTime_FromT) AS od
INNER JOIN
(SELECT adminuserauthrelid
FROM adminuserauth
WHERE adminuserauthreltype = 4
AND adminuserid =36) AS x ON x.adminuserauthrelid = od.agentid
INNER JOIN agent ON agent.agentid = od.agentid
WHERE agent.agentid IN (637,
647,
654,
655,
660,
669,
670,
671,
680,
687)
GROUP BY od.agentid,
od.barcode) AS gh
WHERE num != 0) AS t
INNER JOIN good ON goodbarcode = barcode
INNER JOIN
(SELECT CateGDGroupID,
CateGDCateID,
CateGDGroupName
FROM categroupdetail
WHERE CateGDGroupID=157) AS categroupdetail ON good.GoodCateID=categroupdetail.CateGDCateID
INNER JOIN WeightRDetail ON Good.GoodGoldWeight>=WRDFrom
AND Good.GoodGoldWeight <= WRDTo
AND WRID = 21
INNER JOIN GoodAttrVal ON GoodDecoName=GoodAttrVal.GoodAttrValue
WHERE 1=1
GROUP BY t.agentid,
CateGDGroupID,
CateGDGroupName,
WRDName,
goodattrvalgroup) AS t1
--4.将变动数据更新到全集
UPDATE #temp_kc_day
SET stock =Num
FROM #temp_kc_day
INNER JOIN #temp ON #temp_kc_day.agentid = #temp.agentid
AND #temp_kc_day.date = #temp.DAY
AND #temp.CateGDGroupID=#temp_kc_day.CateGDGroupID
AND #temp.WRDName = #temp_kc_day.WRDName
AND #temp.GoodAttrValGroup = #temp_kc_day.GoodAttrValGroup
--5.根据 库存=变动+起初 计算出每天的库存-->
DECLARE @i INT
SET @i=0 WHILE @i <= Datediff(dd, @stime, @etime) BEGIN
UPDATE #temp_kc_day
SET stocks = Isnull(x.stock, 0) + #temp_begin.Num
FROM #temp_kc_day
LEFT JOIN
(SELECT SUM(Isnull(stock, 0)) AS stock,
agentid,
CateGDGroupID,
WRDName,
GoodAttrValGroup
FROM #temp_kc_day
WHERE Datediff(dd, @stime, date) <= @i
GROUP BY agentid,
CateGDGroupID,
WRDName,
GoodAttrValGroup)AS x ON x.agentid = #temp_kc_day.agentid
AND x.CateGDGroupID=#temp_kc_day.CateGDGroupID
AND x.WRDName =#temp_kc_day.WRDName
AND x.GoodAttrValGroup =#temp_kc_day.GoodAttrValGroup
LEFT JOIN #temp_begin ON #temp_begin.agentid = #temp_kc_day.agentid
AND #temp_begin.CateGDGroupID=#temp_kc_day.CateGDGroupID
AND #temp_begin.WRDName=#temp_kc_day.WRDName
AND #temp_begin.GoodAttrValGroup=#temp_kc_day.GoodAttrValGroup
WHERE Datediff(dd, @stime, #temp_kc_day.date) = @i
SET @i=@i + 1 END;
--6.天平均库存数
SELECT AgentID AS goodagentid,
CateGDGroupID,
WRDName,
goodattrvalgroup,
cast(CASE count(stocks) WHEN 0 THEN cast(0.00 AS numeric(12,2)) ELSE SUM(stocks) / Count(stocks) END AS NUMERIC(12, 2)) AS goodstock INTO #teamp1
FROM #temp_kc_day
GROUP BY AgentID,
CateGDGroupID,
WRDName,
GoodAttrValGroup
------------------------------------------------天平均算法计算库存 结束----------------------------------
--7.生成 区域，分销，分数段 全集
SELECT agentbigarea,
agentname,
x.wrdid,
x.wrdname,
x.goodattrvalgroup,
isnull(goodstock,0) AS quantity INTO #END
FROM
(SELECT agentid,
agentname,
agentbigarea,
goodattrvalgroup,
weightrdetail.*
FROM agent
LEFT JOIN WeightRDetail ON 1=1
AND wrid=21
LEFT JOIN
(SELECT goodattrvalgroup
FROM goodattrval AS x
WHERE 1=1
AND goodattrcode='DecoName'
AND goodattrvalgroup !=''
AND goodattrvalgroup IS NOT NULL
GROUP BY goodattrvalgroup) AS x ON 1=1
WHERE 1=1
AND agentid IN
(SELECT AdminUserAuthRelID
FROM AdminUserAuth
WHERE AdminUserAuthRelType=4
AND AdminUserID= 36)
AND agentid IN (637,
647,
654,
655,
660,
669,
670,
671,
680,
687))AS x
LEFT JOIN #teamp1 AS y ON x.agentid=y.goodagentid
AND x.WRDName=y.WRDName
AND x.goodattrvalgroup=y.goodattrvalgroup
--8.插入区域小计
INSERT INTO #end(agentbigarea,agentname,wrdid,wrdname,goodattrvalgroup,quantity)
SELECT agentbigarea+' -小计',
'',
wrdid,
wrdname,
goodattrvalgroup,
sum(quantity)
FROM #END
GROUP BY agentbigarea,
wrdid,
wrdname,
goodattrvalgroup
--9.插入总合计
INSERT INTO #end(agentbigarea,agentname,wrdid,wrdname,goodattrvalgroup,quantity)
SELECT '总合计',
'',
wrdid,
wrdname,
goodattrvalgroup,
sum(quantity)
FROM #END
WHERE agentbigarea NOT LIKE '%小计%'
AND agentbigarea NOT LIKE '%合计%'
GROUP BY wrdid,
wrdname,
goodattrvalgroup
--插入最后列总合计 排序在最后 wrdid写入很大的数字
INSERT INTO #end(agentbigarea,agentname,wrdid,wrdname,goodattrvalgroup,quantity)
SELECT agentbigarea,
agentname,
99999,
'总合计',
'',
sum(quantity)
FROM #END
GROUP BY agentbigarea,
agentname
-----------------------------------------------------------按销售--------------------------------------------------------
SELECT wrdid,
sagentid,
wrdname ,
--无匹配到的默认成 其他
isnull(goodattrvalgroup,'其他') AS goodattrvalgroup ,
sum(odquantity) AS quantity INTO #teamp2
FROM
--取区间段配置 程序里设置一个选择框默认第一组石重配置，可由操作员自己更改石重方案
(SELECT *
FROM WeightRDetail
WHERE wrid=21)
AS x
LEFT JOIN
----如果是库存数据 将整个y替换成库存算法 (如果是周转表 需要同时计算库存和销售)
(
--取值 这里是取销售 连接重量范围表时直接取good表数据（正数）；石重按单粒计算；数量为0的补1-除数不能为0
--所有带除法的地方 除数都要做0处理
SELECT cast(GoodGoldWeight AS numeric(12,3)) AS goodctrange ,
odquantity,
order_s.sagentid ,
gooddeconame
FROM orderdetail_s
INNER JOIN order_s ON odorderno=order_s.orderno
LEFT JOIN good ON good.goodbarcode=odbarcode
INNER JOIN agent ON agentid = SAgentID
WHERE order_s.addtime>='2015/11/1 0:00:00'
AND order_s.addtime <= '2015/12/4 23:59:59'
AND agentid IN
(SELECT AdminUserAuthRelID
FROM AdminUserAuth
WHERE AdminUserAuthRelType=4
AND AdminUserID= 36)
AND SAgentID IN (637,
647,
654,
655,
660,
669,
670,
671,
680,
687)
AND goodcateid IN
(SELECT categdcateid
FROM CateGroupDetail
WHERE categdgroupid =157)) AS y
--区间配置和数据结果关联 on用范围比较
ON x.wrdfrom <= goodctrange
AND x.wrdto>=goodctrange
LEFT JOIN
(SELECT goodattrvalue,
goodattrvalgroup
FROM goodattrval AS x
WHERE 1=1
AND goodattrcode='DecoName'
AND goodattrvalgroup !=''
AND goodattrvalgroup IS NOT NULL) AS z ON y.gooddeconame=z.goodattrvalue
--分组条件 分销商代码，范围名，范围id 范围名可以忽略 传id即可
GROUP BY sagentid,
wrdname,
wrdid,
isnull(goodattrvalgroup,'其他')
ORDER BY wrdid
--生成结果全集集合；报表展示的界面是完整的 但是数据可能有null的情况 必须取一个完整集合;
SELECT agentbigarea,
agentname,
x.wrdid,
x.wrdname,
x.goodattrvalgroup,
isnull(quantity,0) AS quantity INTO #end1
FROM
(SELECT agentid,
agentname,
agentbigarea,
goodattrvalgroup,
weightrdetail.*
FROM agent
LEFT JOIN WeightRDetail ON 1=1
AND wrid=21
LEFT JOIN
(SELECT goodattrvalgroup
FROM goodattrval AS x
WHERE 1=1
AND goodattrcode='DecoName'
AND goodattrvalgroup !=''
AND goodattrvalgroup IS NOT NULL
GROUP BY goodattrvalgroup) AS x ON 1=1
WHERE 1=1
AND agentid IN
(SELECT AdminUserAuthRelID
FROM AdminUserAuth
WHERE AdminUserAuthRelType=4
AND AdminUserID= 36)
AND agentid IN (637,
647,
654,
655,
660,
669,
670,
671,
680,
687))AS x
LEFT JOIN #teamp2 AS y ON x.agentid=y.sagentid
AND x.wrdid=y.wrdid
AND x.goodattrvalgroup=y.goodattrvalgroup
--插入区域小计
INSERT INTO #end1(agentbigarea,agentname,wrdid,wrdname,goodattrvalgroup,quantity)
SELECT agentbigarea+' -小计',
'',
wrdid,
wrdname,
goodattrvalgroup,
sum(quantity)
FROM #end1
GROUP BY agentbigarea,
wrdid,
wrdname,
goodattrvalgroup
--插入总合计
INSERT INTO #end1(agentbigarea,agentname,wrdid,wrdname,goodattrvalgroup,quantity)
SELECT '总合计',
'',
wrdid,
wrdname,
goodattrvalgroup,
sum(quantity)
FROM #end1
WHERE agentbigarea NOT LIKE '%小计%'
AND agentbigarea NOT LIKE '%合计%'
GROUP BY wrdid,
wrdname,
goodattrvalgroup
--插入最后列总合计 排序在最后 wrdid写入很大的数字
INSERT INTO #end1(agentbigarea,agentname,wrdid,wrdname,goodattrvalgroup,quantity)
SELECT agentbigarea,
agentname,
99999,
'总合计',
'',
sum(quantity)
FROM #end1
GROUP BY agentbigarea,
agentname
--合并销售和库存数据 #tempEnd
SELECT e.*,
r.quantity AS quantity1,
e.quantity/ (CASE r.quantity
WHEN 0 THEN 1
WHEN NULL THEN 1
ELSE r.quantity
END) AS quantity2 INTO #tempEnd
FROM #end1 e
LEFT JOIN #END r ON e.AgentName=r.AgentName
AND e.WRDID=r.WRDID
AND e.GoodAttrValGroup= r.GoodAttrValGroup and e.AgentBigArea=r.AgentBigArea
--动态行转列
DECLARE @subSql nvarchar(MAX)
SET @subSql = 'select Row_Number() over(order by agentbigarea,agentname) as RowNumber ,
AgentBigArea as AgentBigArea,AgentName'
SELECT @subSql = @subSql + ',SUM(case wrdname+goodattrvalgroup when ''' + wrdname+goodattrvalgroup+ ''' then
quantity else 0 end) as [' + wrdname+'|'+goodattrvalgroup + '|销售] ,SUM(case wrdname+goodattrvalgroup
when ''' + wrdname+goodattrvalgroup+ ''' then quantity1 else 0 end) as [' + wrdname+'|'+goodattrvalgroup + '|库存]
,SUM(case wrdname+goodattrvalgroup when ''' + wrdname+goodattrvalgroup+ ''' then quantity2 else 0 end) as
[' + wrdname+'|'+goodattrvalgroup + '|周转]'
FROM
(SELECT TOP 10000 wrdname,
goodattrvalgroup
FROM #tempEnd
GROUP BY goodattrvalgroup,
wrdname
ORDER BY goodattrvalgroup,
Max(wrdid)) AS a
SET @subSql = @subSql + ' from #tempEnd group by AgentBigArea,agentname' print @subSql EXEC (@subSql)
--select * from #tempEnd
--select * from #end1
--select * from #END
3.技术点：天平均库存算法，动态行转列等
