[MSSQL] Result set direct operation

实际工作中经常碰到占比，比如XX部门超领金额占总金额的占比，离职人员占总人数占比等等。一直都是存取变量，计算结果。今天尝试了下结果集直接进行四则运算

-- =============================================   --Author: <david gong>--Create Date: <2015-9-23>--Description: < material overflow analysis >-- =============================================  alter PROC [dbo]. [Up_excess_material] (@ year as Char(4))  as begin Create table #tmp (Project varchar), month varchar($), amount Decimal (2) default (0));  --declare @year as char (4)--set @year = ' $ 'Declare @yearmonth as CHAR (6) Declare @01 decimal (16,2), @02 decimal (16,2), @03 decimal (16,2), @04 decimal (16,2), @05 Decimal (16,2), @06 decimal (16,2), @07 decimal (16,2), @08 decimal (16,2), @09 decimal (16,2), @10 decimal (16,2), @11 decimal ( 16,2), @12 decimal (16,2)--Define variablesDECLARE @01_wc decimal (16,2), @02_wc decimal (16,2), @03_wc decimal (16,2), @04_wc int,@05_wc Decimal (16,2), @06_wc decimal (16,2), @07_wc decimal (16,2), @08_wc decimal (16,2), @09_wc decimal (16,2), @10_wc decimal (16,2), @11_wc decimal (16,2), @12 _WC Decimal (16,2)--Define the total number of turnover variablesDECLARE @i intSet@i=1Declare@Month Char(2) while @i<= ABEGIN   Set@Month= Right(@i+ -,2)Set@yearmonth [email protected] Year+ Right(@i+ -,2)if(@yearmonth <=convert (Char(6), GETDATE (), the))begin--The total amount of material in the factoryInsert  into#tmp (project, month, amount)Select ' 1. Total material amount '  asProject, @MonthMonthsum(LA013) asAmount fromMoctcInner JoinMocte ontc001=te001 andtc002=te002Inner JoinInvla onte001=la006 andte002=la007 andte003=la008wheretc008=' Si '  andTc001<>' 5402 '  andCONVERT (Char(6), TC003, the) [email protected]--the amount of the super-collarUnion  All        Select ' 2. Excess material amount '  asProject, @MonthMonthsum(LA013) asAmount fromMoctcInner JoinMocte ontc001=te001 andtc002=te002Inner JoinInvla onte001=la006 andte002=la007 andte003=la008wheretc008=' Si '  andtc001=' 5402 '  andCONVERT (Char(6), TC003, the) [Email protected]Union  All--super-collar by Department amountSelectProject, Month,sumAmount asAmount from(Select  Case  whenME002inch(' BL workshop ',' LCM Workshop ') Then ' 3. Production department amount '                     whenME002 =' Development Department '   Then ' 4. Development Department amount '                     whenME002 =' Engineering Department '   Then ' 5. Engineering Department amount '                     whenME002 =' purchasing department '   Then ' 6. Purchasing Department amount '                    ElseME002End   asProject, @MonthMonth, LA013 asAmount fromMoctcInner JoinMocte ontc001=te001 andtc002=te002Inner JoinInvla onte001=la006 andte002=la007 andte003=la008Inner JoinCmsme ontc021=me001wheretc008=' Si '  andtc001=' 5402 '  andCONVERT (Char(6), TC003, the) [email protected]) KGroup  byK. Project, K. MonthEnd   Set@[email protected]+1ENDTotal amount of---materialSelect* into#tempall from#tmp Pivot (MaxAmount forMonthinch([ on],[ Geneva],[Geneva],[Geneva],[ to],[ .],[ -],[ ,],[ the],[Ten],[ One],[ A])) ASelectProject, [ on] asJanuary, [ Geneva] February, [Geneva] March, [Geneva] April, [ to] May, [ .] June, [ -] July, [ ,] August, [ the] September, [Ten] October, [ One] November, [ A] December from#tempallUnion  AllArithmetic the fields corresponding to the two result sets----Select ' 7. Total overflow rate '  asProjectcast(Round (a.[ on]/b.[ on],4) as   Numeric(5,4)),cast(Round (a.[ Geneva]/b.[ Geneva],4) as   Numeric(5,4)),cast(Round (a.[Geneva]/b.[Geneva],4) as   Numeric(5,4)),cast(Round (a.[Geneva]/b.[Geneva],4) as   Numeric(5,4)),cast(Round (a.[ to]/b.[ to],4) as   Numeric(5,4)),cast(Round (a.[ .]/b.[ .],4) as   Numeric(5,4)),cast(Round (a.[ -]/b.[ -],4) as   Numeric(5,4)),cast(Round (a.[ ,]/b.[ ,],4) as   Numeric(5,4)),cast(Round (a.[ the]/b.[ the],4) as   Numeric(5,4)),cast(Round (a.[Ten]/b.[Ten],4) as   Numeric(5,4)),cast(Round (a.[ One]/b.[ One],4) as   Numeric(5,4)),cast(Round (a.[ A]/b.[ A],4) as   Numeric(5,4)) from(Select* from#tempallwhereitem =' 1. Total material amount ') asB, (Select* from#tempallwhereitem =' 2. Excess material amount ') asAUnion  AllSelect ' 8. Production department lead rate '  asProjectcast(Round (a.[ on]/b.[ on],4) as   Numeric(5,4)),cast(Round (a.[ Geneva]/b.[ Geneva],4) as   Numeric(5,4)),cast(Round (a.[Geneva]/b.[Geneva],4) as   Numeric(5,4)),cast(Round (a.[Geneva]/b.[Geneva],4) as   Numeric(5,4)),cast(Round (a.[ to]/b.[ to],4) as   Numeric(5,4)),cast(Round (a.[ .]/b.[ .],4) as   Numeric(5,4)),cast(Round (a.[ -]/b.[ -],4) as   Numeric(5,4)),cast(Round (a.[ ,]/b.[ ,],4) as   Numeric(5,4)),cast(Round (a.[ the]/b.[ the],4) as   Numeric(5,4)),cast(Round (a.[Ten]/b.[Ten],4) as   Numeric(5,4)),cast(Round (a.[ One]/b.[ One],4) as   Numeric(5,4)),cast(Round (a.[ A]/b.[ A],4) as   Numeric(5,4)) from(Select* from#tempallwhereitem =' 1. Total material amount ') asB, (Select* from#tempallwhereitem =' 3. Production department amount ') asAUnion  AllSELECT ' 9. '+[Project], [January], [February], [March], [April], [May], [June], [July], [August], [September], [October] , [November], [December] from[OA]. [dbo]. [super-collar target]whereyear [email protected] YearDrop Table#tmpDrop Table#tempallEnd

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

[MSSQL] Result set direct operation