CODEBOXER.com

an old pivot table report procedure

26 February 2009 | 10:24 am by codeboxer

Back in the day, I wrote reports for Rupert himself at FOX IT. Here is an old query from those days (written in T-SQL for .NET Reporting Services):

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/****** Object: Stored Procedure dbo.PR_T09 Script Date: 2/3/2006 4:17:47 PM ******/
ALTER PROCEDURE dbo.PR_T09
(
@YEAR1 char(4) = '2006'
,@QTR1 char(6) = 'Q1'
,@VER1 char(5) = '100'

,@YEAR2 char(4) = '2006'
,@QTR2 char(6) = 'Q2'
,@VER2 char(5) = '100'

,@BU VARCHAR(50) = ''
,@BU_GRP VARCHAR(50) = ''
,@BU_GRP_SUM VARCHAR(50) = 'TCFT-CONS-NOFOX21'

,@PERIOD_START INT = 1
,@PERIOD_END INT = 12
,@AMT BIT = 1
,@PRE_AMT BIT = 1
,@POST_AMT BIT = 1
,@II BIT = 0

)

/*************************************************************************

Created By: Krister Tidics - copied from F221 written by Livia Squires
Created Date: Feb 2, 2006
Called From: reporting services TCFT T09
Description: TCFT Operating Profit and Loss PL VARIANCE

Input: 1] Version info
2] II - switch to set whether Imputed interest is broken out separately

Output: PL data pivot for Operating Profit and Loss Reports

Revision History

Date: Initials: Desc:
**************************************************************************/

AS
BEGIN

SET NOCOUNT ON
/** Test harness
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DROP TABLE #PROJ
DECLARE
@YEAR1 CHAR(4)
,@QTR1 CHAR(6)
,@VER1 CHAR(5)
,@YEAR2 CHAR(4)
,@QTR2 CHAR(6)
,@VER2 CHAR(5)
,@BU_GRP_SUM VARCHAR(50)
,@BU_GRP VARCHAR(50)
,@BU CHAR(5)
,@II BIT
,@BY_PERIOD BIT
,@AMT INT
,@PRE_AMT INT
,@POST_AMT INT
,@PERIOD_START INT
,@PERIOD_END INT

SET @YEAR1 = '2006'
SET @QTR1 = 'Q1'
SET @VER1 = '100'

SET @YEAR2 = '2006'
SET @QTR2 = 'Q2'
SET @VER2 = '100'

SET @BU_GRP_SUM = 'TCFT-CONS-NOFOX21'
SET @BU_GRP = ''
SET @BU = ''

SET @II = 0
SET @BY_PERIOD = 0
SET @AMT = 1
SET @PRE_AMT = 1
SET @POST_AMT = 1
SET @PERIOD_START = 1
SET @PERIOD_END = 12

--*/

DECLARE @BU_NAME VARCHAR(50), @SCALE INT, @VER_ID INT
SET @SCALE = 5 -- no rounding

--DROP TABLE #PROJ

CREATE TABLE #PROJ (
COMPANY CHAR(5)
,PLAN_YEAR CHAR(4)
,FORECAST_QUARTER CHAR(6)
,VERSION_ID CHAR(5)
,BUSINESS_UNIT CHAR(5)
,REPORT_BUSINESS_UNIT CHAR(5)
,PERIOD INT NULL
,BUDGET_CODE CHAR(10)
,BC_DESC VARCHAR(60)
,PROJECT_ID CHAR(6) NOT NULL
,REPORT_PROJECT_ID CHAR(6) --NOT NULL
,SERIES_ID CHAR(6)
,TBD_ID CHAR(6)
,REPORT_SERIES_ID CHAR(6)
,FIRST_SEASON_PROJECT_ID CHAR(6) NULL
,LAST_SEASON_PROJECT_ID CHAR(6) NULL
,SEASON_COUNT CHAR(1) NULL
,PROJECT_GROUP NVARCHAR(15)
,PROJECT_GROUP_NAME NVARCHAR(50)
,PROJECT_GROUP_ORDER INT
,PROJECT_GROUP_ROLLUP NVARCHAR(15)
,PROJECT_GROUP_ROLLUP_NAME NVARCHAR(50)
,PROJECT_GROUP_ROLLUP_ORDER INT
,PROJECT_GROUP_MASTER NVARCHAR(15)
,PROJECT_GROUP_MASTER_NAME NVARCHAR(50)
,PROJECT_GROUP_MASTER_ORDER INT
,PROJECT_DESC VARCHAR (100)
,SERIES_DESC VARCHAR (100)
,INIT_AIR VARCHAR(5)
,AMOUNT MONEY
,PRE_AMOUNT MONEY
,POST_AMOUNT MONEY
,NETWORK MONEY
,DOM_SYN MONEY
,INT_TV MONEY
,DOM_HV MONEY
,INT_HV MONEY
,LM MONEY
,MUSIC MONEY
,PAY_TV MONEY
,OTHER MONEY
,REL_COST MONEY
,SERIES_PROD MONEY
,FINANCING MONEY
,POH MONEY
,RES MONEY
,PAR MONEY
,AMORT_ADJ MONEY
,VER_ID INTEGER NULL
)

