午夜精品福利视频,亚洲激情专区,免费看a网站,aa毛片,亚洲色图激情小说,亚洲一级毛片,免费一级毛片一级毛片aa

sql server: sql script -電腦資料

電腦資料 時間:2019-01-01 我要投稿
【www.stanzs.com - 電腦資料】

   

select ProductGUID,ProductName,ProjectGUID from dbo.Product/*F637A079-E22B-4E50-87E9-000147B1B1F4產(chǎn)品5 4CB304EF-2135-43E7-90D6-B03B75CB491B    C731BDB9-1436-4A23-A4FA-001097DF8218產(chǎn)品4 471C3F21-B725-497C-9383-6ED7C97756D8    4CF14E4B-495F-4344-801F-001D4C731494產(chǎn)品1 0BF32124-1963-4A5A-920C-036B2A0A2186    71344D5D-9994-4DC7-ABF6-00546C11565C產(chǎn)品3 A7E60BCF-1FA0-4D00-AE36-50C61074119F    F182ED1B-DACB-43A0-958B-005C5174429F產(chǎn)品3 645E2033-9CE8-44E3-89E8-F7638E6108CB    76C87E67-E3DE-4D0D-8256-005DADE4118D產(chǎn)品5 F85302F9-31A4-447B-A0CE-9F5586B5AB4C    76F4E739-9EE5-4570-B93E-00620E6954A2產(chǎn)品2 4815811E-9F9C-46B1-AA46-8A98A967F233    494A2EBD-D414-4470-A9A8-006346620755產(chǎn)品5 CD6CA57C-6398-4C4B-91A4-9C5A22C0991D    D8EC93CE-D3E6-48CE-B276-006D7FE1F9FC產(chǎn)品3 94B1FF27-49F7-4196-BA89-88AF71F27C49    C2EB45AB-5C0D-43E1-82F4-00795920FF1D產(chǎn)品4 BCBD7C06-D963-44B6-AF79-550831B1CE53    5BB124D2-6748-4514-992D-009AD0C01DD6產(chǎn)品1 EED22E55-FB42-42AE-9919-8D13BCF47506    29C1840B-E73F-4BF4-BE62-00E8A3EB8D95產(chǎn)品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: 套數(shù)(INT)--SaleArea:面積(MONEY)--SalePrice:單價(MONEY)--SaleAmount:銷售中加(MONEY)select SaleDtlGUID,ProductGUID,YearMonth,SaleNum,SaleArea,SalePrice,SaleAmount,[Year],[Month] from dbo.SaleDtl/*D9023E32-D981-4DB1-82EA-A8296FBF6A8AF637A079-E22B-4E50-87E9-000147B1B1F4    2013-04     7   700.0000    6000.0000   4200000.0000    2013    04  BEDF4CED-0C39-45EA-97BF-7C298C1D8A5BF637A079-E22B-4E50-87E9-000147B1B1F4    2008-11     9   900.0000    5000.0000   4500000.0000    2008    11  63DE92BA-4C08-4FF1-9A68-27CD4293084AF637A079-E22B-4E50-87E9-000147B1B1F4    2014-08     12  1200.0000   7000.0000   8400000.0000    2014    08  BC874228-B13F-4E49-9560-3CD39FF2B8A9F637A079-E22B-4E50-87E9-000147B1B1F4    2011-05     12  12000.0000  7000.0000   84000000.0000   2011    05  164FA80F-3ECF-4E82-86A1-A7F832867028F637A079-E22B-4E50-87E9-000147B1B1F4    2011-04     7   700.0000    6000.0000   4200000.0000    2011    04  7EC32C1E-F34B-4BF1-9BA4-5205C2C322F0F637A079-E22B-4E50-87E9-000147B1B1F4    2010-05     12  12000.0000  7000.0000   84000000.0000   2010    05  278D72A4-F340-4DB9-8083-7BD277EB7C9EF637A079-E22B-4E50-87E9-000147B1B1F4    2013-11     9   900.0000    5000.0000   4500000.0000    2013    11  E70EF3D1-A92C-45C1-A7D3-110A57C93CF0F637A079-E22B-4E50-87E9-000147B1B1F4    2015-08     12  1200.0000   7000.0000   8400000.0000    2015    08  26D1A828-26EE-4B9E-BEEF-A6F0A74CDE9EF637A079-E22B-4E50-87E9-000147B1B1F4    2007-02     8   800.0000    3500.0000   2800000.0000    2007    02  FD59AF4A-447D-4860-B2C2-10B0FD8C7531F637A079-E22B-4E50-87E9-000147B1B1F4    2006-03     5   500.0000    5000.0000   2500000.0000    2006    03  9288807A-C53A-44FA-B9AF-A69E0BF1C4CEF637A079-E22B-4E50-87E9-000147B1B1F4    2006-02     8   800.0000    3500.0000   2800000.0000    2006    02  CF001CD9-BC4D-4FB8-B39E-0FE41D2468A8F637A079-E22B-4E50-87E9-000147B1B1F4    2011-08     12  1200.0000   7000.0000   8400000.0000    2011    08  608070DC-53F0-46E0-B5F8-102106786073F637A079-E22B-4E50-87E9-000147B1B1F4    2012-04     7   700.0000    6000.0000   4200000.0000    2012    04  9869C12C-0896-4C17-B575-3A2C52CBCE13F637A079-E22B-4E50-87E9-000147B1B1F4    2014-10     3   300.0000    5000.0000   1500000.0000    2014    10  705F790B-23F3-4C9D-A9DA-A4B5799D1D56F637A079-E22B-4E50-87E9-000147B1B1F4    2007-01     10  1000.0000   4000.0000   4000000.0000    2007    01  050DA0EE-8862-4F0F-92FE-A521208F09C3F637A079-E22B-4E50-87E9-000147B1B1F4    2009-12     6   600.0000    5000.0000   3000000.0000    2009    12  6E0903D3-204E-4E3E-A737-39FCF7B434E8F637A079-E22B-4E50-87E9-000147B1B1F4    2010-08     12  1200.0000   7000.0000   8400000.0000    2010    08  22A0FC81-77C1-4B37-9DA8-24166AF7A490F637A079-E22B-4E50-87E9-000147B1B1F4    2011-07     8   800.0000    8000.0000   6400000.0000    2011    07  DDBDE42E-4986-44AC-BE77-8EC381356820F637A079-E22B-4E50-87E9-000147B1B1F4    2014-11     9   900.0000    5000.0000   4500000.0000    2014    11  6A7B23A3-C48B-4519-986D-3901CCFA50AEF637A079-E22B-4E50-87E9-000147B1B1F4    2010-04     7   700.0000    6000.0000   4200000.0000    2010    04  1BA93EC8-CB89-4DA5-B27C-239A43099214F637A079-E22B-4E50-87E9-000147B1B1F4    2009-04     7   700.0000    6000.0000   4200000.0000    2009    04 *//*臨時表說明#product:用項目過濾后,將“合計”作為一個產(chǎn)品的集合#TempAllSaleDtl:通過項目過濾后的銷售明細(xì),所有月的#ProductSaleArea:各個產(chǎn)品的總面積,用于計算比例#TempSaleDtl:通過日期過濾,且加工過后的銷售明細(xì),包括增加累積列,以前年度、以后年度、項目合計的記錄#tempSaleDtl2:列轉(zhuǎn)行后的數(shù)據(jù)集#tempSaleDtl3:行轉(zhuǎn)列后的數(shù)據(jù)集*/DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份--查找該項目的所有產(chǎn)品放進(jìn)臨時表#product,這里將“合計”作為一個產(chǎn)品的集合也插入產(chǎn)品表#product:select ProductGUID,ProductName,ProjectGUID,ProductCode into #productfrom(select ProductGUID,ProductName,ProjectGUID,ProductName as ProductCode from Productwhere ProjectGUID=@ProjectGUID union all select '00000000-0000-0000-0000-000000000000','合計',@ProjectGUID,'00' as ProductCode) aGO--查找該項目的所有產(chǎn)品的銷售明細(xì)放進(jìn)臨時表#TempAllSaleDtl,以作備用:DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice INTO #TempAllSaleDtl FROM dbo.SaleDtlWHERE ProductGUID IN (SELECT ProductGUID FROM dbo.Product WHERE ProjectGUID=@ProjectGUID)go--根據(jù)#TempAllSaleDtl現(xiàn)有數(shù)據(jù)統(tǒng)計,向#TempAllSaleDtl添加總合計記--根據(jù)現(xiàn)有數(shù)據(jù)統(tǒng)計,向#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 YearMonthgo--從臨時表#TempAllSaleDtl 中查找指定年的銷售明細(xì)放進(jìn)臨時表#TempSaleDtl ,注意 這個時候就已經(jīng)包含了 “合計”產(chǎn)品00的數(shù)據(jù):--查找某年的銷售明細(xì):#TempSaleDtlDECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,SalePrice AS ljSaleArea,SalePrice AS blSaleArea,SalePrice AS ljSaleAmountINTO #TempSaleDtl FROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Yeargo--從臨時表#TempAllSaleDtl 中查找指定年的銷售明細(xì)放進(jìn)臨時表#TempSaleDtl ,注意 這個時候就已經(jīng)包含了 “合計”產(chǎn)品00的數(shù)據(jù):--查找某年的銷售明細(xì):#TempSaleDtlDECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,SalePrice AS ljSaleArea,SalePrice AS blSaleArea,SalePrice AS ljSaleAmount INTO #TempSaleDtlFROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Yeargo--從#TempAllSaleDtl 中統(tǒng)計項目各個產(chǎn)品的總銷售面積放入表:#ProductSaleArea,主要用作計算 項目累計銷售面積比例--獲取項目各個產(chǎn)品的總銷售面積:#ProductSaleAreaSELECT ProductGUID,SUM(SaleArea) AS all_SaleArea INTO #ProductSaleArea FROM #TempAllSaleDtl GROUP BY ProductGUIDgo--從表#TempSaleDtl 統(tǒng)計當(dāng)前年度合計列,各產(chǎn)品的所有面積、金額、均價總合計 放入表#TempSaleDtl,注意這里 SUM(SaleAmount)/SUM(SaleArea) 計算銷售單價:--添加2011合計列的記錄(本年度的各產(chǎn)品的所有面積、金額、均價總合計)DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份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 ProductGUIDGO--從表#TempAllSaleDtl 統(tǒng)計以前年度的各產(chǎn)品的所有面積、金額、均價總合計 操作與上一步類似 放入表#TempSaleDtl--以前年度列記錄(本年度以前的各產(chǎn)品的所有面積、金額、均價總合計 操作與上一步類似)DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份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 #TempAllSaleDtlwhere YearMonth=@Year+'-00'group by ProductGUIDGO--從表#TempAllSaleDtl 統(tǒng)計以后年度的各產(chǎn)品的所有面積、金額、均價總合計 操作與上一步類似 放入表#TempSaleDtl--以后年度列記錄(本年度以后的各產(chǎn)品的所有面積、金額、均價總合計 操作與上一步類似)DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份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,0from #TempAllSaleDtlwhere YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00'group by ProductGUIDgo--從表#TempAllSaleDtl 統(tǒng)計各產(chǎn)品取所有的合計 放入表#TempSaleDtl--項目合計列記錄(各產(chǎn)品取所有的合計,

