Classification summary + cross-table processing without regular custom segments

/ * - Originally: http://community.9cbs.net/expert/topic/3845/3845290.xml? Temp = .3689386 - * /

- Test data Create Table TB (Number INT, Nature Varchar (10), Quantity INT, Indicators 1 Decimal (10, 1), Indicator 2 Decimal) Insert TB SELECT 1, '00 ', 10, 1.1, 10UNION All Select 2 , '01 ', 20, 1.2, 20Union All Select 3, '00', 30, 1.5, 10Union All Select 4, '01 ', 40, 1.9, 35UNION All SELECT 5, '00', 40, 1.2, 20

/ * - Treatment requirements

Requirements result:

A Range Properties (00) Nature (01) --------------------------- -------------------- Indicators 1 <1.0 .00.00 1.0-1.29 .63 .63 1.3-1.59 .38 .38 1.9-1.99.00.00 > = 2 .00.00 Indicator 1 Average 1.27 1.55 Indicator 2 <10.00.00 10-31 1.00 1.00 31-50 .00.00> = 50.00 .00 Indicator 2 Average 13.33 27.50 Total: 80.00 60.00 --------------------------------------------------- -----------------

Category Description:

Range Nature (00) Nature (01) Indicator 1 <1.0 0 0.0-1.29 (10 40) / (10 30) 1.3-1.59 30 / (10 30 40) 0 1.6-1.99 0 40 / (20 40)> = 2 0 0 Indicator 1 Average: (1.1 1.9) / 2 Indicator 2 <10 0 0 10-30 (10 30 40) / (10 30) 20 / (20 40) 31-50 0 40 / (20 40)> = 50 0 0 Indicator 2 Average: (10 10 20) / 3 ( 20 35) / 2

Total number: 10 30 40 20 40 - * / GO

- Query processing Select A, Range, [Nature (00)], [SELECT A = Case A.id when 1 Then 'Indicator 1' When 21 Then 'Indicator 2' Else 'End, Range = a.lb, [Nature (00)] = CAST (Case When Ba> 0 Then ISNULL (AA * 1. / BA, 0) Else 0 End As Decimal (10, 2)), [Nature (01)] = CAST (Case When Ba> 0 Then Inull (aa * 1. / ba, 0) ELSE 0 END As Decimal (10, 2)), A.IDFROM (SELECT B.ID, B.LB, A = SUM (Case a. Nature WHEN '00' TEN A. Quantity end), B = Sum (Case a. Nature When '01' TEN A. Quantity End) from TB A Right Join (SELECT ID = 1, LB = '<1.0', A = NULL, B = 1.0 Union All Select ID = 2, LB = '1.0-1.29', a = 1.0, b = 1.3 Union All Select ID = 3, LB = '1.3-1.59', A = 1.3, B = 1.9 Union All Select ID = 4, LB = '1.9-1.99', A = 1.9, B = 2.0 Union All Select ID = 5, LB = '> = 2', A = 2.0, B = NULL) B ON A. Indicators 1> = ISNULL (BA, A. Indicator 1) And a. Indicator 1 = ISNULL (BA, A. Indicator 2) and a. Indicator 2

, Cast (isnull (Case Nature When '00' THEN Nature End)> 0 THEN SUM (Case Nature When '00' Then Index 1 End) * 1. / Count (Case Nature When '00' THEN Nature End ELSE 0 end, 0) As Decimal (10, 2)), Cast (isnull (Case When Count (Case Nature When '01' Then Nature End)> 0 THEN SUM (Case Nature WHEN '01' THEN Indicator 1 End) * 1. / count (Case Nature When '01' THEN Nature End) ELSE 0 End, 0) As Decimal (10, 2)), ID = 6From Tbunion AllSelect 'Indicator 2 Average', '', Cast (ISNULL Case When Count (Case Nature When '00' THEN Nature End)> 0 THEN SUM (Case Nature When '00' THEN Nature 2 End) * 1. / Count (Case Nature When '00' THEN Nature ELSE 0 END, 0) As Decimal (10, 2)), Cast (isnull (Case When Count (Case Nature When '01' THEN Nature End)> 0 THEN SUM (Case Nature When '01' Then Indicator 2 End) * 1. / COUNT (Case Properties WHEN '01' THEN Nature END) ELSE 0 End, 0) As Decimal (10, 2)), ID = 26FROM TBUNION ALLSELECT 'Number:', '', ISNULL (SUM (Case Nature When '00' THEN number END, 0), ISNULL (SUM (Case Nature When '01' THEN End), 0), ID = 30FROM TB) A ORDER BY IDGO - Delete Test DROP TABLE TB