WITH [ Pivot ] AS (

SELECT

LSC

, ISNULL ( [ AK ] , 0 ) AS 'AK'

, ISNULL ( [ AL ] , 0 ) AS 'AL'

, ISNULL ( [ AR ] , 0 ) AS 'AR'

, ISNULL ( [ AZ ] , 0 ) AS 'AZ'

, ISNULL ( [ CA ] , 0 ) AS 'CA'

, ISNULL ( [ CO ] , 0 ) AS 'CO'

, ISNULL ( [ CT ] , 0 ) AS 'CT'

, ISNULL ( [ DC ] , 0 ) AS 'DC'

, ISNULL ( [ DE ] , 0 ) AS 'DE'

, ISNULL ( [ FL ] , 0 ) AS 'FL'

, ISNULL ( [ GA ] , 0 ) AS 'GA'

, ISNULL ( [ HI ] , 0 ) AS 'HI'

, ISNULL ( [ IA ] , 0 ) AS 'IA'

, ISNULL ( [ ID ] , 0 ) AS 'ID'

, ISNULL ( [ IL ] , 0 ) AS 'IL'

, ISNULL ( [ IN ] , 0 ) AS 'IN'

, ISNULL ( [ KS ] , 0 ) AS 'KS'

, ISNULL ( [ KY ] , 0 ) AS 'KY'

, ISNULL ( [ LA ] , 0 ) AS 'LA'

, ISNULL ( [ MD ] , 0 ) AS 'MD'

, ISNULL ( [ ME ] , 0 ) AS 'ME'

, ISNULL ( [ MI ] , 0 ) AS 'MI'

, ISNULL ( [ MN ] , 0 ) AS 'MN'

, ISNULL ( [ MO ] , 0 ) AS 'MO'

, ISNULL ( [ MS ] , 0 ) AS 'MS'

, ISNULL ( [ NC ] , 0 ) AS 'NC'

, ISNULL ( [ ND ] , 0 ) AS 'ND'

, ISNULL ( [ NE ] , 0 ) AS 'NE'

, ISNULL ( [ NH ] , 0 ) AS 'NH'

, ISNULL ( [ NJ ] , 0 ) AS 'NJ'

, ISNULL ( [ NM ] , 0 ) AS 'NM'

, ISNULL ( [ NV ] , 0 ) AS 'NV'

, ISNULL ( [ OH ] , 0 ) AS 'OH'

, ISNULL ( [ OK ] , 0 ) AS 'OK'

, ISNULL ( [ OR ] , 0 ) AS 'OR'

, ISNULL ( [ PA ] , 0 ) AS 'PA'

, ISNULL ( [ RI ] , 0 ) AS 'RI'

, ISNULL ( [ SC ] , 0 ) AS 'SC'

, ISNULL ( [ SD ] , 0 ) AS 'SD'

, ISNULL ( [ TN ] , 0 ) AS 'TN'

, ISNULL ( [ TX ] , 0 ) AS 'TX'

, ISNULL ( [ UT ] , 0 ) AS 'UT'

, ISNULL ( [ VA ] , 0 ) AS 'VA'

, ISNULL ( [ VT ] , 0 ) AS 'VT'

, ISNULL ( [ WA ] , 0 ) AS 'WA'

, ISNULL ( [ WI ] , 0 ) AS 'WI'

, ISNULL ( [ WV ] , 0 ) AS 'WV'

, ISNULL ( [ WY ] , 0 ) AS 'WY'

, SUM ( ISNULL ( [ AK ] , 0 ) + ISNULL ( [ AL ] , 0 ) + ISNULL ( [ AR ] , 0 ) + ISNULL ( [ AZ ] , 0 ) + ISNULL ( [ CA ] , 0 ) + ISNULL ( [ CO ] , 0 ) + ISNULL ( [ CT ] , 0 ) + ISNULL ( [ DC ] , 0 ) + ISNULL ( [ DE ] , 0 ) + ISNULL ( [ FL ] , 0 ) + ISNULL ( [ GA ] , 0 ) + ISNULL ( [ HI ] , 0 ) + ISNULL ( [ IA ] , 0 ) + ISNULL ( [ ID ] , 0 ) + ISNULL ( [ IL ] , 0 ) + ISNULL ( [ IN ] , 0 ) + ISNULL ( [ KS ] , 0 ) + ISNULL ( [ KY ] , 0 ) + ISNULL ( [ LA ] , 0 ) + ISNULL ( [ MD ] , 0 ) + ISNULL ( [ ME ] , 0 ) + ISNULL ( [ MI ] , 0 ) + ISNULL ( [ MN ] , 0 ) + ISNULL ( [ MO ] , 0 ) + ISNULL ( [ MS ] , 0 ) + ISNULL ( [ NC ] , 0 ) + ISNULL ( [ ND ] , 0 ) + ISNULL ( [ NE ] , 0 ) + ISNULL ( [ NH ] , 0 ) + ISNULL ( [ NJ ] , 0 ) + ISNULL ( [ NM ] , 0 ) + ISNULL ( [ NV ] , 0 ) + ISNULL ( [ OH ] , 0 ) + ISNULL ( [ OK ] , 0 ) + ISNULL ( [ OR ] , 0 ) + ISNULL ( [ PA ] , 0 ) + ISNULL ( [ RI ] , 0 ) + ISNULL ( [ SC ] , 0 ) + ISNULL ( [ SD ] , 0 ) + ISNULL ( [ TN ] , 0 ) + ISNULL ( [ TX ] , 0 ) + ISNULL ( [ UT ] , 0 ) + ISNULL ( [ VA ] , 0 ) + ISNULL ( [ VT ] , 0 ) + ISNULL ( [ WA ] , 0 ) + ISNULL ( [ WI ] , 0 ) + ISNULL ( [ WV ] , 0 ) + ISNULL ( [ WY ] , 0 ) ) AS 'Total'

FROM (

SELECT

[ IssueState ]

, [ SourceDesc ] AS 'LSC'

, SUM ( [ P ] ) AS 'Production'

FROM (

SELECT

[ PolicyNo ]

, SUM ( [ SetProduction ] ) AS 'P'

FROM [ BI_Reporting_RO ] . [ Production ] . [ PremiumProduction ] A

WHERE [ LeadSourceCode ] LIKE '%X'

GROUP BY [ PolicyNo ]

HAVING SUM ( [ SetCount ] ) <> 0

) A

LEFT JOIN [ BI_Reporting_RO ] . [ LSP ] . Contracts B

ON B . [ PolicyNo ] = A . [ PolicyNo ]

LEFT JOIN [ BI_Reporting_RO ] . [ Params ] . LeadSources C

ON C . [ SourceCode ] = B . [ LeadSourceCode ]

WHERE

[ IssueState ] IS NOT NULL

AND [ LeadSourceCode ] IS NOT NULL

GROUP BY

[ IssueState ]

, [ SourceDesc ]

) A

PIVOT ( MAX ( [ Production ] ) FOR [ IssueState ] IN ( [ AK ] , [ AL ] , [ AR ] , [ AZ ] , [ CA ] , [ CO ] , [ CT ] , [ DC ] , [ DE ] , [ FL ] , [ GA ] , [ HI ] , [ IA ] , [ ID ] , [ IL ] , [ IN ] , [ KS ] , [ KY ] , [ LA ] , [ MD ] , [ ME ] , [ MI ] , [ MN ] , [ MO ] , [ MS ] , [ NC ] , [ ND ] , [ NE ] , [ NH ] , [ NJ ] , [ NM ] , [ NV ] , [ OH ] , [ OK ] , [ OR ] , [ PA ] , [ RI ] , [ SC ] , [ SD ] , [ TN ] , [ TX ] , [ UT ] , [ VA ] , [ VT ] , [ WA ] , [ WI ] , [ WV ] , [ WY ] ) ) AS X

GROUP BY

LSC

, ISNULL ( [ AK ] , 0 )

, ISNULL ( [ AL ] , 0 )

, ISNULL ( [ AR ] , 0 )

, ISNULL ( [ AZ ] , 0 )

, ISNULL ( [ CA ] , 0 )

, ISNULL ( [ CO ] , 0 )

, ISNULL ( [ CT ] , 0 )

, ISNULL ( [ DC ] , 0 )

, ISNULL ( [ DE ] , 0 )

, ISNULL ( [ FL ] , 0 )

, ISNULL ( [ GA ] , 0 )

, ISNULL ( [ HI ] , 0 )

, ISNULL ( [ IA ] , 0 )

, ISNULL ( [ ID ] , 0 )

, ISNULL ( [ IL ] , 0 )

, ISNULL ( [ IN ] , 0 )

, ISNULL ( [ KS ] , 0 )

, ISNULL ( [ KY ] , 0 )

, ISNULL ( [ LA ] , 0 )

, ISNULL ( [ MD ] , 0 )

, ISNULL ( [ ME ] , 0 )

, ISNULL ( [ MI ] , 0 )

, ISNULL ( [ MN ] , 0 )

, ISNULL ( [ MO ] , 0 )

, ISNULL ( [ MS ] , 0 )

, ISNULL ( [ NC ] , 0 )

, ISNULL ( [ ND ] , 0 )

, ISNULL ( [ NE ] , 0 )

, ISNULL ( [ NH ] , 0 )

, ISNULL ( [ NJ ] , 0 )

, ISNULL ( [ NM ] , 0 )

, ISNULL ( [ NV ] , 0 )

, ISNULL ( [ OH ] , 0 )

, ISNULL ( [ OK ] , 0 )

, ISNULL ( [ OR ] , 0 )

, ISNULL ( [ PA ] , 0 )

, ISNULL ( [ RI ] , 0 )

, ISNULL ( [ SC ] , 0 )

, ISNULL ( [ SD ] , 0 )

, ISNULL ( [ TN ] , 0 )

, ISNULL ( [ TX ] , 0 )

, ISNULL ( [ UT ] , 0 )

, ISNULL ( [ VA ] , 0 )

, ISNULL ( [ VT ] , 0 )

, ISNULL ( [ WA ] , 0 )

, ISNULL ( [ WI ] , 0 )

, ISNULL ( [ WV ] , 0 )

, ISNULL ( [ WY ] , 0 )

) ,

