[Easy to fly] EXP (SUM (LOG field)) practice

When writing to half of the code, you need to count the product of the specified line. Think of a SQL
New function for EXP (SUM (field)) try. The full code is as follows

-- =============================================   --Author: <david gong>--Create Date: <2015-9-11>--Description: < statistics COB QUALITY status table >-- =============================================  ALTER Proc up_proc_cob (@year as Char(4))asbegin Create table #tmpCOB (Project varchar), month varchar(%),% Dec iMAL(8,4) default (0));  --declare @year as char (4)--set @year = ' $ 'Declare @yearmonth as CHAR (6) DECLARE @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--Statistics of binding pass rate in the monthInsert  into#tmpCOB (project, Month, percentage)Select ' 1. Binding pass Rate '  asProject, @MonthMonthcast(Round (sum(Number of tests-bad quantity) *1.0/sum(Number of tests),4) as  Numeric(8,4)) Pass Rate fromProcess COB Binding TestwhereCONVERT (Char(6), date, the) [email protected]--statistics on the appearance rate of the monthInsert  into#tmpCOB (project, Month, percentage)Select ' 2. Appearance pass rate '  asProject, @MonthMonthcast(Round (sum(Number of tests-bad quantity) *1.0/sum(Number of tests),4) as  Numeric(8,4)) Pass Rate fromProcess COB AppearancewhereCONVERT (Char(6), date, the) [email protected]--statistical test pass rateInsert  into#tmpCOB (project, Month, percentage)Select ' 3. Final Test pass rate '  asProject, @MonthMonthcast(Round (sum(Number of tests-bad quantity) *1.0/sum(Number of tests),4) as  Numeric(8,4)) Pass Rate fromProcess COB MeasurementwhereCONVERT (Char(6), date, the) [email protected]--statistical FQC pass rateInsert  into#tmpCOB (project, Month, percentage)Select ' 4.FQC pass rate '  asProject, @MonthMonthcast(Round (sum(Quantity tested-unqualified quantity) *1.0/sum(Number of tests),4) as  Numeric(8,4)) Pass Rate fromProcess Fqc_cob_ InspectionwhereCONVERT (Char(6), date of inspection, the) [Email protected]End   Set@[email protected]+1ENDSelect* into#COB from#tmpCOB Pivot (MaxPercentage forMonthinch([ on],[ Geneva],[Geneva],[Geneva],[ to],[ .],[ -],[ ,],[ the],[Ten],[ One],[ A])) ASelectProject, [ on] January, [ Geneva] February, [Geneva] March, [Geneva] April, [ to] May, [ .] June, [ -] July, [ ,] August, [ the] September, [Ten] October, [ One] November, [ A] December from#COBUnion  All---processing the column product of a specified row/*--method one: Case  whenPivot function Specifies columns after row to columnSelect* from(Select  ' 6. Process one pass rate '  asProject, Month, ROUND (sum(Percent of bindings) *sum(Appearance percent) *sum(Final measurement percentage),4) asPercentage from(SelectProject, Month, Case  whenitem =' 1. Binding pass Rate '  ThenPercentageElse 0 EndPercent binding, Case  whenitem =' 2. Appearance pass rate '  ThenPercentageElse 0 EndAppearance percent, Case  whenitem =' 3. Final Test pass rate '  ThenPercentageElse 0 EndFinal measurement percentage from#tmpCOBwhereProject <>' 4.FQC pass rate ') AGroup  byA. Month) asC Pivot (MaxPercentage forMonthinch([ on],[ Geneva],[Geneva],[Geneva],[ to],[ .],[ -],[ ,],[ the],[Ten],[ One],[ A]) b*/--method two directly using exp (SUM(LOG (field)))SELECT ' 6. Process one pass rate ', ROUND (EXP (SUM(LOG ([ on]))),4) asJanuary, ROUND (EXP (SUM(LOG ([ Geneva]))),4) asFebruary, ROUND (EXP (SUM(LOG ([Geneva]))),4) asMarch, ROUND (EXP (SUM(LOG ([Geneva]))),4) asApril, ROUND (EXP (SUM(LOG ([ to]))),4) asMay, ROUND (EXP (SUM(LOG ([ .]))),4) asJune, ROUND (EXP (SUM(LOG ([ -]))),4) asJuly, ROUND (EXP (SUM(LOG ([ ,]))),4) asAugust, ROUND (EXP (SUM(LOG ([ the]))),4) asSeptember, ROUND (EXP (SUM(LOG ([Ten]))),4) asOctober, ROUND (EXP (SUM(LOG ([ One]))),4) asNovember, ROUND (EXP (SUM(LOG ([ A]))),4) asDecember from#COBwhereProject <>' 4.FQC pass rate 'Union  AllSelect  Case  whenitem =' FQC rate target '  Then ' 5.FQC pass rate target ' Else ' 7. First pass rate target ' End  asProjects, January, February, March, April, May, June, July, August, September, October, November, December fromProcess FQC TargetwhereSeries =' COB '  andyear [email protected] YearDrop Table#tmpCOBDrop Table#COBEnd

Effect

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

[Easy to fly] EXP (SUM (LOG field)) practice