sql server: sql script

。與上面的區(qū)別在于沒有添加 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 ProductGUIDGO--從表#TempSaleDtl 與 #TempAllSaleDtl統(tǒng)計累積銷售面積、累積銷售面積比例,累積銷售金額 更新表#TempSaleDtl--更新銷售明細(xì)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 mINNER JOIN #TempSaleDtl n ON m.YearMonth=n.YearMonth AND m.ProductGUID=n.ProductGUID GROUP BY n.ProductGUID,n.YearMonth) b ON #TempSaleDtl.ProductGUID=b.ProductGUIDAND #TempSaleDtl.YearMonth=b.YearMonth LEFT JOIN #ProductSaleArea c ON c.ProductGUID=#TempSaleDtl.ProductGUIDGO--從表#TempSaleDtl 列轉(zhuǎn)行,轉(zhuǎn)換后的表只有 產(chǎn)品、統(tǒng)計類型、日期,值4列;(每個產(chǎn)品對應(yīng)的0-12、13 月對應(yīng)的值) 放入表#tempSaleDtl2--列轉(zhuǎn)行,轉(zhuǎn)換后的表只有 產(chǎn)品、統(tǒng)計類型、日期,值4列;(每個產(chǎn)品對應(yīng)的0-12、13 月對應(yīng)的值)SELECT * INTO #tempSaleDtl2 FROM (SELECT ProductGUID,'銷售套數(shù)' AS type,'01' AS typecode,YearMonth,MAX(SaleNum) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonthUNION ALL SELECT ProductGUID,'銷售面積' AS type,'02' AS typecode,YearMonth,MAX(SaleArea) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonthUNION ALL SELECT ProductGUID,'銷售均價' AS type,'03' AS typecode,YearMonth,MAX(SalePrice) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonthUNION ALL SELECT ProductGUID,'銷售金額' AS type,'04' AS typecode,YearMonth,MAX(SaleAmount) AS val FROM #TempSaleDtl GROUP BY ProductGUID,YearMonthUNION ALL SELECT ProductGUID,'累計銷售面積' AS type, '05' AS typecode,YearMonth,SUM(ljSaleArea) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonthUNION ALL SELECT ProductGUID,'累計銷售面積比例' AS type, '06' AS typecode,YearMonth,SUM(blSaleArea) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonthUNION ALL SELECT ProductGUID,'累計銷售金額' AS type, '07' AS typecode,YearMonth,SUM(ljSaleAmount) FROM #TempSaleDtl GROUP BY ProductGUID,YearMonth) tGO--從表#tempSaleDtl2行轉(zhuǎn)列,按類型聚合 求出每個產(chǎn)品每個類型(面積、金額……)的合計 放入表#tempSaleDtl3]DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份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 #tempSaleDtl3FROM #tempSaleDtl2 GROUP BY ProductGUID,type,typecode ORDER BY ProductGUID,typecodeGOselect * from #tempSaleDtl3GO--從Project表中加入項目數(shù)據(jù)DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份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=@ProjectGUIDGOselect 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.ProjectGUIDGO--從產(chǎn)品表和Project表、#tempSaleDtl3中加入類型行數(shù)據(jù) 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.ProjectGUIDGO--從Project表中加入項目數(shù)據(jù)DECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查詢的項目DECLARE @Year CHAR(4)SET @Year='2011'--要統(tǒng)一的年份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--從產(chǎn)品表和Project表中加入合計行數(shù)據(jù) 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.ProjectGUIDunion ALL --從產(chǎn)品表和Project表、#tempSaleDtl3中加入類型行數(shù)據(jù) 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 orderCodeGOdrop table #productdrop table #TempAllSaleDtlDROP TABLE #TempSaleDtlDROP TABLE #tempSaleDtl2DROP TABLE #tempSaleDtl3DROP TABLE #ProductSaleAreaselect * from dbo.SaleDtlDBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE--SET STATISTICS IO ON--SET STATISTICS TIME ONDECLARE @ProjectGUID UNIQUEIDENTIFIERSET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'DECLARE @Year CHAR(4)SET @Year='2011'/*臨時表說明#product:用項目過濾后,將“合計”作為一個產(chǎn)品的集合#TempAllSaleDtl:通過項目過濾后的銷售明細(xì),所有月的#ProductSaleArea:各個產(chǎn)品的總面積,用于計算比例#TempSaleDtl:通過日期過濾,且加工過后的銷售明細(xì),包括增加累積列,以前年度、以后年度、項目合計的記錄#tempSaleDtl2:列轉(zhuǎn)行后的數(shù)據(jù)集#tempSaleDtl3:行轉(zhuǎn)列后的數(shù)據(jù)集*/select ProductGUID,ProductName,ProjectGUID,ProductCode into #productfrom(select ProductGUID,ProductName,ProjectGUID,ProductName as ProductCodefrom Product where ProjectGUID=@ProjectGUID union all select '00000000-0000-0000-0000-000000000000','合計',@ProjectGUID,'00' as ProductCode) a--SELECT * FROM #product----查找項目所有產(chǎn)品的銷售明細(xì):#TempAllSaleDtlSELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice INTO #TempAllSaleDtlFROM dbo.SaleDtl WHERE ProductGUID IN ( SELECT ProductGUID FROM dbo.Product WHERE ProjectGUID=@ProjectGUID)--SELECT * FROM #TempAllSaleDtl--ORDER BY ProductGUID,YearMonth--根據(jù)現(xiàn)有數(shù)據(jù)統(tǒng)計,向#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--查找某年的銷售明細(xì):#TempSaleDtlSELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice, SalePrice AS ljSaleArea, SalePrice AS blSaleArea, SalePrice AS ljSaleAmount INTO #TempSaleDtlFROM #TempAllSaleDtl WHERE LEFT([YearMonth],4)=@Year--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--獲取項目各個產(chǎn)品的總銷售面積:#ProductSaleAreaSELECT ProductGUID,SUM(SaleArea) AS all_SaleArea INTO #ProductSaleArea FROM #TempAllSaleDtl GROUP BY ProductGUID--SELECT * FROM #ProductSaleArea--ORDER BY ProductGUID--添加2011合計列的記錄(本年度的各產(chǎn)品的所有面積、金額、均價總合計)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--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--以前年度列記錄(本年度以前的各產(chǎn)品的所有面積、金額、均價總合計 操作與上一步類似)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,0from #TempAllSaleDtl where YearMonth=@Year+'-00'group by ProductGUID--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--以后年度列記錄(本年度以后的各產(chǎn)品的所有面積、金額、均價總合計 操作與上一步類似)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 #TempAllSaleDtlwhere YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00'group by ProductGUID--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--項目合計列記錄(各產(chǎn)品取所有的合計。與上面的區(qū)別在于沒有添加 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--更新銷售明細(xì)TempSaleDtl的累積銷售面積、累積銷售面積比例,累積銷售金額UPDATE #TempSaleDtl SET ljSaleArea=b.sum_SaleArea, ljSaleAmount=b.sum_SaleAmount, blSaleArea=b.sum_SaleArea/c.all_SaleAreaFROM #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.YearMonthLEFT JOIN #ProductSaleArea c ON c.ProductGUID=#TempSaleDtl.ProductGUID--SELECT * FROM #TempSaleDtl--ORDER BY ProductGUID,YearMonth--列轉(zhuǎn)行,轉(zhuǎn)換后的表只有 產(chǎn)品、統(tǒng)計類型、日期,值4列;(每個產(chǎn)品對應(yīng)的0-12、13 月對應(yīng)的值)SELECT * INTO #tempSaleDtl2 FROM ( SELECT ProductGUID,'銷售套數(shù)' 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--行轉(zhuǎn)列,按日期聚合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 #tempSaleDtl2GROUP BY ProductGUID,type,typecode ORDER BY ProductGUID,typecode--SELECT * FROM #tempSaleDtl3--ORDER BY ProductGUID,typecode--從Project表中加入項目數(shù)據(jù)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--從產(chǎn)品表和Project表中加入合計行數(shù)據(jù) 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 --從產(chǎn)品表和Project表、#tempSaleDtl3中加入類型行數(shù)據(jù) 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 orderCodedrop table #productdrop table #TempAllSaleDtlDROP TABLE #TempSaleDtlDROP TABLE #tempSaleDtl2DROP TABLE #tempSaleDtl3DROP TABLE #ProductSaleAreaGO

   

