SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Fernando Fernandez
-- Create date: 2/10/09
-- Description: Cross Tab summary report by Business Unit
-- =============================================
ALTER PROCEDURE BUSummaryReportCrossTab
@BusinessUnitId int = 0,
@BeginDt datetime,
@EndDt datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlSelect varchar(8000)
– embed the parameters into the sql
SET @SqlSelect = ‘SELECT oeb.LAST_NAME , oeb.FIRST_NAME
, t.ID, t.DateOfService, t.Score, t.HoldFailed
, qs.Score AS QuadrantScore
, it.Description, it.SortOrder
FROM TRIP t
, ORACLE_EMPLOYEE_BU oeb
, QUADRANT_SCORE qs
, INFORMATION_TYPE it
WHERE t.AuditedFlag = 1
AND t.BUSINESS_UNIT_ID = ‘ + CONVERT( VARCHAR(5), @BusinessUnitId ) + ‘
AND t.DateOfService BETWEEN ”’ + CONVERT( VARCHAR(10), @BeginDt, 101 ) + ”’ AND ”’ + CONVERT( VARCHAR(10), @EndDt, 101 ) + ”’
AND t.EMPLOYEE_NUMBER = oeb.EMPLOYEE_NUMBER
AND t.ID = qs.TRIP_ID
AND qs.INFORMATION_TYPE_ID = it.ID
UNION
SELECT oeb.LAST_NAME , oeb.FIRST_NAME
, t.ID, t.DateOfService, t.Score, t.HoldFailed
, t.Score AS QuadrantScore
, ”Overall”, -1
FROM TRIP t
, ORACLE_EMPLOYEE_BU oeb
WHERE t.AuditedFlag = 1
AND t.BUSINESS_UNIT_ID = ‘ + CONVERT( VARCHAR(5), @BusinessUnitId ) + ‘
AND t.DateOfService BETWEEN ”’ + CONVERT( VARCHAR(10), @BeginDt, 101 ) + ”’ AND ”’ + CONVERT( VARCHAR(10), @EndDt, 101 ) + ”’
AND t.EMPLOYEE_NUMBER = oeb.EMPLOYEE_NUMBER ‘;
– the stored proc below converts the result set of the given SQL into a crosstab
execute CrossTab
@Select= @SqlSelect
, @PivotCol=’YEAR( DateOfService ) * 100 + MONTH( DateOfService )’
, @Summaries=’AVG( QuadrantScore ELSE 0 )[], AVG( Score )[Overall], COUNT( ID )[TotalPCRsScored] ‘
, @GroupBy=’LAST_NAME, FIRST_NAME, Description’
, @NumberPivotCols = 0
–, @OtherCols=”
– version using PIVOT keyword – it doesn’t work as good as the stored proc version
–SELECT LAST_NAME, FIRST_NAME, Description, SortOrder, [200809], [200810], [200811], [200812], [200901], [200902]
– –, Score–, HoldFailed
–FROM (
–
–SELECT oeb.LAST_NAME, oeb.FIRST_NAME
– , YEAR( t.DateOfService ) * 100 + MONTH( t.DateOfService ) AS YearMonth
– , qs.Score AS QuadrantScore
– , it.Description, it.SortOrder
–FROM TRIP t
– , ORACLE_EMPLOYEE_BU oeb
– , QUADRANT_SCORE qs
– , INFORMATION_TYPE it
–WHERE t.AuditedFlag = 1
– AND t.BUSINESS_UNIT_ID = 175 –{0}
– AND t.DateOfService BETWEEN ‘11/1/08′ AND ‘2/28/09′ –’{1}’ AND ‘{2}’
– AND t.EMPLOYEE_NUMBER = oeb.EMPLOYEE_NUMBER
– AND t.ID = qs.TRIP_ID
– AND qs.INFORMATION_TYPE_ID = it.ID
–UNION
–SELECT oeb.LAST_NAME, oeb.FIRST_NAME
– , YEAR( t.DateOfService ) * 100 + MONTH( t.DateOfService ) AS YearMonth
– , t.Score AS QuadrantScore
– , ‘Overall’ AS Description, -1
–FROM TRIP t
– , ORACLE_EMPLOYEE_BU oeb
–WHERE t.AuditedFlag = 1
– AND t.BUSINESS_UNIT_ID = 175 –{0}
– AND t.DateOfService BETWEEN ‘11/1/08′ AND ‘2/28/09′ –’{1}’ AND ‘{2}’
– AND t.EMPLOYEE_NUMBER = oeb.EMPLOYEE_NUMBER
–) AS pv
– PIVOT ( AVG( QuadrantScore ) FOR YearMonth IN ( [200809], [200810], [200811], [200812], [200901], [200902] ) ) AS ScoresPivot
–ORDER BY LAST_NAME, FIRST_NAME, SortOrder
END
GO