CREATE NONCLUSTERED INDEX IX_PROJECT_ID ON #PROJ
(
PROJECT_ID
,PERIOD
)

CREATE NONCLUSTERED INDEX IX_FIRST_SEASON_PROJECT_ID ON #PROJ
(
FIRST_SEASON_PROJECT_ID
)

CREATE NONCLUSTERED INDEX IX_LAST_SEASON_PROJECT_ID ON #PROJ
(
LAST_SEASON_PROJECT_ID
)

CREATE NONCLUSTERED INDEX IX_SERIES_ID ON #PROJ
(
SERIES_ID
)

-- GET THE PROJECT DATA
-- VER 1
INSERT #PROJ (
COMPANY
,PLAN_YEAR
,FORECAST_QUARTER
,VERSION_ID
,BUSINESS_UNIT
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,SERIES_ID
,TBD_ID
,PROJECT_GROUP
,FIRST_SEASON_PROJECT_ID
,LAST_SEASON_PROJECT_ID
,SEASON_COUNT
,PROJECT_DESC
,SERIES_DESC
,INIT_AIR
,AMOUNT
,PRE_AMOUNT
,POST_AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
,VER_ID
)
EXEC dbo.PR_TCFT_OPL_BU
@YEAR1
,@QTR1
,@VER1
,@BU
,@BU_GRP
,@BU_GRP_SUM
,@PERIOD_START
,@PERIOD_END
,@II
,@AMT
,@PRE_AMT
,@POST_AMT
,1 -- TEMP TABLE
,1 -- VER_ID

-- GET THE PROJECT DATA
-- VER 2
INSERT #PROJ (
COMPANY
,PLAN_YEAR
,FORECAST_QUARTER
,VERSION_ID
,BUSINESS_UNIT
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,SERIES_ID
,TBD_ID
,PROJECT_GROUP
,FIRST_SEASON_PROJECT_ID
,LAST_SEASON_PROJECT_ID
,SEASON_COUNT
,PROJECT_DESC
,SERIES_DESC
,INIT_AIR
,AMOUNT
,PRE_AMOUNT
,POST_AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
,VER_ID
)
EXEC dbo.PR_TCFT_OPL_BU
@YEAR2
,@QTR2
,@VER2
,@BU
,@BU_GRP
,@BU_GRP_SUM
,@PERIOD_START
,@PERIOD_END
,@II
,@AMT
,@PRE_AMT
,@POST_AMT
,1 -- TEMP TABLE
,2 -- VER_ID

-- UPDATE THE TBD PROJECTS IN PRIOR TO MATCH THE PROJECT HEADERS IN THE CURRENT VERSION
UPDATE PRIOR_VER
SET PRIOR_VER.REPORT_PROJECT_ID = CURR_VER.REPORT_PROJECT_ID
,PRIOR_VER.REPORT_SERIES_ID = CURR_VER.REPORT_SERIES_ID
,PRIOR_VER.SERIES_DESC = CURR_VER.SERIES_DESC
,PRIOR_VER.PROJECT_DESC = CURR_VER.PROJECT_DESC
--,PRIOR_VER.RELEASE_STATUS = CURR_VER.RELEASE_STATUS
--SELECT *
FROM #PROJ PRIOR_VER
JOIN
(SELECT REPORT_PROJECT_ID,REPORT_SERIES_ID,TBD_ID,SERIES_DESC,PROJECT_DESC--,RELEASE_STATUS
FROM #PROJ
WHERE VER_ID = 1 AND TBD_ID IS NOT NULL)
CURR_VER
ON CURR_VER.TBD_ID = PRIOR_VER.PROJECT_ID
WHERE PRIOR_VER.VER_ID = 2

