进销存中的复杂报表（一）
库存指标报表：
概述：分销商按（天平均，实时库存，历史库存）查询库存，展示按照设置的类别组sum求和的结果
SQL：
DECLARE @tcid INT;
DECLARE @stime DATETIME,
@etime DATETIME;
SET @stime='2015/6/1 0:00:00';
SET @etime='2015/7/9 23:59:59'
SELECT @tcid = tcid
FROM tagconfig
WHERE tcbegintime < @stime
AND tcendtime > @stime
AND tctype = 1
AND status = 2
--AND tcstatus = 1
SELECT agentbigarea,
agentid,
cgtype,
agentname,
cgname,
CASE
WHEN tcdgoodcount != 0 THEN tcdgoodcount
WHEN tcdgoodmoney != 0 THEN tcdgoodmoney
WHEN tcdgoodweight != 0 THEN tcdgoodweight
ELSE '0'
END AS zhibiao
INTO #teamp
FROM tagconfig
INNER JOIN tagconfigdetail
ON tcdtcid = tcid
INNER JOIN agent
ON tcdagentid = agentid
INNER JOIN categroup
ON tcdcateid = categroupid
WHERE tcid = @tcid
ORDER BY agentname
SELECT cgname,
cgrank,
cgtype
INTO #teamp4
FROM categroup
WHERE cgcode = '库存指标组'
AND cgparentid != 0
ORDER BY cgrank ASC
DECLARE @count INT;
DECLARE @number INT;
DECLARE @GroupName NVARCHAR(50);
DECLARE @DSql NVARCHAR(4000)='';
--DECLARE @DSql1 NVARCHAR(1000)='';
--DECLARE @DSql2 NVARCHAR(1000)='';
--DECLARE @DSql3 NVARCHAR(1000)='';
DECLARE @DSql4 NVARCHAR(4000)='';
--DECLARE @DSql5 NVARCHAR(1000)='';
--DECLARE @DSql6 NVARCHAR(1000)='';
--DECLARE @DSql7 NVARCHAR(1000)='';
DECLARE @lastname NVARCHAR(20);
DECLARE @cgtype INT;
DECLARE @colum1 NVARCHAR(20);
SELECT @count = Count(*)
FROM #teamp4;
SET @number=0;
print(@count);
WHILE @number < @count
BEGIN
SELECT @number = @number + 1;
SELECT @GroupName = hh.cgname,
@cgtype = cgtype
FROM (SELECT Row_number()
OVER (
ORDER BY cgrank ) 'rowindex',
*
FROM #teamp4) hh
WHERE hh.rowindex = @number
IF( @cgtype = 1 )
BEGIN
SET @lastname ='（克）';
SET @colum1 = 'GoodGoldWeight';
END
IF( @cgtype = 2 )
BEGIN
SET @lastname ='（件）';
SET @colum1 = 'Goodcount';
END
IF( @cgtype = 3 )
BEGIN
SET @lastname ='（万元）';
SET @colum1 = 'goodsaleprice';
END
IF( @number = @count )
BEGIN
SET @DSql = @DSql + 'Sum( case cgname when '''
+ @GroupName + ''' then ' + @colum1
+ ' else 0 end) as ''' + @GroupName + '库存'
+ @lastname + ''','
SET @DSql4 = @DSql4 + 'Sum([' + @GroupName + '库存' + @lastname
+ ']) as ''' + @GroupName + '库存' + @lastname
+ ''','
SET @DSql = @DSql + 'Sum( case cgname when '''
+ @GroupName
+ ''' then zhibiao else 0 end) as '''
+ @GroupName + '指标' + @lastname + ''','
SET @DSql4 = @DSql4 + 'Sum([' + @GroupName + '指标' + @lastname
+ ']) as ''' + @GroupName + '指标' + @lastname
+ ''','
SET @DSql = @DSql + 'Sum( case cgname when '''
+ @GroupName + ''' then ' + @colum1
+ '-zhibiao else 0 end) as ''' + @GroupName
+ '差值'','
SET @DSql4 = @DSql4 + 'Sum([' + @GroupName + '差值]) as '''
+ @GroupName + '差值'','
SET @DSql = @DSql + 'Sum( case cgname when '''
+ @GroupName
+ ''' then case zhibiao when 0 then null else (('
+ @colum1
+ '-zhibiao)/zhibiao)*100 end else 0 end) as '''
+ @GroupName + '差值比'''
SET @DSql4 = @DSql4 + ' Case Sum([' + @GroupName + '指标'
+ @lastname
+ ']) when 0 then null else (Sum(['
+ @GroupName + '库存' + @lastname + '])-Sum(['
+ @GroupName + '指标' + @lastname + ']))/Sum(['
+ @GroupName + '指标' + @lastname
+ ']) end as ''' + @GroupName + '差值比'''
END
ELSE
BEGIN
SET @DSql = @DSql + 'Sum( case cgname when '''
+ @GroupName + ''' then ' + @colum1
+ ' else 0 end) as ''' + @GroupName + '库存'
+ @lastname + ''','
SET @DSql4 = @DSql4 + 'Sum([' + @GroupName + '库存' + @lastname
+ ']) as ''' + @GroupName + '库存' + @lastname
+ ''','
SET @DSql = @DSql + 'Sum( case cgname when '''
+ @GroupName
+ ''' then zhibiao else 0 end) as '''
+ @GroupName + '指标' + @lastname + ''','
SET @DSql4 = @DSql4 + 'Sum([' + @GroupName + '指标' + @lastname
+ ']) as ''' + @GroupName + '指标' + @lastname
+ ''','
SET @DSql = @DSql + 'Sum( case cgname when '''
+ @GroupName + ''' then ' + @colum1
+ '-zhibiao else 0 end) as ''' + @GroupName
+ '差值'','
SET @DSql4 = @DSql4 + 'Sum([' + @GroupName + '差值]) as '''
+ @GroupName + '差值'','
SET @DSql = @DSql + 'Sum( case cgname when '''
+ @GroupName
+ ''' then case zhibiao when 0 then null else (('
+ @colum1
+ '-zhibiao)/zhibiao)*100 end else 0 end) as'''
+ @GroupName + '差值比'','
SET @DSql4 = @DSql4 + ' Case Sum([' + @GroupName + '指标'
+ @lastname
+ ']) when 0 then null else (Sum(['
+ @GroupName + '库存' + @lastname + '])-Sum(['
+ @GroupName + '指标' + @lastname + ']))/Sum(['
+ @GroupName + '指标' + @lastname
+ ']) end as ''' + @GroupName + '差值比'','
END
END
------------------------------------------------------------------使用天平均库存计算 开始-------------------------------------
--第一步： 生成一个全集存入#Teamp_0
SELECT TOP(Datediff(d, Dateadd(dd, -2, '2015-06-01 00:00:00'),'2015-06-30 00:00:00')) IDENTITY(int, 1, 1) AS id
INTO #date
FROM syscolumns
--select * from #date
SELECT DISTINCT tcdagentid AS AgentID,
categdgroupname AS CGName,
Dateadd(d, -Day(CONVERT(DATETIME,
Dateadd(d, #date.id,
Dateadd(dd, -2,
'2015-06-01 00:00:00'
)),
112)) + 1, CONVERT(DATETIME,
Dateadd(d, #date.id,
Dateadd(dd, -2, '2015-06-01 00:00:00'
)),
112)) AS month,
CONVERT(DATETIME, Dateadd(d, #date.id,
Dateadd(dd, -2, '2015-06-01 00:00:00')),
112) AS date,
Cast(0 AS NUMERIC(12, 2)) AS GoodSalePrice,
Cast(0 AS NUMERIC(12, 2)) AS GoodSalePrices,
Cast(0 AS NUMERIC(12, 3)) AS GoodGoldWeight,
Cast(0 AS NUMERIC(12, 3)) AS GoodGoldWeights,
Cast(0 AS NUMERIC(12, 2)) AS num,
Cast(0 AS NUMERIC(12, 2)) AS nums
INTO #teamp_0
FROM #date
--LEFT JOIN agent
--on agent.AgentID in (627,653)
LEFT JOIN categroupdetail
ON categdgroupcode = '库存指标组'
LEFT JOIN tagconfigdetail
ON tcdcateid = categdgroupid
--select * from
---- select * from CateGroup
--SELECT * from #teamp_0
--FROM tagconfigdetail
-- select * from CateGroupDetail
--第二步：计算每天的变化量存入#teamp_1
SELECT *
INTO #teamp_1
FROM (SELECT Row_number()
OVER(
ORDER BY t.agentid ) AS RowNumber,
categdgroupname,
Sum (num) AS num,
Sum(goodsaleprice * num) AS GoodSalePrice,
Sum(goodgoldweight * num) AS GoodGoldWeight,
years,
Max(addtime) AS AddTime,
Max(t.agentid) AS agentid
FROM (SELECT *
FROM (SELECT barcode,
agentid,
Sum(saleprice) AS
saleprice,
Sum(num) AS num,
Max(addtime) AS
addtime,
Cast(Max(Year(addtime)) AS VARCHAR(10))
+ '-'
+ Cast(Max(Month(addtime)) AS VARCHAR(10))
+ '-'
+ Cast(Max(Day(addtime)) AS VARCHAR(10)) AS Years
FROM (SELECT rbarcode AS barcode,
orderdetail_r.quantity AS num,
saleprice,
fromagentid AS agentid,
addtime
FROM order_r
INNER JOIN orderdetail_r
ON orderno = rorderno
WHERE addtime <= '2015-06-30 23:59:59'
AND addtime > '2015-06-01 00:00:00'
-- AND
--fromagentid IN (627)
--627,636,638,640,642,653,658,659,663
UNION ALL
SELECT fbarcode AS barcode,
orderdetail_f.quantity AS num,
saleprice,
fagentid AS agentid,
confitime AS AddTime
FROM order_f
INNER JOIN orderdetail_f
ON orderno = forderno
AND order_f.status = 4
WHERE confitime <= '2015-06-30 23:59:59'
AND confitime > '2015-06-01 00:00:00'
--AND fagentid IN (627)
UNION ALL
SELECT fbarcode AS barcode,
-orderdetail_f.quantity AS num,
-saleprice,
fromagentid AS agentid,
addtime
FROM order_f
INNER JOIN orderdetail_f
ON orderno = forderno
AND order_f.status >= 2
WHERE addtime <= '2015-06-30 23:59:59'
AND addtime > '2015-06-01 00:00:00'
--AND fromagentid IN (627)
UNION ALL
SELECT dbarcode AS barcode,
orderdetail_d.quantity AS num,
saleprice,
dagentid AS agentid,
confitime AS addtime
FROM order_d
INNER JOIN orderdetail_d
ON orderno = dorderno
AND order_d.status = 4
WHERE confitime <= '2015-06-30 23:59:59'
AND confitime > '2015-06-01 00:00:00'
--AND dagentid IN (627)
UNION ALL
SELECT dbarcode AS barcode,
-orderdetail_d.quantity AS num,
-saleprice,
fromagentid AS agentid,
addtime
FROM order_d
INNER JOIN orderdetail_d
ON orderno = dorderno
AND order_d.status >= 2
WHERE addtime <= '2015-06-30 23:59:59'
AND addtime > '2015-06-01 00:00:00'
--AND fromagentid IN (627)
UNION ALL
SELECT tbarcode AS barcode,
orderdetail_t.quantity AS num,
saleprice,
tagentid AS agentid,
confitime AS addtime
FROM order_t
INNER JOIN orderdetail_t
ON orderno = torderno
AND order_t.status = 4
WHERE confitime <= '2015-06-30 23:59:59'
AND confitime > '2015-06-01 00:00:00'
--AND tagentid IN (627)
UNION ALL
SELECT tbarcode AS barcode,
-orderdetail_t.quantity AS num,
-saleprice,
fromagentid AS agentid,
addtime
FROM order_t
INNER JOIN orderdetail_t
ON orderno = torderno
AND order_t.status >= 2
WHERE addtime <= '2015-06-30 23:59:59'
AND addtime > '2015-06-01 00:00:00'
--AND fromagentid IN (627)
UNION ALL
SELECT ktbarcode AS barcode,
-orderdetail_kt.quantity AS num,
-saleprice,
fromagentid AS agentid,
addtime
FROM order_kt
INNER JOIN orderdetail_kt
ON orderno = ktorderno
AND order_kt.status >= 2
WHERE addtime <= '2015-06-30 23:59:59'
AND addtime > '2015-06-01 00:00:00'
--AND fromagentid IN (627)
UNION ALL
SELECT odbarcode AS barcode,
-orderdetail_s.odquantity AS num,
-odsaleprice AS SalePrice,
sagentid AS agentid,
addtime
FROM order_s
INNER JOIN orderdetail_s
ON orderno = odorderno
WHERE addtime <= '2015-06-30 23:59:59'
AND addtime > '2015-06-01 00:00:00'
--AND sagentid IN (627)
) AS od
GROUP BY agentid,
barcode,
addtime) AS gh
WHERE num != 0
AND agentid IN (SELECT adminuserauthrelid
FROM adminuserauth
WHERE adminuserauthreltype = 4
AND adminuserid = 36)) AS t
INNER JOIN good
ON goodbarcode = barcode
INNER JOIN categroupdetail
ON categdcateid = goodcateid
AND categdgroupcode = '库存指标组'
WHERE 1 = 1
GROUP BY t.agentid,
categdgroupname,
t.years) AS t1
WHERE 1 = 1
ORDER BY agentid,
addtime
--第三步:将天变动统计数据插入临时结果集合里面,这里是用了2个字段存放 一个是差异数一个是合计数;合计数在后面计算
UPDATE #teamp_0
SET goodsaleprice = #teamp_1.goodsaleprice,
goodgoldweight = #teamp_1.goodgoldweight,
num = #teamp_1.num
FROM #teamp_0
LEFT JOIN #teamp_1
ON #teamp_0.agentid = #teamp_1.agentid
AND #teamp_0.date = #teamp_1.years
AND #teamp_0.cgname = #teamp_1.categdgroupname
-- 第四步 : 根据历史库存计算期初 存入#Teamp_2
DECLARE @AddTime_From DATETIME;
SET @AddTime_From=(SELECT Isnull(Max(addtime), '2014-05-31 23:59:59:999')
FROM goodshis
WHERE addtime < @stime);
SELECT goodagentid,
categdgroupname,
Sum(num) AS goodcount,
Sum(goodsaleprice * num) AS goodsaleprice,
Sum(goodgoldweight * num) AS GoodGoldWeight
INTO #teamp_2
FROM (SELECT *
FROM (SELECT barcode,
agentid,
Sum(saleprice) AS saleprice,
Sum(num) AS num
FROM (SELECT barcode,
num,
saleprice,
agentid
FROM goodshis
WHERE addtime = @AddTime_From
UNION ALL
SELECT rbarcode AS barcode,
orderdetail_r.quantity AS num,
saleprice,
fromagentid AS agentid
FROM order_r
INNER JOIN orderdetail_r
ON orderno = rorderno
WHERE addtime <= @stime
AND addtime > @AddTime_From
UNION ALL
SELECT fbarcode AS barcode,
orderdetail_f.quantity AS num,
saleprice,
fagentid AS agentid
FROM order_f
INNER JOIN orderdetail_f
ON orderno = forderno
AND order_f.status = 4
WHERE confitime <= @stime
AND confitime > @AddTime_From
UNION ALL
SELECT fbarcode AS barcode,
-orderdetail_f.quantity AS num,
-saleprice,
fromagentid AS agentid
FROM order_f
INNER JOIN orderdetail_f
ON orderno = forderno
AND order_f.status >= 2
WHERE addtime <= @stime
AND addtime > @AddTime_From
UNION ALL
SELECT dbarcode AS barcode,
orderdetail_d.quantity AS num,
saleprice,
dagentid AS agentid
FROM order_d
INNER JOIN orderdetail_d
ON orderno = dorderno
AND order_d.status = 4
WHERE confitime <= @stime
AND confitime > @AddTime_From
UNION ALL
SELECT dbarcode AS barcode,
-orderdetail_d.quantity AS num,
-saleprice,
fromagentid AS agentid
FROM order_d
INNER JOIN orderdetail_d
ON orderno = dorderno
AND order_d.status >= 2
WHERE addtime <= @stime
AND addtime > @AddTime_From
UNION ALL
SELECT tbarcode AS barcode,
orderdetail_t.quantity AS num,
saleprice,
tagentid AS agentid
FROM order_t
INNER JOIN orderdetail_t
ON orderno = torderno
AND order_t.status = 4
WHERE confitime <= @stime
AND confitime > @AddTime_From
UNION ALL
SELECT tbarcode AS barcode,
-orderdetail_t.quantity AS num,
-saleprice,
fromagentid AS agentid
FROM order_t
INNER JOIN orderdetail_t
ON orderno = torderno
AND order_t.status >= 2
WHERE addtime <= @stime
AND addtime > @AddTime_From
UNION ALL
SELECT ktbarcode AS barcode,
-orderdetail_kt.quantity AS num,
-saleprice,
fromagentid AS agentid
FROM order_kt
INNER JOIN orderdetail_kt
ON orderno = ktorderno
AND order_kt.status >= 2
WHERE addtime <= @stime
AND addtime > @AddTime_From
UNION ALL
SELECT odbarcode AS barcode,
-orderdetail_s.odquantity AS num,
-odsaleprice AS SalePrice,
sagentid AS agentid
FROM order_s
INNER JOIN orderdetail_s
ON orderno = odorderno
WHERE addtime <= @stime
AND addtime > @AddTime_From) AS od
GROUP BY agentid,
barcode) AS gh
WHERE num != 0
AND agentid IN (SELECT adminuserauthrelid
FROM adminuserauth
WHERE adminuserauthreltype = 4
AND adminuserid = 36)) AS t
INNER JOIN good
ON goodbarcode = barcode
LEFT JOIN agent AS A2
ON A2.agentid = t.agentid
INNER JOIN categroupdetail
ON goodcateid = categdcateid
WHERE num != 0
AND categdgroupcode = '库存指标组'
GROUP BY goodagentid,
categdgroupname
-- 第五步: 循环计算合计数据
DECLARE @i INT
SET @i=0
WHILE @i <= Datediff(dd, '2015-06-01 00:00:00', '2015-06-30 00:00:00')
BEGIN
UPDATE #teamp_0
SET goodgoldweights = Isnull(x.goodgoldweight, 0),
goodsaleprices = Isnull(x.goodsaleprice, 0),
nums = Isnull(x.num, 0)
FROM #teamp_0
LEFT JOIN (SELECT Sum(Isnull(goodgoldweight, 0)) AS GoodGoldWeight,
Sum(Isnull(goodsaleprice, 0)) AS GoodSalePrice,
Sum(Isnull(num, 0)) AS num,
agentid,
cgname
--Sum(Isnull(quan, 0)) AS quan,
--customer
FROM #teamp_0
WHERE Datediff(dd, '2015-06-01 00:00:00', date) <= @i
GROUP BY agentid,
cgname)AS x
ON x.agentid = #teamp_0.agentid
AND x.cgname = #teamp_0.cgname
WHERE Datediff(dd, '2015-06-01 00:00:00', #teamp_0.date) = @i
SET @i=@i + 1
END;
--第六步 : 期初加上变动得到总数 存入 #Teamp_3
SELECT agentid AS goodagentid,
cgname AS CateGDGroupName,
( goodcount + nums ) AS goodcount,
( goodgoldweights + t1.goodgoldweight ) AS GoodGoldWeight,
( t1.goodsaleprice + goodsaleprices ) AS goodsaleprice
INTO #teamp_3
FROM #teamp_2 t1
RIGHT JOIN #teamp_0 t2
ON t1.goodagentid = t2.agentid
AND cgname = categdgroupname
--第七步：计算平均库存 存入 #teamp1
SELECT goodagentid,
categdgroupname,
Sum(Isnull(goodcount, 0)) / Count(*) AS goodcount,
Sum(Isnull(goodgoldweight, 0)) / Count(*) AS GoodGoldWeight,
Sum(Isnull(goodsaleprice, 0)) / Count(*) AS goodsaleprice
INTO #teamp1
FROM #teamp_3
GROUP BY goodagentid,
categdgroupname
-- select * from #Teamp_0
--select * from #Teamp_2 where GoodAgentID =627 and CateGDGroupName='K金'
--drop table #date
--drop table #Teamp_1
--drop table #Teamp_2
--drop table #Teamp_3
--drop table #Teamp_0
------------------------------------------------------------------使用天平均库存计算 结束-------------------------------------
SELECT *
INTO #teamp2
FROM #teamp t1
LEFT JOIN #teamp1 t2
ON t1.agentid = t2.goodagentid
AND t1.cgname = t2.categdgroupname
ORDER BY agentid,
cgname
DECLARE @LSql NVARCHAR(4000);
DECLARE @LSql1 NVARCHAR(4000);
SET @LSql = 'select MAX(AgentBigArea) as AgentBigArea,AgentName,'
+ @DSql + ',sort=0 from #teamp2 group by AgentName';
SET @LSql1 = 'select AgentBigArea,AgentName=''小计'','
+ @DSql4 +',sort=1 from (' + @LSql
+ ') as k group by AgentBigArea'
print(@DSql)
print(@DSql4)
print(@LSql)
print(@LSql1)
EXEC(@LSql+' union all '+ @LSql1+' ORDER BY agentbigarea,sort');
--DROP TABLE #teamp
--DROP TABLE #teamp1
--DROP TABLE #teamp2
--DROP TABLE #teamp4
-- drop table #date
-- drop table #Teamp_1
-- drop table #Teamp_2
-- drop table #Teamp_3
-- drop table #Teamp_0
--select * from #teamp order by AgentBigArea
--select * from #teamp1 order by goodagentid
--select * from #teamp2 order by AgentBigArea
--select * from #teamp4
-- select * from #Teamp_0
--select * from #Teamp_2 where GoodAgentID =627 and CateGDGroupName='K金'
