select ProductGUID,ProductName,ProjectGUID from dbo.Product
/*
F637A079-E22B-4E50-87E9-000147B1B1F4
产品5 4CB304EF-2135-43E7-90D6-B03B75CB491B C731BDB9-1436-4A23-A4FA-001097DF8218
产品4 471C3F21-B725-497C-9383-6ED7C97756D8 4CF14E4B-495F-4344-801F-001D4C731494
产品1 0BF32124-1963-4A5A-920C-036B2A0A2186 71344D5D-9994-4DC7-ABF6-00546C11565C
产品3 A7E60BCF-1FA0-4D00-AE36-50C61074119F F182ED1B-DACB-43A0-958B-005C5174429F
产品3 645E2033-9CE8-44E3-89E8-F7638E6108CB 76C87E67-E3DE-4D0D-8256-005DADE4118D
产品5 F85302F9-31A4-447B-A0CE-9F5586B5AB4C 76F4E739-9EE5-4570-B93E-00620E6954A2
产品2 4815811E-9F9C-46B1-AA46-8A98A967F233 494A2EBD-D414-4470-A9A8-006346620755
产品5 CD6CA57C-6398-4C4B-91A4-9C5A22C0991D D8EC93CE-D3E6-48CE-B276-006D7FE1F9FC
产品3 94B1FF27-49F7-4196-BA89-88AF71F27C49 C2EB45AB-5C0D-43E1-82F4-00795920FF1D
产品4 BCBD7C06-D963-44B6-AF79-550831B1CE53 5BB124D2-6748-4514-992D-009AD0C01DD6
产品1 EED22E55-FB42-42AE-9919-8D13BCF47506 29C1840B-E73F-4BF4-BE62-00E8A3EB8D95
产品5 83E68000-94B8-42DD-BE71-BC3AF1A11677
*/
select ProjectGUID,ProjectName from dbo.Project
/*
F1C24DA5-072A-40E4-8451-0081FF5B0678
项目286 2158A228-F248-46AA-98FB-008F84A183A4
项目187 582B7C29-D7BA-4FA3-BE26-01AE3973C2AF
项目122 D8F179DF-9844-4CF8-AFAA-01C64296A14E
项目95 8EE3A6A8-D73F-45C5-B09F-024214D20043
项目62 AD86DBC2-AA91-4940-9C27-02B5734C3EC6
项目367 2468F381-C1F9-4E0B-B0DF-032063BCAAC4
项目70 0BF32124-1963-4A5A-920C-036B2A0A2186
项目336 1E75C1E0-2829-43C8-9031-04624215C738
项目377 21B31D71-0678-4229-80A8-0539023F9F6F
项目4 15ED25B3-103A-415E-AF91-058BE0AE0600
项目8 EE4F7336-9629-425B-ABCF-0662F01EEECB
项目470 9374A1E0-54F8-49C7-A02C-0675F46E153D
项目63 1CE94186-510F-4B6B-BE4B-06C9362951EF
项目137 FC1DDDAB-3B92-424A-856C-082DEB91A1B3
项目9 3F1AA58F-28A3-402F-9CE7-086C26998C17
项目210 08601BA9-767F-4F92-941F-08C9EBFCA3A4
项目466
*/
--SaleNum: 套数（INT）
--SaleArea:面积（MONEY）
--SalePrice:单价（MONEY）
--SaleAmount:销售中加（MONEY）
select SaleDtlGUID,ProductGUID,YearMonth,SaleNum,SaleArea,SalePrice,SaleAmount,[Year],[Month] from dbo.SaleDtl
/*
D9023E32-D981-4DB1-82EA-A8296FBF6A8A
F637A079-E22B-4E50-87E9-000147B1B1F4 2013-04 7 700.0000 6000.0000 4200000.0000 2013 04 BEDF4CED-0C39-45EA-97BF-7C298C1D8A5B
F637A079-E22B-4E50-87E9-000147B1B1F4 2008-11 9 900.0000 5000.0000 4500000.0000 2008 11 63DE92BA-4C08-4FF1-9A68-27CD4293084A
F637A079-E22B-4E50-87E9-000147B1B1F4 2014-08 12 1200.0000 7000.0000 8400000.0000 2014 08 BC874228-B13F-4E49-9560-3CD39FF2B8A9
F637A079-E22B-4E50-87E9-000147B1B1F4 2011-05 12 12000.0000 7000.0000 84000000.0000 2011 05 164FA80F-3ECF-4E82-86A1-A7F832867028
F637A079-E22B-4E50-87E9-000147B1B1F4 2011-04 7 700.0000 6000.0000 4200000.0000 2011 04 7EC32C1E-F34B-4BF1-9BA4-5205C2C322F0
F637A079-E22B-4E50-87E9-000147B1B1F4 2010-05 12 12000.0000 7000.0000 84000000.0000 2010 05 278D72A4-F340-4DB9-8083-7BD277EB7C9E
F637A079-E22B-4E50-87E9-000147B1B1F4 2013-11 9 900.0000 5000.0000 4500000.0000 2013 11 E70EF3D1-A92C-45C1-A7D3-110A57C93CF0
F637A079-E22B-4E50-87E9-000147B1B1F4 2015-08 12 1200.0000 7000.0000 8400000.0000 2015 08 26D1A828-26EE-4B9E-BEEF-A6F0A74CDE9E
F637A079-E22B-4E50-87E9-000147B1B1F4 2007-02 8 800.0000 3500.0000 2800000.0000 2007 02 FD59AF4A-447D-4860-B2C2-10B0FD8C7531
F637A079-E22B-4E50-87E9-000147B1B1F4 2006-03 5 500.0000 5000.0000 2500000.0000 2006 03 9288807A-C53A-44FA-B9AF-A69E0BF1C4CE
F637A079-E22B-4E50-87E9-000147B1B1F4 2006-02 8 800.0000 3500.0000 2800000.0000 2006 02 CF001CD9-BC4D-4FB8-B39E-0FE41D2468A8
F637A079-E22B-4E50-87E9-000147B1B1F4 2011-08 12 1200.0000 7000.0000 8400000.0000 2011 08 608070DC-53F0-46E0-B5F8-102106786073
F637A079-E22B-4E50-87E9-000147B1B1F4 2012-04 7 700.0000 6000.0000 4200000.0000 2012 04 9869C12C-0896-4C17-B575-3A2C52CBCE13
F637A079-E22B-4E50-87E9-000147B1B1F4 2014-10 3 300.0000 5000.0000 1500000.0000 2014 10 705F790B-23F3-4C9D-A9DA-A4B5799D1D56
F637A079-E22B-4E50-87E9-000147B1B1F4 2007-01 10 1000.0000 4000.0000 4000000.0000 2007 01 050DA0EE-8862-4F0F-92FE-A521208F09C3
F637A079-E22B-4E50-87E9-000147B1B1F4 2009-12 6 600.0000 5000.0000 3000000.0000 2009 12 6E0903D3-204E-4E3E-A737-39FCF7B434E8
F637A079-E22B-4E50-87E9-000147B1B1F4 2010-08 12 1200.0000 7000.0000 8400000.0000 2010 08 22A0FC81-77C1-4B37-9DA8-24166AF7A490
F637A079-E22B-4E50-87E9-000147B1B1F4 2011-07 8 800.0000 8000.0000 6400000.0000 2011 07 DDBDE42E-4986-44AC-BE77-8EC381356820
F637A079-E22B-4E50-87E9-000147B1B1F4 2014-11 9 900.0000 5000.0000 4500000.0000 2014 11 6A7B23A3-C48B-4519-986D-3901CCFA50AE
F637A079-E22B-4E50-87E9-000147B1B1F4 2010-04 7 700.0000 6000.0000 4200000.0000 2010 04 1BA93EC8-CB89-4DA5-B27C-239A43099214
F637A079-E22B-4E50-87E9-000147B1B1F4 2009-04 7 700.0000 6000.0000 4200000.0000 2009 04
*/
/*
临时表说明#product：用项目过滤后，将“合计”作为一个产品的集合#TempAllSaleDtl：通过项目过滤后的销售明细，所有月的#ProductSaleArea：各个产品的总面积，用于计算比例#TempSaleDtl：通过日期过滤，且加工过后的销售明细，包括增加累积列，以前年度、以后年度、项目合计的记录#tempSaleDtl2：列转行后的数据集#tempSaleDtl3：行转列后的数据集
*/
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
--查找该项目的所有产品放进临时表#product，这里将“合计”作为一个产品的集合也插入产品表#product：
select ProductGUID,ProductName,ProjectGUID,ProductCode into #product
from(select ProductGUID,ProductName,ProjectGUID,ProductName as ProductCode from Product
where ProjectGUID=@ProjectGUID union all select '00000000-0000-0000-0000-000000000000','合计',@ProjectGUID,'00' as ProductCode) a
GO
--查找该项目的所有产品的销售明细放进临时表#TempAllSaleDtl，以作备用：
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice INTO #TempAllSaleDtl FROM dbo.SaleDtl
WHERE ProductGUID IN (SELECT ProductGUID FROM dbo.Product WHERE ProjectGUID=@ProjectGUID)
go
--根据#TempAllSaleDtl现有数据统计，向#TempAllSaleDtl添加总合计记
--根据现有数据统计，向#TempAllSaleDtl添加总合计记录
insert into #TempAllSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice)
select '00000000-0000-0000-0000-000000000000',YearMonth,SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea)from #TempAllSaleDtl group by YearMonth
go
--从临时表#TempAllSaleDtl 中查找指定年的销售明细放进临时表#TempSaleDtl ，注意 这个时候就已经包含了 “合计”产品00的数据：
--查找某年的销售明细：#TempSaleDtl
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,SalePrice AS ljSaleArea,SalePrice AS blSaleArea,SalePrice AS ljSaleAmount
INTO #TempSaleDtl FROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Year
go
--从临时表#TempAllSaleDtl 中查找指定年的销售明细放进临时表#TempSaleDtl ，注意 这个时候就已经包含了 “合计”产品00的数据：
--查找某年的销售明细：#TempSaleDtl
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,SalePrice AS ljSaleArea,SalePrice AS blSaleArea,SalePrice AS ljSaleAmount INTO #TempSaleDtl
FROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Year
go
--从#TempAllSaleDtl 中统计项目各个产品的总销售面积放入表：#ProductSaleArea，主要用作计算 项目累计销售面积比例
--获取项目各个产品的总销售面积：#ProductSaleArea
SELECT ProductGUID,SUM(SaleArea) AS all_SaleArea INTO #ProductSaleArea FROM #TempAllSaleDtl GROUP BY ProductGUID
go
--从表#TempSaleDtl 统计当前年度合计列，各产品的所有面积、金额、均价总合计 放入表#TempSaleDtl，注意这里 SUM(SaleAmount)/SUM(SaleArea) 计算销售单价：
--添加2011合计列的记录(本年度的各产品的所有面积、金额、均价总合计)
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)
select ProductGUID,@Year+'-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0from #TempSaleDtl group by ProductGUID
GO
--从表#TempAllSaleDtl 统计以前年度的各产品的所有面积、金额、均价总合计 操作与上一步类似 放入表#TempSaleDtl
--以前年度列记录（本年度以前的各产品的所有面积、金额、均价总合计 操作与上一步类似）
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)
select ProductGUID,@Year+'-00',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0 from #TempAllSaleDtl
where YearMonth=@Year+'-00'
group by ProductGUID
GO
--从表#TempAllSaleDtl 统计以后年度的各产品的所有面积、金额、均价总合计 操作与上一步类似 放入表#TempSaleDtl
--以后年度列记录（本年度以后的各产品的所有面积、金额、均价总合计 操作与上一步类似）
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)
select ProductGUID,'9999-12',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0
from #TempAllSaleDtl
where YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00'group by ProductGUID
go
--从表#TempAllSaleDtl 统计各产品取所有的合计 放入表#TempSaleDtl
--项目合计列记录(各产品取所有的合计。与上面的区别在于没有添加 here YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00')
insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)
select ProductGUID,'9999-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0
from #TempAllSaleDtl group by ProductGUID
GO
--从表#TempSaleDtl 与 #TempAllSaleDtl统计累积销售面积、累积销售面积比例，累积销售金额 更新表#TempSaleDtl
--更新销售明细TempSaleDtl的累积销售面积、累积销售面积比例，累积销售金额
UPDATE #TempSaleDtl SET ljSaleArea=b.sum_SaleArea,ljSaleAmount=b.sum_SaleAmount,blSaleArea=b.sum_SaleArea/c.all_SaleArea FROM #TempSaleDtl left JOIN
(SELECT n.ProductGUID,n.YearMonth,SUM(m.SaleArea) AS sum_SaleArea,SUM(m.SaleAmount) AS sum_SaleAmount FROM #TempAllSaleDtl m
INNER JOIN #TempSaleDtl n ON m.YearMonth=n.YearMonth AND m.ProductGUID=n.ProductGUID GROUP BY n.ProductGUID,n.YearMonth) b ON #TempSaleDtl.ProductGUID=b.ProductGUID
AND #TempSaleDtl.YearMonth=b.YearMonth LEFT JOIN #ProductSaleArea c ON c.ProductGUID=#TempSaleDtl.ProductGUID
GO
--从表#TempSaleDtl 列转行，转换后的表只有 产品、统计类型、日期，值4列；(每个产品对应的0-12、13 月对应的值) 放入表#tempSaleDtl2
--列转行，转换后的表只有 产品、统计类型、日期，值4列；(每个产品对应的0-12、13 月对应的值)
SELECT * INTO #tempSaleDtl2 FROM (SELECT ProductGUID,'销售套数' AS type,'01' AS typecode,YearMonth,MAX(SaleNum) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth
UNION ALL SELECT ProductGUID,'销售面积' AS type,'02' AS typecode,YearMonth,MAX(SaleArea) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth
UNION ALL SELECT ProductGUID,'销售均价' AS type,'03' AS typecode,YearMonth,MAX(SalePrice) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth
UNION ALL SELECT ProductGUID,'销售金额' AS type,'04' AS typecode,YearMonth,MAX(SaleAmount) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth
UNION ALL SELECT ProductGUID,'累计销售面积' AS type, '05' AS typecode,YearMonth,SUM(ljSaleArea) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth
UNION ALL SELECT ProductGUID,'累计销售面积比例' AS type, '06' AS typecode,YearMonth,SUM(blSaleArea) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth
UNION ALL SELECT ProductGUID,'累计销售金额' AS type, '07' AS typecode,YearMonth,SUM(ljSaleAmount) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth) t
GO
--从表#tempSaleDtl2行转列，按类型聚合 求出每个产品每个类型(面积、金额……)的合计 放入表#tempSaleDtl3]
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
SELECT ProductGUID,type,typecode, MAX(CASE YearMonth WHEN '9999-13' THEN val ELSE 0 END) AS '项目合计', MAX(CASE YearMonth WHEN @Year+'-00' THEN val ELSE 0 END) AS '以前年度合计', MAX(CASE YearMonth WHEN @Year+'-13' THEN val ELSE 0 END) AS '2011年合计', MAX(CASE YearMonth WHEN @Year+'-01' THEN val ELSE 0 END) AS '2011-01', MAX(CASE YearMonth WHEN @Year+'-02' THEN val ELSE 0 END) AS '2011-02', MAX(CASE YearMonth WHEN @Year+'-03' THEN val ELSE 0 END) AS '2011-03', MAX(CASE YearMonth WHEN @Year+'-04' THEN val ELSE 0 END) AS '2011-04', MAX(CASE YearMonth WHEN @Year+'-05' THEN val ELSE 0 END) AS '2011-05', MAX(CASE YearMonth WHEN @Year+'-06' THEN val ELSE 0 END) AS '2011-06', MAX(CASE YearMonth WHEN @Year+'-07' THEN val ELSE 0 END) AS '2011-07', MAX(CASE YearMonth WHEN @Year+'-08' THEN val ELSE 0 END) AS '2011-08', MAX(CASE YearMonth WHEN @Year+'-09' THEN val ELSE 0 END) AS '2011-09', MAX(CASE YearMonth WHEN @Year+'-10' THEN val ELSE 0 END) AS '2011-10', MAX(CASE YearMonth WHEN @Year+'-11' THEN val ELSE 0 END) AS '2011-11', MAX(CASE YearMonth WHEN @Year+'-12' THEN val ELSE 0 END) AS '2011-12', MAX(CASE YearMonth WHEN '9999-12' THEN val ELSE 0 END) AS '以后年度合计'into #tempSaleDtl3
FROM #tempSaleDtl2 GROUP BY ProductGUID,type,typecode ORDER BY ProductGUID,typecode
GO
select * from #tempSaleDtl3
GO
--从Project表中加入项目数据
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
select ProjectName as orderCode,ProjectGUID,ProjectName,
'--' AS '项目合计',
'--' AS '以前年度合计',
'--' AS '2011年合计',
'--' AS '2011-01',
'--' AS '2011-02',
'--' AS '2011-03',
'--' AS '2011-04',
'--' AS '2011-05',
'--' AS '2011-06',
'--' AS '2011-07',
'--' AS '2011-08',
'--' AS '2011-09',
'--' AS '2011-10',
'--' AS '2011-11',
'--' AS '2011-12',
'--' AS '以后年度合计'
from Project
where ProjectGUID=@ProjectGUID
GO
select Project.ProjectName+'.'+a.ProductCode as orderCode,a.ProductGUID,a.ProductName,
'--' AS '项目合计',
'--' AS '以前年度合计',
'--' AS '2011年合计',
'--' AS '2011-01',
'--' AS '2011-02',
'--' AS '2011-03',
'--' AS '2011-04',
'--' AS '2011-05',
'--' AS '2011-06',
'--' AS '2011-07',
'--' AS '2011-08',
'--' AS '2011-09',
'--' AS '2011-10',
'--' AS '2011-11',
'--' AS '2011-12',
'--' AS '以后年度合计'
from #product a
left join Project on a.ProjectGUID=Project.ProjectGUID
GO
--从产品表和Project表、#tempSaleDtl3中加入类型行数据
select c.ProjectName+'.'+b.ProductCode+'.'+a.typecode as orderCode,a.ProductGUID, a.[type],
cast(a.[项目合计] as varchar(20)),
cast(a.[以前年度合计] as varchar(20)),
cast(a.[2011年合计] as varchar(20)),
cast(a.[2011-01] as varchar(20)),
cast(a.[2011-02] as varchar(20)),
cast(a.[2011-03] as varchar(20)),
cast(a.[2011-04] as varchar(20)),
cast(a.[2011-05] as varchar(20)),
cast(a.[2011-06] as varchar(20)),
cast(a.[2011-07] as varchar(20)),
cast(a.[2011-08] as varchar(20)),
cast(a.[2011-09] as varchar(20)),
cast(a.[2011-10] as varchar(20)),
cast(a.[2011-11] as varchar(20)),
cast(a.[2011-12] as varchar(20)),
cast(a.[以后年度合计] as varchar(20))
from #tempSaleDtl3 a
left join #product b on a.ProductGUID=b.ProductGUID
left join Project c on b.ProjectGUID=c.ProjectGUID
GO
--从Project表中加入项目数据
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目
DECLARE @Year CHAR(4)SET @Year='2011'--要统一的年份
select * from ( select ProjectName as orderCode,ProjectGUID,ProjectName,
'--' AS '项目合计',
'--' AS '以前年度合计',
'--' AS '2011年合计',
'--' AS '2011-01',
'--' AS '2011-02',
'--' AS '2011-03',
'--' AS '2011-04',
'--' AS '2011-05',
'--' AS '2011-06',
'--' AS '2011-07',
'--' AS '2011-08',
'--' AS '2011-09',
'--' AS '2011-10',
'--' AS '2011-11',
'--' AS '2011-12',
'--' AS '以后年度合计'
from Project
where ProjectGUID=@ProjectGUID
----项目1 8FA659C8-3DA9-4330-B277-9B517E67606D 项目1
union all
--从产品表和Project表中加入合计行数据
select Project.ProjectName+'.'+a.ProductCode as orderCode,a.ProductGUID,a.ProductName,
'--' AS '项目合计',
'--' AS '以前年度合计',
'--' AS '2011年合计',
'--' AS '2011-01',
'--' AS '2011-02',
'--' AS '2011-03',
'--' AS '2011-04',
'--' AS '2011-05',
'--' AS '2011-06',
'--' AS '2011-07',
'--' AS '2011-08',
'--' AS '2011-09',
'--' AS '2011-10',
'--' AS '2011-11',
'--' AS '2011-12',
'--' AS '以后年度合计'
from #product a
left join Project on a.ProjectGUID=Project.ProjectGUID
union ALL
--从产品表和Project表、#tempSaleDtl3中加入类型行数据
select c.ProjectName+'.'+b.ProductCode+'.'+a.typecode as orderCode,a.ProductGUID, a.[type],
cast(a.[项目合计] as varchar(20)),
cast(a.[以前年度合计] as varchar(20)),
cast(a.[2011年合计] as varchar(20)),
cast(a.[2011-01] as varchar(20)),
cast(a.[2011-02] as varchar(20)),
cast(a.[2011-03] as varchar(20)),
cast(a.[2011-04] as varchar(20)),
cast(a.[2011-05] as varchar(20)),
cast(a.[2011-06] as varchar(20)),
cast(a.[2011-07] as varchar(20)),
cast(a.[2011-08] as varchar(20)),
cast(a.[2011-09] as varchar(20)),
cast(a.[2011-10] as varchar(20)),
cast(a.[2011-11] as varchar(20)),
cast(a.[2011-12] as varchar(20)),
cast(a.[以后年度合计] as varchar(20))
from #tempSaleDtl3 a
left join #product b on a.ProductGUID=b.ProductGUID
left join Project c on b.ProjectGUID=c.ProjectGUID) t1 order by orderCode
GO
drop table #product
drop table #TempAllSaleDtl
DROP TABLE #TempSaleDtl
DROP TABLE #tempSaleDtl2
DROP TABLE #tempSaleDtl3
DROP TABLE #ProductSaleArea
select * from dbo.SaleDtl
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--SET STATISTICS IO ON
--SET STATISTICS TIME ON
DECLARE @ProjectGUID UNIQUEIDENTIFIER
SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'DECLARE @Year CHAR(4)SET @Year='2011'
/*临时表说明
#product：用项目过滤后，将“合计”作为一个产品的集合
#TempAllSaleDtl：通过项目过滤后的销售明细，所有月的
#ProductSaleArea：各个产品的总面积，用于计算比例
#TempSaleDtl：通过日期过滤，且加工过后的销售明细，包括增加累积列，以前年度、以后年度、项目合计的记录
#tempSaleDtl2：列转行后的数据集
#tempSaleDtl3：行转列后的数据集
*/
select ProductGUID,ProductName,ProjectGUID,ProductCode into #product
from(select ProductGUID,ProductName,ProjectGUID,ProductName as ProductCode
from Product where ProjectGUID=@ProjectGUID union all
select '00000000-0000-0000-0000-000000000000','合计',@ProjectGUID,'00' as ProductCode) a
--SELECT * FROM #product
----查找项目所有产品的销售明细：#TempAllSaleDtl
SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice INTO #TempAllSaleDtl
FROM dbo.SaleDtl WHERE ProductGUID IN ( SELECT ProductGUID FROM dbo.Product WHERE ProjectGUID=@ProjectGUID)
--SELECT * FROM #TempAllSaleDtl
--ORDER BY ProductGUID,YearMonth
--根据现有数据统计，向#TempAllSaleDtl添加总合计记录
insert into #TempAllSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice)
select '00000000-0000-0000-0000-000000000000',YearMonth,SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea)from #TempAllSaleDtlgroup by YearMonth
--SELECT * FROM #TempAllSaleDtl
--ORDER BY ProductGUID,YearMonth
--查找某年的销售明细：#TempSaleDtl
SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice, SalePrice AS ljSaleArea, SalePrice AS blSaleArea, SalePrice AS ljSaleAmount INTO #TempSaleDtl
FROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Year
--SELECT * FROM #TempSaleDtl
--ORDER BY ProductGUID,YearMonth
--获取项目各个产品的总销售面积：#ProductSaleArea
SELECT ProductGUID,SUM(SaleArea) AS all_SaleArea INTO #ProductSaleArea FROM #TempAllSaleDtl GROUP BY ProductGUID
--SELECT * FROM #ProductSaleArea
--ORDER BY ProductGUID
--添加2011合计列的记录(本年度的各产品的所有面积、金额、均价总合计)
insert into #TempSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)
select ProductGUID,@Year+'-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0
from #TempSaleDtl group by ProductGUID
--SELECT * FROM #TempSaleDtl
--ORDER BY ProductGUID,YearMonth
--以前年度列记录（本年度以前的各产品的所有面积、金额、均价总合计 操作与上一步类似）
insert into #TempSaleDtl( ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)
select ProductGUID,@Year+'-00',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0
from #TempAllSaleDtl where YearMonth=@Year+'-00'group by ProductGUID
--SELECT * FROM #TempSaleDtl
--ORDER BY ProductGUID,YearMonth
--以后年度列记录（本年度以后的各产品的所有面积、金额、均价总合计 操作与上一步类似）
insert into #TempSaleDtl( ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)
select ProductGUID,'9999-12',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0 from #TempAllSaleDtl
where YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00'group by ProductGUID
--SELECT * FROM #TempSaleDtl
--ORDER BY ProductGUID,YearMonth
--项目合计列记录(各产品取所有的合计。与上面的区别在于没有添加 here YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00')
insert into #TempSaleDtl( ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount)
select ProductGUID,'9999-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0from #TempAllSaleDtl group by ProductGUID
--SELECT * FROM #TempSaleDtl
--ORDER BY ProductGUID,YearMonth
--更新销售明细TempSaleDtl的累积销售面积、累积销售面积比例，累积销售金额
UPDATE #TempSaleDtl SET ljSaleArea=b.sum_SaleArea, ljSaleAmount=b.sum_SaleAmount, blSaleArea=b.sum_SaleArea/c.all_SaleArea
FROM #TempSaleDtl left JOIN ( SELECT n.ProductGUID,n.YearMonth,SUM(m.SaleArea) AS sum_SaleArea,SUM(m.SaleAmount) AS sum_SaleAmount
FROM #TempAllSaleDtl m INNER JOIN #TempSaleDtl n ON m.YearMonth=n.YearMonth AND m.ProductGUID=n.ProductGUID
GROUP BY n.ProductGUID,n.YearMonth) b ON #TempSaleDtl.ProductGUID=b.ProductGUID AND #TempSaleDtl.YearMonth=b.YearMonth
LEFT JOIN #ProductSaleArea c ON c.ProductGUID=#TempSaleDtl.ProductGUID
--SELECT * FROM #TempSaleDtl
--ORDER BY ProductGUID,YearMonth
--列转行，转换后的表只有 产品、统计类型、日期，值4列；(每个产品对应的0-12、13 月对应的值)
SELECT * INTO #tempSaleDtl2 FROM ( SELECT ProductGUID,'销售套数' AS type,'01' AS typecode,YearMonth,MAX(SaleNum) AS val FROM #TempSaleDtl
GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'销售面积' AS type,'02' AS typecode,YearMonth,MAX(SaleArea) AS val FROM #TempSaleDtl
GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'销售均价' AS type,'03' AS typecode,YearMonth,MAX(SalePrice) AS val FROM #TempSaleDtl
GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'销售金额' AS type,'04' AS typecode,YearMonth,MAX(SaleAmount) AS val FROM #TempSaleDtl
GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'累计销售面积' AS type, '05' AS typecode,YearMonth,SUM(ljSaleArea) FROM #TempSaleDtl
GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'累计销售面积比例' AS type, '06' AS typecode,YearMonth,SUM(blSaleArea) FROM #TempSaleDtl
GROUP BY ProductGUID,YearMonth UNION ALL SELECT ProductGUID,'累计销售金额' AS type, '07' AS typecode,YearMonth,SUM(ljSaleAmount) FROM #TempSaleDtl
GROUP BY ProductGUID,YearMonth) t
--SELECT * FROM #tempSaleDtl2
--ORDER BY ProductGUID,yearmonth
--行转列，按日期聚合
SELECT ProductGUID,type,typecode, MAX(CASE YearMonth WHEN '9999-13' THEN val ELSE 0 END) AS '项目合计',
MAX(CASE YearMonth WHEN @Year+'-00' THEN val ELSE 0 END) AS '以前年度合计',
MAX(CASE YearMonth WHEN @Year+'-13' THEN val ELSE 0 END) AS '2011年合计',
MAX(CASE YearMonth WHEN @Year+'-01' THEN val ELSE 0 END) AS '2011-01',
MAX(CASE YearMonth WHEN @Year+'-02' THEN val ELSE 0 END) AS '2011-02',
MAX(CASE YearMonth WHEN @Year+'-03' THEN val ELSE 0 END) AS '2011-03',
MAX(CASE YearMonth WHEN @Year+'-04' THEN val ELSE 0 END) AS '2011-04',
MAX(CASE YearMonth WHEN @Year+'-05' THEN val ELSE 0 END) AS '2011-05',
MAX(CASE YearMonth WHEN @Year+'-06' THEN val ELSE 0 END) AS '2011-06',
MAX(CASE YearMonth WHEN @Year+'-07' THEN val ELSE 0 END) AS '2011-07',
MAX(CASE YearMonth WHEN @Year+'-08' THEN val ELSE 0 END) AS '2011-08',
MAX(CASE YearMonth WHEN @Year+'-09' THEN val ELSE 0 END) AS '2011-09',
MAX(CASE YearMonth WHEN @Year+'-10' THEN val ELSE 0 END) AS '2011-10',
MAX(CASE YearMonth WHEN @Year+'-11' THEN val ELSE 0 END) AS '2011-11',
MAX(CASE YearMonth WHEN @Year+'-12' THEN val ELSE 0 END) AS '2011-12',
MAX(CASE YearMonth WHEN '9999-12' THEN val ELSE 0 END) AS '以后年度合计'into #tempSaleDtl3 FROM #tempSaleDtl2
GROUP BY ProductGUID,type,typecode ORDER BY ProductGUID,typecode
--SELECT * FROM #tempSaleDtl3
--ORDER BY ProductGUID,typecode
--从Project表中加入项目数据
select * from ( select ProjectName as orderCode,ProjectGUID,ProjectName,
'--' AS '项目合计',
'--' AS '以前年度合计',
'--' AS '2011年合计',
'--' AS '2011-01',
'--' AS '2011-02',
'--' AS '2011-03',
'--' AS '2011-04',
'--' AS '2011-05',
'--' AS '2011-06',
'--' AS '2011-07',
'--' AS '2011-08',
'--' AS '2011-09',
'--' AS '2011-10',
'--' AS '2011-11',
'--' AS '2011-12',
'--' AS '以后年度合计'
from Project where ProjectGUID=@ProjectGUID
----项目1 8FA659C8-3DA9-4330-B277-9B517E67606D 项目1
union all
--从产品表和Project表中加入合计行数据
select Project.ProjectName+'.'+a.ProductCode as orderCode,a.ProductGUID,a.ProductName,
'--' AS '项目合计',
'--' AS '以前年度合计',
'--' AS '2011年合计',
'--' AS '2011-01',
'--' AS '2011-02',
'--' AS '2011-03',
'--' AS '2011-04',
'--' AS '2011-05',
'--' AS '2011-06',
'--' AS '2011-07',
'--' AS '2011-08',
'--' AS '2011-09',
'--' AS '2011-10',
'--' AS '2011-11',
'--' AS '2011-12',
'--' AS '以后年度合计'
from #product a
left join Project on a.ProjectGUID=Project.ProjectGUID
union ALL
--从产品表和Project表、#tempSaleDtl3中加入类型行数据
select c.ProjectName+'.'+b.ProductCode+'.'+a.typecode as orderCode,a.ProductGUID, a.[type],
cast(a.[项目合计] as varchar(20)),
cast(a.[以前年度合计] as varchar(20)),
cast(a.[2011年合计] as varchar(20)),
cast(a.[2011-01] as varchar(20)),
cast(a.[2011-02] as varchar(20)),
cast(a.[2011-03] as varchar(20)),
cast(a.[2011-04] as varchar(20)),
cast(a.[2011-05] as varchar(20)),
cast(a.[2011-06] as varchar(20)),
cast(a.[2011-07] as varchar(20)),
cast(a.[2011-08] as varchar(20)),
cast(a.[2011-09] as varchar(20)),
cast(a.[2011-10] as varchar(20)),
cast(a.[2011-11] as varchar(20)),
cast(a.[2011-12] as varchar(20)),
cast(a.[以后年度合计] as varchar(20))
from #tempSaleDtl3 a
left join #product b on a.ProductGUID=b.ProductGUID
left join Project c on b.ProjectGUID=c.ProjectGUID) t1 order by orderCode
drop table #product
drop table #TempAllSaleDtl
DROP TABLE #TempSaleDtl
DROP TABLE #tempSaleDtl2
DROP TABLE #tempSaleDtl3
DROP TABLE #ProductSaleArea
GO
/*开盘调价报告-本次开盘产品列表*/
ALTER PROC usp_s_KpTjReport_KpProduct
(
@PlanGUID UNIQUEIDENTIFIER , --开盘调价方案GUID
@ProjGUID UNIQUEIDENTIFIER --所属项目GUID
)
AS
SET NOCOUNT ON;
-------------------------------------------------------------------------------------------------------------------------------------------
--1. 查询最新审核项目分解版本
-------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @ProjAnalysisGUID UNIQUEIDENTIFIER
SELECT TOP 1 @ProjAnalysisGUID=ProjAnalysisGUID
FROM dbo.s_ProjAnalysis
WHERE ProjGUID=@ProjGUID AND State='已审核'
ORDER BY ShDate DESC
--项目名称(用于分区全名称匹配)
DECLARE @ProjName VARCHAR(400)
SELECT @ProjName=ProjName
FROM dbo.p_Project
WHERE ProjGUID=@ProjGUID
--SELECT @ProjAnalysisGUID
-------------------------------------------------------------------------------------------------------------------------------------------
--2. 查询出要求格式的分区和业态名称，以及列表计算需要的数据
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT A.RoomGUID,
[ProductCode]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D.Area2ProductTypeCode) ELSE
MAX(CASE WHEN G.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.Area2ProductTypeCode END )
END ,
[AreaName]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(ISNULL(D3.Area2ProductName+'-','')+ISNULL(D2.Area2ProductName+'-','')+D1.Area2ProductName) ELSE
MAX(CASE WHEN G.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN ISNULL(D3.Area2ProductName+'-','')+ISNULL(D2.Area2ProductName+'-','')+D1.Area2ProductName END)
END,
[ProductName]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D.Area2ProductName) ELSE
MAX(CASE WHEN G.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.Area2ProductName END )
END ,
[BldArea]=MAX(A.BldArea),
[ToTal]=MAX(A.ToTal),
[ZhDiscntRate]=CAST(MAX(F.ZhDiscntRate)/100 AS DECIMAL(5,3)) --综合折扣率保存为1-100
INTO #A
FROM dbo.s_TjResult A
INNER JOIN dbo.p_room B ON B.RoomGUID=A.RoomGUID
INNER JOIN dbo.p_BuildProductType C ON C.BProductTypeCode=B.BProductTypeCode
INNER JOIN dbo.s_Area2Product D ON D.ProductTypeGUID=C.BuildProductTypeGUID AND D.ProjAnalysisGUID=@ProjAnalysisGUID--试算房间所属产品 --可能有多条
LEFT JOIN dbo.s_Area2Product D1 ON D1.Area2ProductTypeCode=D.ParentCode AND D1.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区
LEFT JOIN dbo.s_Area2Product D2 ON D2.Area2ProductTypeCode=D1.ParentCode AND D2.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区
LEFT JOIN dbo.s_Area2Product D3 ON D3.Area2ProductTypeCode=D2.ParentCode AND D3.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区上级分区
INNER JOIN dbo.p_Building E ON E.BldGUID=A.BldGUID--试算房间楼栋
INNER JOIN dbo.s_TjPlan F ON F.PlanGUID=@PlanGUID--开盘调价计划
LEFT JOIN dbo.p_Building G ON G.IsBld=0 AND G.ParentCode+'.'+G.BldCode=E.ParentCode AND B.ProjGUID=@ProjGUID--试算房间楼栋所属区域
WHERE A.PlanGUID=@PlanGUID
GROUP BY A.RoomGUID
HAVING COUNT(A.RoomGUID)=1 OR COUNT(CASE WHEN G.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN 1 END)=1
--SELECT * FROM #A
-------------------------------------------------------------------------------------------------------------------------------------------
--3. 按业态计算指标
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT AreaName=MAX(A.AreaName)
,ProductName=MAX(A.ProductName)
,RoomCount=COUNT(1)
,BldArea=CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0))--面积
,ZQJJ= CASE WHEN CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0))=0 THEN 0 ELSE
CAST(SUM(ISNULL(A.ToTal,0))/10000 AS DECIMAL(18,0))
/ CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0)) END --折前均价（元/平米）
,ZQTotal=CAST(SUM(ISNULL(A.ToTal,0))/10000 AS DECIMAL(18,0))--折前总价（万元）
,ZSJJ= CASE WHEN CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0))=0 THEN 0 ELSE
CAST(SUM(ISNULL(A.ToTal,0))/10000 AS DECIMAL(18,0))
/ CAST(SUM(ISNULL(A.BldArea,0)) AS DECIMAL(18,0)) END
* MAX(A.ZhDiscntRate)--折实均价（元/平米）
,ZSTotal= CAST(SUM(ISNULL(A.ToTal,0))/10000 AS DECIMAL(18,0))
*MAX(A.ZhDiscntRate)--折实总价（万元）
,Remarks=MAX('综合折扣率为：'+CAST(CAST(A.ZhDiscntRate*100 AS DECIMAL(5,1))AS VARCHAR(5))+'%')--备注
INTO #B
FROM #A A
GROUP BY A.ProductCode
-------------------------------------------------------------------------------------------------------------------------------------------
--4. 联合查询出列表展现结果
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT AreaName=A.AreaName ,--分区
ProductName ,--业态
RoomCount ,--套数
BldArea=CAST(BldArea AS DECIMAL(18,0)),--面积
ZQJJ =CAST(ZQJJ AS DECIMAL(18,0)),--折前均价
ZQTotal=CAST(ZQTotal AS DECIMAL(18,0)) ,--折前总价
ZSJJ =CAST(ZSJJ AS DECIMAL(18,0)),--折实均价
ZSTotal =CAST(ZSTotal AS DECIMAL(18,0)),--折实总价
Remarks,--备注
FqRowspan=CASE WHEN A.AreaName IS NULL THEN 1 ELSE B.FqRowspan END --当前分区下产品个数（用于Rowspan）
FROM #B A
INNER JOIN (
SELECT AreaName,COUNT(1) AS FqRowspan
FROM #B
GROUP BY AreaName
) B ON B.AreaName IS NULL OR B.AreaName=A.AreaName--加入统计每个分区的产品个数
UNION ALL
SELECT '合计',
NULL,
SUM(RoomCount) ,
SUM(CAST(BldArea AS DECIMAL(18,0))),
NULL,
SUM(CAST(ZQTotal AS DECIMAL(18,0))),
NULL,
SUM(CAST(ZSTotal AS DECIMAL(18,0))),
MAX(Remarks),
1
FROM #B
ORDER BY AreaName ASC,ProductName DESC
--SELECT * FROM #A ORDER BY RoomGUID
-------------------------------------------------------------------------------------------------------------------------------------------
--5. 删除临时表
-------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE #A
DROP TABLE #B
GO
/*开盘调价方案审批-工作流域取数*/
ALTER PROC usp_s_KpTjReport_WF
(
@PlanGUID UNIQUEIDENTIFIER --开盘调价方案GUID
)
AS
SET NOCOUNT ON;
DECLARE @JcwjDiff DECIMAL(18,0)--累计实现业态均价与决策文件均价差额(当前未开票或未匹配业态则0，若任一业态均价小于0则为-1，否则为1)
DECLARE @ProjGUID UNIQUEIDENTIFIER--当前项目GUID
DECLARE @ProjAnalysisGUID UNIQUEIDENTIFIER--当前最新审核项目分解版本GUID
DECLARE @PlanType VARCHAR(20)--当前方案类型
DECLARE @TjDiff DECIMAL(18,0)--调整前后总价差额合计(万元)
DECLARE @TjTotal DECIMAL(18,0)--调价房间总价(万元)
DECLARE @TjCount BIGINT--调价房间套数
-------------------------------------------------------------------------------------------------------------------------------------------
--1. 查询累计实现业态均价与决策文件均价差额
--1.1 获取当前项目和最新审核项目分解版本
-------------------------------------------------------------------------------------------------------------------------------------------
SET @JcwjDiff=0 --默认值
--项目GUID及报告类型
SELECT @ProjGUID=ProjGUID,@PlanType=PlanType
FROM dbo.s_TjPlan
WHERE PlanGUID=@PlanGUID
--最新审核项目分解版本
SELECT TOP 1 @ProjAnalysisGUID=ProjAnalysisGUID
FROM dbo.s_ProjAnalysis
WHERE ProjGUID=@ProjGUID AND State='已审核'
ORDER BY ShDate DESC
--项目名称(用于分区全名称匹配)
DECLARE @ProjName VARCHAR(400)
SELECT @ProjName=ProjName
FROM dbo.p_Project
WHERE ProjGUID=@ProjGUID
--SELECT @ProjGUID AS 项目,@PlanType AS 类型,@ProjAnalysisGUID AS 项目分解
IF( @PlanType='调价报告')
BEGIN
SELECT A.RoomGUID,--A.bldGUID,COUNT(A.RoomGUID) AS CRoomGUID,COUNT(A.BldGUID) AS CBld,COUNT(CASE WHEN C1.Area2ProductName=A.FqName THEN 1 END) AS C,
[ProductCode]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D.Area2ProductTypeCode)
ELSE
MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.Area2ProductTypeCode END)
END ,
[AreaName]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D1.Area2ProductName)
ELSE
MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D1.Area2ProductName END)
END,
[ProductName]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D.Area2ProductName)
ELSE
MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.Area2ProductName END)
END ,
[RgTotal]=MAX(E.CjTotal),--元
[RgBldArea]=MAX(E.BldArea),
[HtTotal]=MAX(F.HtTotal),--元
[HtBldArea]=MAX(F.BldArea),
[AttachRoomArea]=MAX(O.BldArea),--认购合同附属房产的面积
[KpDate]=MAX(H.KpDate),
[RoomState]=MAX(B.Status),
[TjTotal]=MAX(A.Total),--元
[RoomBzTotal]=MAX(B.Total),--元
[RoomBldArea]=MAX(B.BldArea),
[XmFjPrice]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D.SalePrice)
ELSE
MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.SalePrice END)
END ,--元
[BldSalePrice]=MAX(M.SalePrice) ,--元
[BldSaleArea]=MAX(M.SaleArea),
[JcwjSaleTotal]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D.SalePrice)
ELSE
MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.SaleTotal END)
END ,--万元
[JcwjSaleArea]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D.SaleArea)
ELSE
MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.SaleArea END)
END ,
[JcwjSalePrice]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(D.SalePrice)
ELSE
MAX(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN D.SalePrice END)
END --元
INTO #A
FROM dbo.s_TjResult A
--对应业态分区
INNER JOIN dbo.p_room B ON B.RoomGUID=A.RoomGUID
INNER JOIN dbo.p_BuildProductType C ON C.BProductTypeCode=B.BProductTypeCode
INNER JOIN dbo.s_Area2Product D ON D.ProductTypeGUID=C.BuildProductTypeGUID AND D.ProjAnalysisGUID=@ProjAnalysisGUID --可能有多条
LEFT JOIN dbo.s_Area2Product D1 ON D1.Area2ProductTypeCode=D.ParentCode AND D1.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区
LEFT JOIN dbo.s_Area2Product D2 ON D2.Area2ProductTypeCode=D1.ParentCode AND D2.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区
LEFT JOIN dbo.s_Area2Product D3 ON D3.Area2ProductTypeCode=D2.ParentCode AND D3.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区上级分区
----对应合同订单
LEFT JOIN dbo.s_order E ON E.RoomGUID =A.RoomGUID AND E.Status='激活'
LEFT JOIN dbo.s_contract F ON F.RoomGUID =A.RoomGUID AND F.Status='激活'
--合同订单附属房产（统计销售面积）
LEFT JOIN dbo.s_OCAttachRoom O ON O.RoomGUID=A.RoomGUID AND O.SaleGUID IN(E.OrderGUID,F.ContractGUID)--一个房间不可能同时被认购和签约
----对应开盘计划（取开盘日期）
LEFT JOIN dbo.s_TjResult G ON G.RoomGUID=A.RoomGUID AND G.PlanGUID IN(SELECT PlanGUID FROM s_tjPlan WHERE PlanType='开盘报告' AND ProjGUID=@ProjGUID)--对应开盘计划的试算结果
LEFT JOIN dbo.s_TjPlan H ON H.PlanGUID=G.PlanGUID--对应的开盘计划
--对应开盘或调价试算结果（当房间做多个调价报告时，取最新制定的方案试算记录）
LEFT JOIN dbo.s_TjResult K ON K.RoomGUID=A.RoomGUID AND K.PlanGUID IN (
SELECT TOP 1 X.PlanGUID
FROM s_tjPlan X
INNER JOIN ( SELECT PlanGUID
FROM s_TjResult
WHERE RoomGUID = A.RoomGUID
) L ON L.PlanGUID = X.PlanGUID
WHERE X.ShDate IS NOT NULL --要求已审核
ORDER BY X.ZdDate DESC )
INNER JOIN dbo.p_Building M ON M.IsBld=1 AND M.BldGUID=A.BldGUID--房间所属楼栋
LEFT JOIN dbo.p_Building N ON N.IsBld=0 AND N.ParentCode+'.'+N.BldCode=M.ParentCode AND N.ProjGUID=@ProjGUID--楼栋所属区域
WHERE A.PlanGUID=@PlanGUID
GROUP BY A.RoomGUID
HAVING COUNT(A.RoomGUID)=1 OR COUNT(CASE WHEN N.BldFullName=@ProjName+ISNULL('-'+D3.Area2ProductName,'')+ISNULL('-'+D2.Area2ProductName,'')+ISNULL('-'+D1.Area2ProductName,'') THEN 1 END)=1
--SELECT * FROM #A ORDER BY [ProductCode] DESC
IF(EXISTS (SELECT 1 FROM #A))--当未匹配业态，则决策文件均价差额为0
BEGIN
-------------------------------------------------------------------------------------------------------------------------------------------
--1.3 按业态计算指标,获得预计整体可实现折实均价与决策文件差额
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT A.ProductCode
--预计整体可实现与决策文件差额
,[Diff]=
--'折实均价(元/平米)'
CASE WHEN
(
ISNULL(SUM(CASE WHEN A.RoomState IN ('认购','签约') THEN ISNULL(A.RgBldArea,0)+ISNULL(A.HtBldArea,0)+ISNULL(A.[AttachRoomArea],0) END),0)
+ISNULL( SUM(CASE WHEN DATEDIFF(DAY,GETDATE(), A.[KpDate])<=0 AND A.RoomState NOT IN ('认购','签约') THEN A.[RoomBldArea] END),0)
+ISNULL(
SUM(
CASE WHEN
ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0
THEN
--如果所有楼栋都生成房间，则统计房间建筑面积之和
--如果部分楼栋没有生成房间，则显示生成房间楼栋建筑面积之和+楼栋预计销售面积
CASE WHEN A.RoomGUID IS NULL THEN A.[BldSaleArea]
ELSE A.[RoomBldArea] END
END )
,0)
)=0 THEN 0 ELSE
(
ISNULL(SUM(CASE WHEN A.RoomState IN ('认购','签约') THEN ISNULL(A.RgTotal,0)+ISNULL(A.HtTotal,0) END),0)
+ISNULL(SUM(CASE WHEN DATEDIFF(DAY,GETDATE(), A.[KpDate])<=0 AND A.RoomState NOT IN ('认购','签约') THEN A.[RoomBzTotal] END),0)
+
(ISNULL(SUM(CASE WHEN ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0 AND ISNULL(A.[TjTotal],0)<>0 THEN A.[TjTotal] END),0)--未到推盘日期已经定价房间 的房间总价
+ISNULL(SUM(CASE WHEN ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0 AND ISNULL(A.[TjTotal],0)=0 AND ISNULL(A.[RoomBzTotal],0)<>0 THEN A.XmFjPrice*A.[RoomBldArea] END),0)--未到推盘日期未定价房间 的房间总价
+ISNULL(SUM(CASE WHEN ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0 AND NOT(ISNULL(A.[TjTotal],0)<>0) AND NOT(ISNULL(A.[TjTotal],0)=0 AND ISNULL(A.[RoomBzTotal],0)<>0) THEN A.[BldSalePrice]*A.[BldSaleArea] END),0)--未到推盘日期未定价的楼栋房间
)
)
/(
ISNULL(SUM(CASE WHEN A.RoomState IN ('认购','签约') THEN ISNULL(A.RgBldArea,0)+ISNULL(A.HtBldArea,0)+ISNULL(A.[AttachRoomArea],0) END),0)
+ISNULL( SUM(CASE WHEN DATEDIFF(DAY,GETDATE(), A.[KpDate])<=0 AND A.RoomState NOT IN ('认购','签约') THEN A.[RoomBldArea] END),0)
+ISNULL(
SUM(
CASE WHEN
ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0
THEN
--如果所有楼栋都生成房间，则统计房间建筑面积之和
--如果部分楼栋没有生成房间，则显示生成房间楼栋建筑面积之和+楼栋预计销售面积
CASE WHEN A.RoomGUID IS NULL THEN A.[BldSaleArea]
ELSE A.[RoomBldArea] END
END )
,0)
) END
-ISNULL(MAX(A.[JcwjSalePrice]),0)
INTO #A1
FROM #A A
GROUP BY A.ProductCode
-------------------------------------------------------------------------------------------------------------------------------------------
--1.4 计算累计实现业态均价与决策文件均价差额
--1.5 当其中有一个差额小于0，则均价差额返回-1
--1.6 当所有差额都大于0，则均价差额返回1
--1.7 删除临时表
-------------------------------------------------------------------------------------------------------------------------------------------
--SELECT 1,* FROM #A1
IF(EXISTS(SELECT 1 FROM #A1 WHERE Diff<0))
SET @JcwjDiff=-1
ELSE
SET @JcwjDiff=1
DROP TABLE #A1
END ;
DROP TABLE #A
END ;
--SELECT @JcwjDiff
-------------------------------------------------------------------------------------------------------------------------------------------
--2. 查询调整前后总价差额合计
--2.1 最近一次执行的开盘或调价方案
--2.1 如果是“开盘报告”或之前没有报告则不计算
-------------------------------------------------------------------------------------------------------------------------------------------
--获取本次调价的审核日期（用于查看历史记录时筛选）
DECLARE @ShDate DATETIME
SELECT @ShDate=ShDate
FROM dbo.s_TjPlan
WHERE PlanGUID=@PlanGUID
-------------------------------------------------------------------------------------------------------------------------------------------
--2.2 查询比较的基本信息
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT A.RoomGUID,
[ProductCode]=CASE WHEN COUNT(A.RoomGUID)=1 THEN
MAX(E.Area2ProductTypeCode) ELSE
MAX(CASE WHEN I.BldFullName=@ProjName+ISNULL('-'+E3.Area2ProductName,'')+ISNULL('-'+E2.Area2ProductName,'')+ISNULL('-'+E1.Area2ProductName,'') THEN E.Area2ProductTypeCode END )
END ,
[OldSaleTotal]=MAX(B.Total),
[NewSaleTotal]=MAX(A.Total),
[OldBldArea]=MAX(B.BldArea),
[NewBldArea]=MAX(A.BldArea),
[OldZhDiscntRate]=CAST(MAX(H.ZhDiscntRate)/100 AS DECIMAL(5,3)), --综合折扣率保存为1-100
[NewZhDiscntRate]=CAST(MAX(G.ZhDiscntRate)/100 AS DECIMAL(5,3)) --综合折扣率保存为1-100
INTO #B
--SELECT C.RoomGUID,D.BuildProductTypeGUID,A.PlanGUID,B.PlanGUID
FROM dbo.s_TjResult A
INNER JOIN dbo.p_Room C ON C.RoomGUID=A.RoomGUID
INNER JOIN dbo.s_TjResult B ON b.RoomGUID=A.RoomGUID --房间对应的之前最近审核过的一次开盘调价方案试算
AND B.PlanGUID IN (
SELECT TOP 1 X.PlanGUID
FROM s_tjPlan X
INNER JOIN ( SELECT PlanGUID
FROM s_TjResult
WHERE PlanGUID<>@PlanGUID AND RoomGUID = A.RoomGUID
) Y ON Y.PlanGUID = X.PlanGUID
WHERE x.ShDate<ISNULL(@ShDate,GETDATE())
ORDER BY X.ShDate DESC )
INNER JOIN dbo.p_BuildProductType D ON D.BProductTypeCode=C.BProductTypeCode
INNER JOIN dbo.s_Area2Product E ON E.ProductTypeGUID=D.BuildProductTypeGUID AND E.ProjAnalysisGUID=@ProjAnalysisGUID---可能有多条
LEFT JOIN dbo.s_Area2Product E1 ON E1.Area2ProductTypeCode=E.ParentCode AND E1.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区
LEFT JOIN dbo.s_Area2Product E2 ON E2.Area2ProductTypeCode=E1.ParentCode AND E2.ProjAnalysisGUID=@ProjAnalysisGUID----产品所属分区上级分区
LEFT JOIN dbo.s_Area2Product E3 ON E3.Area2ProductTypeCode=E2.ParentCode AND E3.ProjAnalysisGUID=@ProjAnalysisGUID--产品所属分区上级分区上级分区
INNER JOIN dbo.p_Building F ON F.BldGUID=A.BldGUID--试算房间楼栋
INNER JOIN dbo.s_TjPlan G ON G.PlanGUID=A.PlanGUID
INNER JOIN dbo.s_TjPlan H ON H.PlanGUID=B.PlanGUID
LEFT JOIN dbo.p_Building I ON I.IsBld=0 AND I.ParentCode+'.'+I.BldCode=F.ParentCode AND I.ProjGUID=@ProjGUID--楼栋所属区域
WHERE A.PlanGUID=@PlanGUID
GROUP BY A.RoomGUID
HAVING COUNT(A.RoomGUID)=1 OR COUNT(CASE WHEN I.BldFullName=@ProjName+ISNULL('-'+E3.Area2ProductName,'')+ISNULL('-'+E2.Area2ProductName,'')+ISNULL('-'+E1.Area2ProductName,'') THEN 1 END)=1
-------------------------------------------------------------------------------------------------------------------------------------------
--2.3 按业态统计总价差额
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT [Diff] = CAST(ISNULL(SUM(A.NewSaleTotal*A.NewZhDiscntRate),0)/10000 - ISNULL(SUM(A.OldSaleTotal*A.OldZhDiscntRate),0)/10000 AS DECIMAL(18, 0))
--差额=折实总价（调整后）-折实总价（调整前）
INTO #B1
FROM #B A
GROUP BY A.ProductCode
-------------------------------------------------------------------------------------------------------------------------------------------
--2.3 计算所有总价差额
--2.4 删除临时表
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT @TjDiff=SUM([Diff]) FROM #B1
DROP TABLE #B
DROP TABLE #B1
-------------------------------------------------------------------------------------------------------------------------------------------
--3. 输出开盘调价审批工作流相关域数据
-------------------------------------------------------------------------------------------------------------------------------------------
SELECT @TjTotal = CAST(SUM(Total)/10000 AS DECIMAL(18, 0)) ,
@TjCount = COUNT(1)
FROM dbo.s_TjResult
WHERE PlanGUID = @PlanGUID
--
SELECT A.PlanGUID ,
A.SxDate ,
B.ProjName ,
PlanName ,
ZdDate ,
PlanType ,
ZhDiscntRate=CAST(ZhDiscntRate AS DECIMAL(5,1)) ,
KpDate ,
TlDiscntRate=CAST(TlDiscntRate AS DECIMAL(5,1)) ,
--
[TjTotal] =@TjTotal ,--调价房间总价
[TjCount] = @TjCount ,--调价房间套数
[TjDiff] = ISNULL(CASE WHEN A.PlanType='开盘报告' THEN @TjTotal
ELSE @TjDiff
END, 0) ,--调整前后总价差额合计
[JcwjDiff] = @JcwjDiff --累计实现业态均价与决策文件均价差额
FROM s_TjPlan A
LEFT JOIN dbo.p_Project B ON B.ProjGUID = A.PRojGUID
WHERE A.PlanGUID = @PlanGUID
GO