/** The next 4 insert queries are to make sure both versions have the same project ids
and budget codes in order for the inner join to work in the final select
**/
--insert any projects in ver2 that dont exist in ver1 into ver1
INSERT #PROJ (
VER_ID
--,PERIOD
,BUDGET_CODE
,BC_DESC
,COMPANY
,PROJECT_ID
,REPORT_PROJECT_ID
,SERIES_ID
,REPORT_SERIES_ID
,PROJECT_DESC
,SERIES_DESC
,BUSINESS_UNIT
,REPORT_BUSINESS_UNIT
,TBD_ID
--,RELEASE_STATUS
,AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
)
SELECT 1 VER_ID
--,VER2.PERIOD
,VER2.BUDGET_CODE
,VER2.BC_DESC
,VER2.COMPANY
,VER2.PROJECT_ID
,VER2.REPORT_PROJECT_ID
,VER2.SERIES_ID
,VER2.REPORT_SERIES_ID
,VER2.PROJECT_DESC
,VER2.SERIES_DESC
,VER2.BUSINESS_UNIT
,VER2.REPORT_BUSINESS_UNIT
,VER2.TBD_ID
--,VER2.RELEASE_STATUS
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM #PROJ VER2
WHERE VER2.VER_ID = 2
AND NOT EXISTS (SELECT VER1.PROJECT_ID
FROM #PROJ VER1
WHERE VER1.VER_ID = 1
AND VER2.PROJECT_ID = VER1.PROJECT_ID
)
--projects in ver1 that don't exist in ver2 into ver2
UNION ALL

SELECT 2 VER_ID
--,VER1.PERIOD
,VER1.BUDGET_CODE
,VER1.BC_DESC
,VER1.COMPANY
,VER1.PROJECT_ID
,VER1.REPORT_PROJECT_ID
,VER1.SERIES_ID
,VER1.REPORT_SERIES_ID
,VER1.PROJECT_DESC
,VER1.SERIES_DESC
,VER1.BUSINESS_UNIT
,VER1.REPORT_BUSINESS_UNIT
,VER1.TBD_ID
--,VER1.RELEASE_STATUS
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
,0
FROM #PROJ VER1
WHERE VER1.VER_ID = 1
AND NOT EXISTS (SELECT VER2.PROJECT_ID
FROM #PROJ VER2
WHERE VER2.VER_ID = 2
AND VER2.PROJECT_ID = VER1.PROJECT_ID
)