/*開盤調(diào)價報告-本次開盤產(chǎn)品列表*/ALTER  PROC usp_s_KpTjReport_KpProduct    (      @PlanGUID UNIQUEIDENTIFIER ,  --開盤調(diào)價方案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         --項目名稱(用于分區(qū)全名稱匹配)    DECLARE @ProjName VARCHAR(400)         SELECT @ProjName=ProjName    FROM dbo.p_Project    WHERE ProjGUID=@ProjGUID         --SELECT @ProjAnalysisGUID    -------------------------------------------------------------------------------------------------------------------------------------------    --2. 查詢出要求格式的分區(qū)和業(yè)態(tài)名稱,以及列表計算需要的數(shù)據(jù)    -------------------------------------------------------------------------------------------------------------------------------------------    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--試算房間所屬產(chǎn)品 --可能有多條        LEFT JOIN dbo.s_Area2Product D1 ON D1.Area2ProductTypeCode=D.ParentCode AND D1.ProjAnalysisGUID=@ProjAnalysisGUID--產(chǎn)品所屬分區(qū)        LEFT JOIN dbo.s_Area2Product D2 ON D2.Area2ProductTypeCode=D1.ParentCode AND D2.ProjAnalysisGUID=@ProjAnalysisGUID--產(chǎn)品所屬分區(qū)上級分區(qū)        LEFT JOIN dbo.s_Area2Product D3 ON D3.Area2ProductTypeCode=D2.ParentCode AND D3.ProjAnalysisGUID=@ProjAnalysisGUID--產(chǎn)品所屬分區(qū)上級分區(qū)上級分區(qū)        INNER JOIN dbo.p_Building E ON E.BldGUID=A.BldGUID--試算房間樓棟        INNER JOIN dbo.s_TjPlan F ON F.PlanGUID=@PlanGUID--開盤調(diào)價計劃        LEFT JOIN dbo.p_Building G ON G.IsBld=0 AND G.ParentCode+'.'+G.BldCode=E.ParentCode AND B.ProjGUID=@ProjGUID--試算房間樓棟所屬區(qū)域    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. 按業(yè)態(tài)計算指標(biāo)    -------------------------------------------------------------------------------------------------------------------------------------------    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. 聯(lián)合查詢出列表展現(xiàn)結(jié)果    -------------------------------------------------------------------------------------------------------------------------------------------    SELECT  AreaName=A.AreaName ,--分區(qū)            ProductName ,--業(yè)態(tài)            RoomCount ,--套數(shù)            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 --當(dāng)前分區(qū)下產(chǎn)品個數(shù)(用于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--加入統(tǒng)計每個分區(qū)的產(chǎn)品個數(shù)    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 #BGO

   

/*開盤調(diào)價方案審批-工作流域取數(shù)*/ALTER  PROC usp_s_KpTjReport_WF    (      @PlanGUID UNIQUEIDENTIFIER    --開盤調(diào)價方案GUID    )AS    SET NOCOUNT ON;    DECLARE @JcwjDiff DECIMAL(18,0)--累計實現(xiàn)業(yè)態(tài)均價與決策文件均價差額(當(dāng)前未開票或未匹配業(yè)態(tài)則0,若任一業(yè)態(tài)均價小于0則為-1,否則為1)    DECLARE @ProjGUID UNIQUEIDENTIFIER--當(dāng)前項目GUID    DECLARE @ProjAnalysisGUID UNIQUEIDENTIFIER--當(dāng)前最新審核項目分解版本GUID    DECLARE @PlanType VARCHAR(20)--當(dāng)前方案類型    DECLARE @TjDiff DECIMAL(18,0)--調(diào)整前后總價差額合計(萬元)    DECLARE @TjTotal DECIMAL(18,0)--調(diào)價房間總價(萬元)    DECLARE @TjCount BIGINT--調(diào)價房間套數(shù)         -------------------------------------------------------------------------------------------------------------------------------------------    --1. 查詢累計實現(xiàn)業(yè)態(tài)均價與決策文件均價差額    --1.1 獲取當(dāng)前項目和最新審核項目分解版本    -------------------------------------------------------------------------------------------------------------------------------------------    SET @JcwjDiff=0 --默認(rèn)值         --項目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         --項目名稱(用于分區(qū)全名稱匹配)    DECLARE @ProjName VARCHAR(400)         SELECT @ProjName=ProjName    FROM dbo.p_Project    WHERE ProjGUID=@ProjGUID    --SELECT @ProjGUID AS 項目,@PlanType AS 類型,@ProjAnalysisGUID AS 項目分解         IF( @PlanType='調(diào)價報告')    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),--認(rèn)購合同附屬房產(chǎn)的面積                [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            --對應(yīng)業(yè)態(tài)分區(qū)            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--產(chǎn)品所屬分區(qū)            LEFT JOIN dbo.s_Area2Product D2 ON D2.Area2ProductTypeCode=D1.ParentCode AND D2.ProjAnalysisGUID=@ProjAnalysisGUID--產(chǎn)品所屬分區(qū)上級分區(qū)            LEFT JOIN dbo.s_Area2Product D3 ON D3.Area2ProductTypeCode=D2.ParentCode AND D3.ProjAnalysisGUID=@ProjAnalysisGUID--產(chǎn)品所屬分區(qū)上級分區(qū)上級分區(qū)            ----對應(yīng)合同訂單            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='激活'            --合同訂單附屬房產(chǎn)(統(tǒng)計銷售面積)            LEFT JOIN dbo.s_OCAttachRoom O ON O.RoomGUID=A.RoomGUID AND O.SaleGUID IN(E.OrderGUID,F.ContractGUID)--一個房間不可能同時被認(rèn)購和簽約            ----對應(yīng)開盤計劃(取開盤日期)            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)--對應(yīng)開盤計劃的試算結(jié)果            LEFT JOIN dbo.s_TjPlan H ON H.PlanGUID=G.PlanGUID--對應(yīng)的開盤計劃            --對應(yīng)開盤或調(diào)價試算結(jié)果(當(dāng)房間做多個調(diào)價報告時,取最新制定的方案試算記錄)            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--樓棟所屬區(qū)域        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))--當(dāng)未匹配業(yè)態(tài),則決策文件均價差額為0        BEGIN            -------------------------------------------------------------------------------------------------------------------------------------------            --1.3 按業(yè)態(tài)計算指標(biāo),獲得預(yù)計整體可實現(xiàn)折實均價與決策文件差額            -------------------------------------------------------------------------------------------------------------------------------------------            SELECT A.ProductCode                --預(yù)計整體可實現(xiàn)與決策文件差額                ,[Diff]=                        --'折實均價(元/平米)'                        CASE WHEN                        (                            ISNULL(SUM(CASE WHEN A.RoomState IN ('認(rèn)購','簽約') 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 ('認(rèn)購','簽約') THEN A.[RoomBldArea] END),0)                            +ISNULL(                                SUM(                                CASE WHEN                                    ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0                                THEN                                    --如果所有樓棟都生成房間,則統(tǒng)計房間建筑面積之和                                    --如果部分樓棟沒有生成房間,則顯示生成房間樓棟建筑面積之和+樓棟預(yù)計銷售面積                                    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 ('認(rèn)購','簽約') 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 ('認(rèn)購','簽約') 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)--未到推盤日期已經(jīng)定價房間  的房間總價                                +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 ('認(rèn)購','簽約') 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 ('認(rèn)購','簽約') THEN A.[RoomBldArea] END),0)                            +ISNULL(                                SUM(                                CASE WHEN                                    ISNULL(DATEDIFF(DAY,GETDATE(),A.[KpDate]),1)>0                                THEN                                    --如果所有樓棟都生成房間,則統(tǒng)計房間建筑面積之和                                    --如果部分樓棟沒有生成房間,則顯示生成房間樓棟建筑面積之和+樓棟預(yù)計銷售面積                                    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 計算累計實現(xiàn)業(yè)態(tài)均價與決策文件均價差額            --1.5 當(dāng)其中有一個差額小于0,則均價差額返回-1            --1.6 當(dāng)所有差額都大于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. 查詢調(diào)整前后總價差額合計    --2.1 最近一次執(zhí)行的開盤或調(diào)價方案    --2.1 如果是“開盤報告”或之前沒有報告則不計算    -------------------------------------------------------------------------------------------------------------------------------------------        --獲取本次調(diào)價的審核日期(用于查看歷史記錄時篩選)        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 --房間對應(yīng)的之前最近審核過的一次開盤調(diào)價方案試算                       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--產(chǎn)品所屬分區(qū)            LEFT JOIN dbo.s_Area2Product E2 ON E2.Area2ProductTypeCode=E1.ParentCode AND E2.ProjAnalysisGUID=@ProjAnalysisGUID----產(chǎn)品所屬分區(qū)上級分區(qū)            LEFT JOIN dbo.s_Area2Product E3 ON E3.Area2ProductTypeCode=E2.ParentCode AND E3.ProjAnalysisGUID=@ProjAnalysisGUID--產(chǎn)品所屬分區(qū)上級分區(qū)上級分區(qū)            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--樓棟所屬區(qū)域        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 按業(yè)態(tài)統(tǒng)計總價差額        -------------------------------------------------------------------------------------------------------------------------------------------        SELECT    [Diff] = CAST(ISNULL(SUM(A.NewSaleTotal*A.NewZhDiscntRate),0)/10000 - ISNULL(SUM(A.OldSaleTotal*A.OldZhDiscntRate),0)/10000 AS DECIMAL(18, 0))                --差額=折實總價(調(diào)整后)-折實總價(調(diào)整前)        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. 輸出開盤調(diào)價審批工作流相關(guān)域數(shù)據(jù)    -------------------------------------------------------------------------------------------------------------------------------------------    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 ,--調(diào)價房間總價            [TjCount] = @TjCount ,--調(diào)價房間套數(shù)            [TjDiff] = ISNULL(CASE WHEN A.PlanType='開盤報告' THEN @TjTotal                                   ELSE @TjDiff                              END, 0) ,--調(diào)整前后總價差額合計            [JcwjDiff] = @JcwjDiff --累計實現(xiàn)業(yè)態(tài)均價與決策文件均價差額    FROM    s_TjPlan A            LEFT JOIN dbo.p_Project B ON B.ProjGUID = A.PRojGUID    WHERE   A.PlanGUID = @PlanGUIDGOou save the job.

最新文章