[ State_Tot ] AS (

SELECT

'Total' AS 'LSC'

, ISNULL ( SUM ( [ AK ] ) , 0 ) AS 'AK'

, ISNULL ( SUM ( [ AL ] ) , 0 ) AS 'AL'

, ISNULL ( SUM ( [ AR ] ) , 0 ) AS 'AR'

, ISNULL ( SUM ( [ AZ ] ) , 0 ) AS 'AZ'

, ISNULL ( SUM ( [ CA ] ) , 0 ) AS 'CA'

, ISNULL ( SUM ( [ CO ] ) , 0 ) AS 'CO'

, ISNULL ( SUM ( [ CT ] ) , 0 ) AS 'CT'

, ISNULL ( SUM ( [ DC ] ) , 0 ) AS 'DC'

, ISNULL ( SUM ( [ DE ] ) , 0 ) AS 'DE'

, ISNULL ( SUM ( [ FL ] ) , 0 ) AS 'FL'

, ISNULL ( SUM ( [ GA ] ) , 0 ) AS 'GA'

, ISNULL ( SUM ( [ HI ] ) , 0 ) AS 'HI'

, ISNULL ( SUM ( [ IA ] ) , 0 ) AS 'IA'

, ISNULL ( SUM ( [ ID ] ) , 0 ) AS 'ID'

, ISNULL ( SUM ( [ IL ] ) , 0 ) AS 'IL'

, ISNULL ( SUM ( [ IN ] ) , 0 ) AS 'IN'

, ISNULL ( SUM ( [ KS ] ) , 0 ) AS 'KS'

, ISNULL ( SUM ( [ KY ] ) , 0 ) AS 'KY'

, ISNULL ( SUM ( [ LA ] ) , 0 ) AS 'LA'

, ISNULL ( SUM ( [ MD ] ) , 0 ) AS 'MD'

, ISNULL ( SUM ( [ ME ] ) , 0 ) AS 'ME'

, ISNULL ( SUM ( [ MI ] ) , 0 ) AS 'MI'

, ISNULL ( SUM ( [ MN ] ) , 0 ) AS 'MN'

, ISNULL ( SUM ( [ MO ] ) , 0 ) AS 'MO'

, ISNULL ( SUM ( [ MS ] ) , 0 ) AS 'MS'

, ISNULL ( SUM ( [ NC ] ) , 0 ) AS 'NC'

, ISNULL ( SUM ( [ ND ] ) , 0 ) AS 'ND'

, ISNULL ( SUM ( [ NE ] ) , 0 ) AS 'NE'

, ISNULL ( SUM ( [ NH ] ) , 0 ) AS 'NH'

, ISNULL ( SUM ( [ NJ ] ) , 0 ) AS 'NJ'

, ISNULL ( SUM ( [ NM ] ) , 0 ) AS 'NM'

, ISNULL ( SUM ( [ NV ] ) , 0 ) AS 'NV'

, ISNULL ( SUM ( [ OH ] ) , 0 ) AS 'OH'

, ISNULL ( SUM ( [ OK ] ) , 0 ) AS 'OK'

, ISNULL ( SUM ( [ OR ] ) , 0 ) AS 'OR'

, ISNULL ( SUM ( [ PA ] ) , 0 ) AS 'PA'

, ISNULL ( SUM ( [ RI ] ) , 0 ) AS 'RI'

, ISNULL ( SUM ( [ SC ] ) , 0 ) AS 'SC'

, ISNULL ( SUM ( [ SD ] ) , 0 ) AS 'SD'

, ISNULL ( SUM ( [ TN ] ) , 0 ) AS 'TN'

, ISNULL ( SUM ( [ TX ] ) , 0 ) AS 'TX'

, ISNULL ( SUM ( [ UT ] ) , 0 ) AS 'UT'

, ISNULL ( SUM ( [ VA ] ) , 0 ) AS 'VA'

, ISNULL ( SUM ( [ VT ] ) , 0 ) AS 'VT'

, ISNULL ( SUM ( [ WA ] ) , 0 ) AS 'WA'

, ISNULL ( SUM ( [ WI ] ) , 0 ) AS 'WI'

, ISNULL ( SUM ( [ WV ] ) , 0 ) AS 'WV'

, ISNULL ( SUM ( [ WY ] ) , 0 ) AS 'WY'

, SUM ( [ AK ] + [ AL ] + [ AR ] + [ AZ ] + [ CA ] + [ CO ] + [ CT ] + [ DC ] + [ DE ] + [ FL ] + [ GA ] + [ HI ] + [ IA ] + [ ID ] + [ IL ] + [ IN ] + [ KS ] + [ KY ] + [ LA ] + [ MD ] + [ ME ] + [ MI ] + [ MN ] + [ MO ] + [ MS ] + [ NC ] + [ ND ] + [ NE ] + [ NH ] + [ NJ ] + [ NM ] + [ NV ] + [ OH ] + [ OK ] + [ OR ] + [ PA ] + [ RI ] + [ SC ] + [ SD ] + [ TN ] + [ TX ] + [ UT ] + [ VA ] + [ VT ] + [ WA ] + [ WI ] + [ WV ] + [ WY ] ) AS 'Total'

FROM [ Pivot ] A

)

SELECT *

FROM [ Pivot ]

UNION

SELECT *