-- This update is to move Amort in the prior version up to the series level
-- of the current version on the report. It needs to be done here
-- so that the next query for inserting missing budget codes into VER1
-- does not miss the amort which we are moving to a different series id
UPDATE #PROJ
SET PROJECT_ID = TBD_NEW_SERIES.SERIES_ID
--select #FIN.*,TBD_NEW_SERIES.TBD_ID,TBD_NEW_SERIES.SERIES_ID
FROM #PROJ
JOIN (
SELECT REPORT_SERIES_ID SERIES_ID,TBD_ID
FROM #PROJ
WHERE VER_ID = 1 AND TBD_ID IS NOT NULL
)
TBD_NEW_SERIES ON TBD_NEW_SERIES.TBD_ID = #PROJ.PROJECT_ID
WHERE #PROJ.VER_ID = 2
AND (#PROJ.AMORT_ADJ <> 0 OR #PROJ.PAR <> 0 OR #PROJ.RES <> 0 )

-- insert budget codes that exist in ver2 but not ver1 into ver1
-- and vice versa
INSERT #PROJ (
VER_ID
--,PERIOD
,BUDGET_CODE
,BC_DESC
,PROJECT_ID
,REPORT_PROJECT_ID
,SERIES_ID
,REPORT_SERIES_ID
,PROJECT_DESC
,SERIES_DESC
,BUSINESS_UNIT
,REPORT_BUSINESS_UNIT
,TBD_ID
--,RELEASE_STATUS
,AMOUNT
,NETWORK
,DOM_SYN
,INT_TV
,DOM_HV
,INT_HV
,LM
,MUSIC
,PAY_TV
,OTHER
,REL_COST
,SERIES_PROD
,FINANCING
,POH
,RES
,PAR
,AMORT_ADJ
)
SELECT 1 VER_ID
--,FIN2.PERIOD
,FIN2.BUDGET_CODE
,FIN2.BC_DESC
,FIN2.PROJECT_ID
,FIN2.REPORT_PROJECT_ID
,FIN2.SERIES_ID
,FIN2.REPORT_SERIES_ID
,FIN2.PROJECT_DESC
,FIN2.SERIES_DESC
,FIN2.BUSINESS_UNIT
,FIN2.REPORT_BUSINESS_UNIT
,FIN2.TBD_ID
--,FIN2.RELEASE_STATUS
,0.00 AMOUNT
,0.00 NETWORK
,0.00 DOM_SYN
,0.00 INT_TV
,0.00 DOM_HV
,0.00 INT_HV
,0.00 LM
,0.00 MUSIC
,0.00 PAY_TV
,0.00 OTHER
,0.00 REL_COST
,0.00 SERIES_PROD
,0.00 FINANCING
,0.00 POH
,0.00 RES
,0.00 PAR
,0.00 AMORT_ADJ
FROM #PROJ FIN2
WHERE FIN2.VER_ID = 2
AND NOT EXISTS (SELECT *
FROM #PROJ FIN1
WHERE FIN1.VER_ID = 1
AND FIN1.PROJECT_ID = FIN2.PROJECT_ID
AND FIN1.BUDGET_CODE = FIN2.BUDGET_CODE)

-- budget codes that exist in ver1 but not ver2 into ver2
UNION ALL
SELECT 2 VER_ID
--,FIN1.PERIOD
,FIN1.BUDGET_CODE
,FIN1.BC_DESC
,FIN1.PROJECT_ID
,FIN1.REPORT_PROJECT_ID
,FIN1.SERIES_ID
,FIN1.REPORT_SERIES_ID
,FIN1.PROJECT_DESC
,FIN1.SERIES_DESC
,FIN1.BUSINESS_UNIT
,FIN1.REPORT_BUSINESS_UNIT
,FIN1.TBD_ID
--,FIN1.RELEASE_STATUS
,0.00 AMOUNT
,0.00 NETWORK
,0.00 DOM_SYN
,0.00 INT_TV
,0.00 DOM_HV
,0.00 INT_HV
,0.00 LM
,0.00 MUSIC
,0.00 PAY_TV
,0.00 OTHER

,0.00 REL_COST
,0.00 SERIES_PROD
,0.00 FINANCING
,0.00 POH
,0.00 RES
,0.00 PAR
,0.00 AMORT_ADJ
FROM #PROJ FIN1
WHERE FIN1.VER_ID = 1
AND NOT EXISTS (SELECT *
FROM #PROJ FIN2
WHERE FIN2.VER_ID = 2
AND FIN1.PROJECT_ID = FIN2.PROJECT_ID
AND FIN1.BUDGET_CODE = FIN2.BUDGET_CODE)

--UPDATE PROJECT_GROUPING
UPDATE #PROJ
SET PROJECT_GROUP_NAME = RG3.GROUP_NAME
,PROJECT_GROUP_ORDER = RG3.GROUP_SORT
,PROJECT_GROUP_ROLLUP = RG2.GROUP_CODE
,PROJECT_GROUP_ROLLUP_NAME = RG2.GROUP_NAME
,PROJECT_GROUP_ROLLUP_ORDER = RG2.GROUP_SORT
,PROJECT_GROUP_MASTER = RG1.GROUP_CODE
,PROJECT_GROUP_MASTER_NAME = RG1.GROUP_NAME
,PROJECT_GROUP_MASTER_ORDER = RG1.GROUP_SORT
FROM REPORT R
JOIN REPORT_FRAME F ON R.REPORT_FRAME_ID = F.REPORT_FRAME_ID
JOIN REPORT_GROUP RG1 ON RG1.REPORT_FRAME_ID = F.REPORT_FRAME_ID AND RG1.GROUP_LEVEL = 1
JOIN REPORT_GROUP RG2 ON RG2.PARENT_REPORT_GROUP_ID = RG1.REPORT_GROUP_ID AND RG2.GROUP_LEVEL = 2
JOIN REPORT_GROUP RG3 ON RG3.PARENT_REPORT_GROUP_ID = RG2.REPORT_GROUP_ID AND RG3.GROUP_LEVEL = 3
JOIN #PROJ ON #PROJ.PROJECT_GROUP = RG3.GROUP_CODE
WHERE R.REPORT_NUMBER = 'T01A'

