x

parametizing a tsql query

I need to parametized the query below. The variable to be parametized are;

  1. Category varchar(255)

  2. Contract_Region varchar(255)

The query:

 use OnBaseUnit
 
 
 DECLARE @contractRegion varchar(255)
 DECLARE @lifecycleNum int
 DECLARE @revalstatenum int
 DECLARE @itemtypenum int
 a
             
 --For directors data provider this should be set to 1, else to 0 for normal reports
 
 --Table utilized to limit universe to only active docs from lifecycle 370 (326 in Prod)
 DECLARE @tmpDocs Table (
             DocHandle INT,
             LifecycleId INT,
             QueueName VARCHAR(255)
             )
 
 SET @lifecycleNum = 370 --Unit: 370, Prod: 326 
 SET @revalstatenum = 2594 --Unit: 2594
 SET @itemtypenum = 537 --Unit : 537, Prod: 484
 
 INSERT INTO @tmpDocs (
             DocHandle,
             LifecycleId,
             QueueName )
             
 SELECT ilc.itemnum 'DocHandle',
                ilc.lcnum 'LifecycleId',
                lcs.statename 'QueueName'
 FROM hsi.itemlc ilc
             INNER JOIN hsi.itemdata itd
                         ON ilc.itemnum = itd.itemnum
             LEFT JOIN hsi.lcstate lcs
                         ON ilc.statenum = lcs.statenum
 WHERE lcnum = @lifecycleNum 
             and ilc.status = 0 AND ilc.statenum <> @revalstatenum AND itd.itemtypenum = @itemtypenum
 
 
 --Table variable that is used in order to retrieve all required fields
 DECLARE @tempTable TABLE (
             DocHandle INT
             ,QueueName varchar(255)
             ,Entry_Number INT
             ,Case_Number varchar(255)
             ,Category varchar(255)
             ,Receipt_Date DATE
             ,Contract_Region varchar(255)
             ,LOB varchar(255)
             ,App_Source varchar (255)
             ,App_Count varchar (255)
             ,Application_Reason varchar(255)
             ,PE_Indexing_Date datetime
             ,PE_Prescreened_Date datetime
             ,PE_1st_Development_Date datetime
             ,PE_PECOS_Enroll_Date datetime
             ,PE_Mainframe_Update_Date datetime
             ,PE_Approval_Recommended_User varchar(255)
             ,PE_Approval_Recommended_Date datetime
             ,PE_Resolution_Date datetime
             ,PE_Resolution_User varchar(255)
             ,Resolution_Type varchar(255)
             ,WF_App_Status varchar(255)
             ,UserId varchar(255)
             ,PE_Current_User varchar(255)
             ,CompletedBy varchar(255)
             ,CompletedDate datetime
             ,AgeInDays INT
 )
 
 INSERT INTO @tempTable (
             DocHandle
             ,QueueName
             ,Entry_Number
             ,Case_Number
             ,Category
             ,Receipt_Date
             ,Contract_Region
             ,LOB
             ,App_Source
             ,App_Count
             ,Application_Reason
             ---
             ,PE_Indexing_Date
             ,PE_Prescreened_Date
             ,PE_1st_Development_Date
             ,PE_PECOS_Enroll_Date
             ,PE_Mainframe_Update_Date
             ,PE_Approval_Recommended_User
             ,PE_Approval_Recommended_Date
             ,PE_Resolution_Date
             ,PE_Resolution_User
             ,Resolution_Type
             ,WF_App_Status
             ,UserId
             ,PE_Current_User
             ,CompletedBy
             ,CompletedDate
             ,AgeInDays
             
 )
 
 select
 
             itemdata.DocHandle
             ,itemdata.QueueName
             ,EntryNumColumn.EntryNum 'Entry_Number'
             ,CaseNumberColumn.CaseNumber 'Case_Number'
             ,CMSMetricCategoryColumn.CMSMetricCategory 'Category'
             --,dbo.getCategory(EntryNum,AppCount,strRiskCatValue,ResolutionStatus,StatusDate,WFAppStatus,PrescreenedDate)'Category'
             --,itemdata.EnteredDate
             ,ReceivedDateColumn.ReceivedDate 'Receipt_Date'
             --,dbo.getDueDate(ReceivedDate,AppSource,CMSMetricCategoryColumn.CMSMetricCategory,SiteVisitDate)'Due_Date'
             ,ContractRegionColumn.ContractRegion 'Contract_Region'
             ,case when ContractRegion IN ('J15 KY A','J15 OH A') then 'Part A' else 
                         case when ContractRegion IN ('J15 KY B','J15 OH B') then 'Part B' else
                         case when ContractRegion =  'J15 HHH' then 'HHH' else 'Other' end end end as 'LOB'
             ,RTRIM(LTRIM(AppSourceColumn.AppSource)) 'WF_App_Source'
             ,AppCountColumn.AppCount 'App_Count'
             ,strRiskCatValueColumn.strRiskCatValue 'Application_Reason'
             --,PEIndexUserColumn.PEIndexUser 'PE_Indexing_User'
             ,IndexDateColumn.IndexDate 'PE_Indexing_Date'
             ,PrescreenedDateColumn.PrescreenedDate 'PE_Prescreened_Date'
             --,PrescreenedNameColumn.PrescreenedName 'PE_Prescreened_User'
             --,SiteVisitDateColumn.SiteVisitDate 'PE_Site_Visit_Date'
             --,SiteVisitNameColumn.SiteVisitName 'PE_Site_Visit_User'
             ,DevelopLv1_Q3_DateColumn.DevelopLv1_Q3_Date 'PE_1st_Development_Date'
             --,DevelopLv1_Q3NameColumn.DevelopLv1_Q3Name 'PE_1st_Development_User'
             --,PecosReady_Q4DateColumn.PecosReady_Q4Date 'PE_PECOS_Ready_Date'
             --,PecosReady_Q4NameColumn.PecosReady_Q4Name 'PE_PECOS_Ready_User'
             ,PecosEnrolled_Q5DateColumn.PecosEnrolled_Q5Date 'PE_PECOS_Enroll_Date'
             --,PecosEnrolled_Q5NameColumn.PecosEnrolled_Q5Name 'PE_PECOS_Enroll_Name'
             ,MCSUPdated_Q5DateColumn.MCSUPdated_Q5Date 'PE_Mainframe_Update_Date'
             --,MCSUPdated_Q5NameColumn.MCSUPdated_Q5Name 'PE_Mainframe_Update_User'
             ,ApprovalRecommendedUserColumn.ApprovalRecommendedUser 'PE_Approval_Recommended_User'
             ,ApprovalRecommendedDateColumn.ApprovalRecommendedDate 'PE_Approval_Recommended_Date'
             ,StatusDateColumn.StatusDate 'PE_Resolution_Date'
             --,case when ApprovalRecommendedDate IS NOT NULL then ApprovalRecommendedDate else StatusDate end 'Completion_Date'
             ,UserNameColumn.UserName 'PE_Resolution_User'
             ,ResolutionStatusColumn.ResolutionStatus 'Resolution_Type'
             ,WFAppStatusColumn.WFAppStatus 'WF_App_Status'
             --,SiteVisitStatusColumn.SiteVisitStatus 'PE_Site_Visit_Status'
             ,UserIdColumn.UserId 'User_ID'
             ,PECurrentUserColumn.PECurrentUser 'PE_Current_User'
             --,supervisorFullName as 'Supervisor'
             --,WFFeeStatusColumn.WFFeeStatus 'WF_Fee_Status'
             --,PEDNFDate 'PE_DNF_Date'
             --,PEDNFUser 'PE_DNF_User'
             --,GPTAN 'PE_GPTAN'
             --,GNPI 'PE_GNPI'
             --,LegalNameColumn.LegalName 'Legal_Name'
             --,IPTAN 'PE_IPTAN'
             --,INPI 'PE_INPI'
             --,FirstName 'First_Name'
             --,LastName 'Last_Name'
             --,SpecialtyColumn.Specialty 'Specialty'
             --,CahabaID 'CAHABA_ID'
             --,PECOSLTID 'PE_PECOS_LT_ID'
             --,FindingUser 'PE_Finding_User'
             --,FindingDate 'PE_Finding_Date'
             --,EffectiveDate 'PE_Effective_Date'
             --,TerminationDate 'PE_Termination_Date'
             
             --,case when (ApprovalRecommendedDate BETWEEN @fromDate AND @thruDate OR (ApprovalRecommendedDate IS NULL AND StatusDate BETWEEN @fromDate AND @thruDate)) then 1 else 0 end 'CompletedFlag'
             --,case when (IndexDate BETWEEN @fromDate AND @thruDate) then 1 else 0 end 'IndexedFlag'
             --,case when (PrescreenedDate BETWEEN @fromDate AND @thruDate) then 1 else 0 end 'PreScreenedFlag'
             --,case when (DevelopLv1_Q3_Date BETWEEN @fromDate AND @thruDate) then 1 else 0 end 'DevelopedFlag'
             --,case when (PecosReady_Q4Date BETWEEN @fromDate AND @thruDate) then 1 else 0 end 'ReadyFlag'
             --,case when (PecosEnrolled_Q5Date BETWEEN @fromDate AND @thruDate) then 1 else 0 end 'EnrolledFlag'
             --,case when (MCSUPdated_Q5Date BETWEEN @fromDate AND @thruDate) then 1 else 0 end 'UpdatedFlag'
             ,case when ApprovalRecommendedUser IS NOT NULL then ApprovalRecommendedUser else UserName end 'CompletedBy'
             ,case when ApprovalRecommendedDate IS NOT NULL then ApprovalRecommendedDate else StatusDate end 'CompletedDate'
             ,DATEDIFF(day,ReceivedDate,GETDATE()) as AgeInDays 
 
 from
 --Main Document Data
 (
             select DocHandle, LifecycleId, QueueName from @tmpDocs
             
 ) itemdata
 left join
 --Entry #
 (select itemnum, keyvaluebig as EntryNum from hsi.keyitem141)EntryNumColumn ON EntryNumColumn.itemnum = itemdata.DocHandle
 left join
 --Case #
 (select itemnum,keyvaluechar as CaseNumber from hsi.keyitem140)CaseNumberColumn ON CaseNumberColumn.itemnum = itemdata.DocHandle
 left join
 --CMS Metric Category
 (select itemnum,keyvaluechar as CMSMetricCategory from hsi.keyitem1676)CMSMetricCategoryColumn ON CMSMetricCategoryColumn.itemnum = itemdata.DocHandle
 left join
 --Received Date
 (select itemnum,keyvaluedate as ReceivedDate from hsi.keyitem437)ReceivedDateColumn ON ReceivedDateColumn.itemnum = itemdata.DocHandle
 left join
 --ContractRegion
 (select itemnum,keyvaluechar as ContractRegion from hsi.keyitem488)ContractRegionColumn ON ContractRegionColumn.itemnum = itemdata.DocHandle
 left join
 --App source
 (select itemnum,keyvaluechar as AppSource from hsi.keyitem892)AppSourceColumn ON AppSourceColumn.itemnum = itemdata.DocHandle
 --App Count
 left join
 (select itemnum,keywordnum from hsi.keyxitem341)AppCount1 on AppCount1.itemnum = itemdata.DocHandle
 left join
 (select keywordnum,keyvaluechar AppCount from hsi.keytable341)AppCountColumn on AppCountColumn.keywordnum = AppCount1.keywordnum
 left join
 --strRiskCatValue
 (select itemnum,keyvaluechar as strRiskCatValue from hsi.keyitem489)strRiskCatValueColumn ON strRiskCatValueColumn.itemnum = itemdata.DocHandle
 left join
 --PEIndexUser
 --(select itemnum,keyvaluechar as PEIndexUser from hsi.keyitem1120)PEIndexUserColumn ON PEIndexUserColumn.itemnum = itemdata.DocHandle
 --left join
 --Index Date
 (select itemnum,keyvaluedate as IndexDate from hsi.keyitem1121)IndexDateColumn ON IndexDateColumn.itemnum = itemdata.DocHandle
 left join
 --Prescreened Date
 (select itemnum,keyvaluedate as PrescreenedDate from hsi.keyitem904)PrescreenedDateColumn ON PrescreenedDateColumn.itemnum = itemdata.DocHandle
 left join
 --Prescreened Name
 --(select itemnum,keyvaluechar as PrescreenedName from hsi.keyitem1024)PrescreenedNameColumn ON PrescreenedNameColumn.itemnum = itemdata.DocHandle 
 --left join
 --Site Visit Date
 (select itemnum,keyvaluedate as SiteVisitDate from hsi.keyitem1031)SiteVisitDateColumn ON SiteVisitDateColumn.itemnum = itemdata.DocHandle
 left join
 --Site Visit Name
 (select itemnum,keyvaluechar as SiteVisitName from hsi.keyitem1032)SiteVisitNameColumn ON SiteVisitNameColumn.itemnum = itemdata.DocHandle 
 left join
 --DevelopLv1_Q3_Date
 (select itemnum,keyvaluedate as DevelopLv1_Q3_Date from hsi.keyitem905)DevelopLv1_Q3_DateColumn ON DevelopLv1_Q3_DateColumn.itemnum = itemdata.DocHandle
 left join
 --DevelopLv1_Q3Name
 --(select itemnum,keyvaluechar as DevelopLv1_Q3Name from hsi.keyitem1013)DevelopLv1_Q3NameColumn ON DevelopLv1_Q3NameColumn.itemnum = itemdata.DocHandle
 --left join
 --PecosReady_Q4 Date
 --(select itemnum,keyvaluedate as PecosReady_Q4Date from hsi.keyitem1015)PecosReady_Q4DateColumn ON PecosReady_Q4DateColumn.itemnum = itemdata.DocHandle
 --left join
 ----PecosReady_Q4 Name
 --(select itemnum,keyvaluechar as PecosReady_Q4Name from hsi.keyitem1016)PecosReady_Q4NameColumn ON PecosReady_Q4NameColumn.itemnum = itemdata.DocHandle 
 --left join
 --PecosEnrolled_Q5 Date
 (select itemnum,keyvaluedate as PecosEnrolled_Q5Date from hsi.keyitem1017)PecosEnrolled_Q5DateColumn ON PecosEnrolled_Q5DateColumn.itemnum = itemdata.DocHandle
 left join
 --PecosEnrolled_Q5 Name
 --(select itemnum,keyvaluechar as PecosEnrolled_Q5Name from hsi.keyitem1018)PecosEnrolled_Q5NameColumn ON PecosEnrolled_Q5NameColumn.itemnum = itemdata.DocHandle 
 --left join
 --MCSUPdated_Q5 Date
 (select itemnum,keyvaluedate as MCSUPdated_Q5Date from hsi.keyitem1019)MCSUPdated_Q5DateColumn ON MCSUPdated_Q5DateColumn.itemnum = itemdata.DocHandle
 left join
 --MCSUPdated_Q5 Name
 --(select itemnum,keyvaluechar as MCSUPdated_Q5Name from hsi.keyitem1020)MCSUPdated_Q5NameColumn ON MCSUPdated_Q5NameColumn.itemnum = itemdata.DocHandle 
 --left join
 --Approval Recommended Date
 (select itemnum,keyvaluedate as ApprovalRecommendedDate from hsi.keyitem1157)ApprovalRecommendedDateColumn ON ApprovalRecommendedDateColumn.itemnum = itemdata.DocHandle
 left join
 ----Approval Recommended User
 (select itemnum,keyvaluechar as ApprovalRecommendedUser from hsi.keyitem1156)ApprovalRecommendedUserColumn ON ApprovalRecommendedUserColumn.itemnum = itemdata.DocHandle 
 left join
 --Status Date
 (select itemnum,keyvaluedate as StatusDate from hsi.keyitem1022)StatusDateColumn ON StatusDateColumn.itemnum = itemdata.DocHandle
 left join
 --User Name
 (select itemnum,keyvaluechar as UserName from hsi.keyitem1021)UserNameColumn ON UserNameColumn.itemnum = itemdata.DocHandle 
 left join
 --Resolution Type (Status)
 (select itemnum,keywordnum from hsi.keyxitem159)ResolutionStatus1 on ResolutionStatus1.itemnum = itemdata.DocHandle
 left join
 (select keywordnum,keyvaluechar ResolutionStatus from hsi.keytable159)ResolutionStatusColumn on ResolutionStatusColumn.keywordnum = ResolutionStatus1.keywordnum
 left join
 --WFAppStatus
 (select itemnum,keyvaluechar as WFAppStatus from hsi.keyitem889)WFAppStatusColumn ON WFAppStatusColumn.itemnum = itemdata.DocHandle
 left join
 --SiteVisitStatus 
 --(select itemnum,keyvaluechar as SiteVisitStatus from hsi.keyitem1154)SiteVisitStatusColumn ON SiteVisitStatusColumn.itemnum = itemdata.DocHandle
 --left join 
 --PECurrentUser
 (
             select itemnum,keyvaluechar as PECurrentUser from hsi.keyitem347
 )PECurrentUserColumn ON PECurrentUserColumn.itemnum = itemdata.DocHandle
 left join 
 --UserID (ID of current user
 (
             select itemnum,keyvaluechar as UserId from hsi.keyitem142
 )UserIdColumn ON UserIdColumn.itemnum = itemdata.DocHandle
 left join 
 --WFFeeStatus 
 (
             select itemnum,keyvaluechar as WFFeeStatus from hsi.keyitem902
 )WFFeeStatusColumn ON WFFeeStatusColumn.itemnum = itemdata.DocHandle
 --left join 
 --PEDNFDate
 --(select itemnum,keyvaluedate as PEDNFDate from hsi.keyitem899)PEDNFDateColumn ON PEDNFDateColumn.itemnum = itemdata.DocHandle
 --left join 
 ----PEDNFUser
 --(select itemnum,keyvaluechar as PEDNFUser from hsi.keyitem1119)PEDNFUserColumn ON PEDNFUserColumn.itemnum = itemdata.DocHandle
 --left join 
 ----GPTAN 
 --(select itemnum,keyvaluechar as GPTAN from hsi.keyitem359)GPTANColumn ON GPTANColumn.itemnum = itemdata.DocHandle
 --left join 
 ----GNPI 
 --(select itemnum,keyvaluechar as GNPI from hsi.keyitem358)GNPIColumn ON GNPIColumn.itemnum = itemdata.DocHandle
 --left join
 ----Legal Name
 --(
 --         select itemnum,keywordnum from hsi.keyxitem182
 --)LegalName1 on LegalName1.itemnum = itemdata.DocHandle
 --left join
 --(
 --         select keywordnum,keyvaluechar LegalName from hsi.keytable182
 --)LegalNameColumn on LegalNameColumn.keywordnum = LegalName1.keywordnum 
 --left join 
 ----IPTAN 
 --(select itemnum,keyvaluechar as IPTAN from hsi.keyitem338)IPTANColumn ON IPTANColumn.itemnum = itemdata.DocHandle
 --left join 
 ----INPI 
 --(select itemnum,keyvaluechar as INPI from hsi.keyitem337)INPIColumn ON INPIColumn.itemnum = itemdata.DocHandle
 --left join 
 ----FirstName 
 --(select itemnum,keyvaluechar as FirstName from hsi.keyitem106)FirstNameColumn ON FirstNameColumn.itemnum = itemdata.DocHandle
 --left join 
 ----LastName 
 --(select itemnum,keyvaluechar as LastName from hsi.keyitem107)LastNameColumn ON LastNameColumn.itemnum = itemdata.DocHandle
 ----Specialty
 --left join
 --(
 --         select itemnum,keywordnum from hsi.keyxitem146
 --)Specialty1 on Specialty1.itemnum = itemdata.DocHandle
 --left join
 --(
 --         select keywordnum,keyvaluechar Specialty from hsi.keytable146
 --)SpecialtyColumn on SpecialtyColumn.keywordnum = Specialty1.keywordnum  
 --left join 
 ----CahabaID 
 --(select itemnum,keyvaluechar as CahabaID from hsi.keyitem1374)CahabaIDColumn ON CahabaIDColumn.itemnum = itemdata.DocHandle
 --left join 
 ----PECOSLTID 
 --(select itemnum,keyvaluechar as PECOSLTID from hsi.keyitem1116)PECOSLTIDColumn ON PECOSLTIDColumn.itemnum = itemdata.DocHandle
 --left join 
 ----FindingUser 
 --(select itemnum,keyvaluechar as FindingUser from hsi.keyitem1534)FindingUserColumn ON FindingUserColumn.itemnum = itemdata.DocHandle
 --left join 
 ----FindingDate 
 --(select itemnum,keyvaluedate as FindingDate from hsi.keyitem1540)FindingDateColumn ON FindingDateColumn.itemnum = itemdata.DocHandle
 --left join 
 ----EffectiveDate 
 --(select itemnum,keyvaluedate as EffectiveDate from hsi.keyitem446)EffectiveDateColumn ON EffectiveDateColumn.itemnum = itemdata.DocHandle
 --left join 
 ----TerminationDate 
 --(select itemnum,keyvaluedate as TerminationDate from hsi.keyitem1025)TerminationDateColumn ON TerminationDateColumn.itemnum = itemdata.DocHandle
 
 
 
 --left join
 --(select * from dbo.deltekEmployeeInfo
 --where (@isGlobal=1 OR @loggedInUser=employeeId OR @loggedInUser = supervisorId)
 --)employeesData on employeesData.employeeId=UserIdColumn.UserId
 
 
 -- Paper – Regular
 
 SELECT 
             App_Source,
             'Regular' as 'Type'
             ,'20 Days or More' as 'AgeGroup'
             ,0 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays >= 20 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays >= 20 THEN 1 ELSE 0 END ) as 'RecordCount'
             
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 
 SELECT 
             App_Source,
             'Regular' as 'Type'
             ,'21 – 60 Days' as 'AgeGroup'
             ,0.8 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 60 AND AgeInDays >=21 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 60 AND AgeInDays >=21 THEN 1 ELSE 0 END ) as 'RecordCount'
             
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 SELECT 
             App_Source
             ,'Regular' as 'Type'
             ,'61 – 120 Days' as 'AgeGroup'
             ,0.9 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 120 AND AgeInDays >=61 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 120 AND AgeInDays >=61 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
             
 SELECT 
             App_Source
             ,'Regular' as 'Type'
             ,'Days 121 - 180' as 'AgeGroup'
             ,0.95 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 180 AND AgeInDays >=121 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 180 AND AgeInDays >=121 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 SELECT 
             App_Source
             ,'Regular' as 'Type'
             ,'Greater Than 180 Days' as 'AgeGroup'
             ,0.05 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays > 180 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays > 180 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL
 GROUP BY App_Source 
 
 UNION ALL
 
 -- Paper – Revalidation
 
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'20 Days or More' as 'AgeGroup'
             ,0 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays >= 20 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays >= 20 THEN 1 ELSE 0 END ) as 'RecordCount'
             
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'21 – 60 Days' as 'AgeGroup'
             ,0.8 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 60 AND AgeInDays >=21 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 60 AND AgeInDays >=21 THEN 1 ELSE 0 END ) as 'RecordCount'
             
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'61 – 120 Days' as 'AgeGroup'
             ,0.9 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 120 AND AgeInDays >=61 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 120 AND AgeInDays >=61 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
             
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'Days 121 - 180'  as 'AgeGroup'
             ,0.95 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 180 AND AgeInDays >=121 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 180 AND AgeInDays >=121 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'Greater Than 180 Days' as 'AgeGroup'
             ,0.05 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays > 180 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays > 180 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'PAPER' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL
 GROUP BY App_Source
 
 UNION ALL
 
 -- WEB – Regular
 
 SELECT 
             App_Source,
             'Regular' as 'Type'
             ,'15 Days or More' as 'AgeGroup'
             ,0 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays >= 15 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays >= 15 THEN 1 ELSE 0 END ) as 'RecordCount'
             
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 
 SELECT 
             App_Source,
             'Regular' as 'Type'
             ,'16 - 45 Days' as 'AgeGroup'
             ,0.8 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 45 AND AgeInDays >=16 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 45 AND AgeInDays >=16 THEN 1 ELSE 0 END ) as 'RecordCount'
             
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 SELECT 
             App_Source
             ,'Regular' as 'Type'
             ,'46 - 60 Days' as 'AgeGroup'
             ,0.9 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 60 AND AgeInDays >=46 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 60 AND AgeInDays >=46 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
             
 SELECT 
             App_Source
             ,'Regular' as 'Type'
             ,'61 - 90 Days' as 'AgeGroup'
             ,0.95 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 90 AND AgeInDays >=61 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 90 AND AgeInDays >=61 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 SELECT 
             App_Source
             ,'Regular' as 'Type'
             ,'Greater Than 90 Days' as 'AgeGroup'
             ,0.05 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays > 90 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays > 90 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category <> 'REVAL' AND PE_Resolution_Date IS NULL
 GROUP BY App_Source 
 
 UNION ALL
 
 -- WEB – Revalidation
 
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'15 Days or More' as 'AgeGroup'
             ,0 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays >= 15 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays >= 15 THEN 1 ELSE 0 END ) as 'RecordCount'
             
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'16 - 45 Days' as 'AgeGroup'
             ,0.8 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 45 AND AgeInDays >=16 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 45 AND AgeInDays >=16 THEN 1 ELSE 0 END ) as 'RecordCount'
             
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'46 - 60 Days' as 'AgeGroup'
             ,0.9 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 60 AND AgeInDays >=46 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 60 AND AgeInDays >=46 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
             
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'61 - 90 Days' as 'AgeGroup'
             ,0.95 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays <= 90 AND AgeInDays >=61 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays <= 90 AND AgeInDays >=61 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL 
 GROUP BY App_Source
 
 UNION ALL
 
 SELECT 
             App_Source
             ,'Revalidation' as 'Type'
             ,'Greater Than 90 Days' as 'AgeGroup'
             ,0.05 as 'Target'
             ,1.0*SUM( CASE WHEN AgeInDays > 90 THEN 1 ELSE 0 END )/COUNT(*) as 'Performance'
             ,COUNT(*) as 'Overall'
             ,SUM( CASE WHEN AgeInDays > 90 THEN 1 ELSE 0 END ) as 'RecordCount'
 
 FROM @tempTable
 WHERE App_Source = 'WEB' AND Category = 'REVAL' AND PE_Resolution_Date IS NULL
 GROUP BY App_Source
more ▼

asked Jul 14 at 11:34 AM in Default

avatar image

Gehima2016
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

For Category, this is straightforward - follow the pattern you have used already.

For Contract_Region - be careful. You've got contractregion already declared - is this the same?

more ▼

answered Jul 14 at 11:44 AM

avatar image

ThomasRushton ♦♦
41.7k 20 50 53

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x448

asked: Jul 14 at 11:34 AM

Seen: 17 times

Last Updated: Jul 14 at 11:44 AM

Copyright 2017 Redgate Software. Privacy Policy