--SELECT * FROM #PROJ

--ADDED KT - I AM UPDATING THE REPORT_PROJECT_IDs for all those that don't come in
UPDATE #PROJ
SET REPORT_PROJECT_ID = PROJECT_ID
,REPORT_BUSINESS_UNIT = BUSINESS_UNIT
,REPORT_SERIES_ID = SERIES_ID
WHERE REPORT_PROJECT_ID IS NULL

-- final select statement
SELECT VER1.SERIES_DESC
,VER1.REPORT_SERIES_ID
,VER1.REPORT_PROJECT_ID
,VER1.PROJECT_DESC
--VER1.RELEASE_STATUS,
--,VER1.PERIOD
,VER1.BUDGET_CODE
--VER1.BC_CAT,
,VER1.BC_DESC
,VER1.PROJECT_GROUP
,VER1.PROJECT_GROUP_NAME
,VER1.PROJECT_GROUP_ORDER
,VER1.PROJECT_GROUP_ROLLUP
,VER1.PROJECT_GROUP_ROLLUP_NAME
,VER1.PROJECT_GROUP_ROLLUP_ORDER
,VER1.PROJECT_GROUP_MASTER
,VER1.PROJECT_GROUP_MASTER_NAME
,VER1.PROJECT_GROUP_MASTER_ORDER
,(VER1.AMOUNT - VER2.AMOUNT) AMOUNT
,(VER1.NETWORK - VER2.NETWORK) NETWORK
,(VER1.DOM_SYN - VER2.DOM_SYN) DOM_SYN
,(VER1.INT_TV - VER2.INT_TV) INT_TV
,(VER1.DOM_HV - VER2.DOM_HV) DOM_HV
,(VER1.INT_HV - VER2.INT_HV) INT_HV
,(VER1.LM - VER2.LM) LM
,(VER1.MUSIC - VER2.MUSIC) MUSIC
,(VER1.PAY_TV - VER2.PAY_TV) PAY_TV
,(VER1.OTHER - VER2.OTHER) OTHER
,(VER1.REL_COST - VER2.REL_COST) REL_COST
,(VER1.SERIES_PROD - VER2.SERIES_PROD) SERIES_PROD
,(VER1.FINANCING - VER2.FINANCING) FINANCING
,(VER1.POH - VER2.POH) POH
,(VER1.RES - VER2.RES) RES
,(VER1.PAR - VER2.PAR) PAR
,(VER1.AMORT_ADJ - VER2.AMORT_ADJ) AMORT_ADJ
FROM (
SELECT
--#PROJ.PERIOD,
#PROJ.SERIES_DESC,
#PROJ.REPORT_SERIES_ID,
#PROJ.REPORT_PROJECT_ID,
#PROJ.PROJECT_DESC,
--#PROJ.RELEASE_STATUS,
#PROJ.BUDGET_CODE
,#PROJ.BC_DESC
,#PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER,
--#PROJ.BC_CAT,
--#PROJ.BC_DESC,
-1*SUM(AMOUNT) AMOUNT,
-1*SUM(NETWORK) NETWORK,
-1*SUM(DOM_SYN) DOM_SYN,
-1*SUM(INT_TV) INT_TV,
-1*SUM(DOM_HV) DOM_HV,
-1*SUM(INT_HV) INT_HV,
-1*SUM(LM) LM,
-1*SUM(MUSIC) MUSIC,
-1*SUM(PAY_TV) PAY_TV,
-1*SUM(OTHER) OTHER,
-1*SUM(REL_COST) REL_COST,
-1*SUM(SERIES_PROD) SERIES_PROD,
-1*SUM(FINANCING) FINANCING,
-1*SUM(POH) POH,
-1*SUM(RES) RES,
-1*SUM(PAR) PAR,
-1*SUM(AMORT_ADJ) AMORT_ADJ

FROM #PROJ
WHERE #PROJ.VER_ID =1
GROUP BY #PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER
,#PROJ.SERIES_DESC
,#PROJ.REPORT_SERIES_ID
,#PROJ.REPORT_PROJECT_ID
,#PROJ.PROJECT_DESC
--,#PROJ.RELEASE_STATUS
--,#PROJ.PERIOD
,#PROJ.BUDGET_CODE
--,#FIN.BC_CAT
,#PROJ.BC_DESC
--ORDER BY #PROJ.RELEASE_ORDER,#PROJ.SERIES_DESC,#PROJ.REPORT_PROJECT_ID,#FIN.BUDGET_CODE
--ORDER BY #PROJ.REPORT_PROJECT_ID,#FIN.BUDGET_CODE
) VER1
JOIN (
SELECT
#PROJ.SERIES_DESC,
--#PROJ.PERIOD,
#PROJ.REPORT_SERIES_ID,
#PROJ.REPORT_PROJECT_ID,
#PROJ.PROJECT_DESC,
--#PROJ.RELEASE_STATUS,
#PROJ.BUDGET_CODE
,#PROJ.BC_DESC
,#PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER,
--#PROJ.BC_CAT,
--#PROJ.BC_DESC,
-1*SUM(AMOUNT) AMOUNT,
-1*SUM(NETWORK) NETWORK,
-1*SUM(DOM_SYN) DOM_SYN,
-1*SUM(INT_TV) INT_TV,
-1*SUM(DOM_HV) DOM_HV,
-1*SUM(INT_HV) INT_HV,
-1*SUM(LM) LM,
-1*SUM(MUSIC) MUSIC,
-1*SUM(PAY_TV) PAY_TV,
-1*SUM(OTHER) OTHER,
-1*SUM(REL_COST) REL_COST,
-1*SUM(SERIES_PROD) SERIES_PROD,
-1*SUM(FINANCING) FINANCING,
-1*SUM(POH) POH,
-1*SUM(RES) RES,
-1*SUM(PAR) PAR,
-1*SUM(AMORT_ADJ) AMORT_ADJ

FROM #PROJ
WHERE #PROJ.VER_ID =2

GROUP BY #PROJ.PROJECT_GROUP
,#PROJ.PROJECT_GROUP_NAME
,#PROJ.PROJECT_GROUP_ORDER
,#PROJ.PROJECT_GROUP_ROLLUP
,#PROJ.PROJECT_GROUP_ROLLUP_NAME
,#PROJ.PROJECT_GROUP_ROLLUP_ORDER
,#PROJ.PROJECT_GROUP_MASTER
,#PROJ.PROJECT_GROUP_MASTER_NAME
,#PROJ.PROJECT_GROUP_MASTER_ORDER
,#PROJ.SERIES_DESC
,#PROJ.REPORT_SERIES_ID
,#PROJ.REPORT_PROJECT_ID
,#PROJ.PROJECT_DESC
--,#PROJ.PERIOD
--,#PROJ.RELEASE_STATUS
,#PROJ.BUDGET_CODE
--,#PROJ.BC_CAT
,#PROJ.BC_DESC

) VER2
ON VER1.REPORT_PROJECT_ID = VER2.REPORT_PROJECT_ID
AND VER1.BUDGET_CODE = VER2.BUDGET_CODE
ORDER BY VER1.SERIES_DESC
,VER1.REPORT_PROJECT_ID
,VER1.BUDGET_CODE

--select * from #PROJ

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Original post blogged on codeboxer.com.

minor league pivot query

27 January 2009 | 10:33 pm by codeboxer

This would be a pivot query 101 kind of snippet. They teach this in SQL kindergarten - I only imagine, of course, because I never went.

The use case: it's a single table, with values grouped by partner_id and two enums for revplan_type. You need a variance report. What do you do?

SELECT t_plan.partner_id, plan_amount - for_amount variance
FROM
(SELECT partner_id, amount plan_amount
FROM revplan_infos
WHERE revplan_type = 'Plan') t_plan
JOIN (
SELECT partner_id, amount for_amount
FROM revplan_infos
WHERE revplan_type = 'Forecast') t_for
ON t_for.partner_id = t_plan.partner_id

Original post blogged on codeboxer.com.