SQLize | PHPize | SQLtest

use RA1048346_Archive IF OBJECT_ID('tempdb..#DimProperty') IS NOT NULL DROP TABLE [#DimProperty]; IF OBJECT_ID('tempdb..#ResidentMember') IS NOT NULL DROP TABLE [#ResidentMember]; IF OBJECT_ID('tempdb..#DimUnit') IS NOT NULL DROP TABLE [#DimUnit]; IF OBJECT_ID('tempdb..#DimAffordableCertification') IS NOT NULL DROP TABLE [#DimAffordableCertification]; IF OBJECT_ID('tempdb..#NARAffordableCertification') IS NOT NULL DROP TABLE [#NARAffordableCertification]; IF OBJECT_ID('tempdb..#MaxDimAffordableCertification') IS NOT NULL DROP TABLE [#MaxDimAffordableCertification]; IF OBJECT_ID('tempdb..#FinalMaxDimAffordableCertification') IS NOT NULL DROP TABLE [#FinalMaxDimAffordableCertification]; IF OBJECT_ID('tempdb..#DimAffordableNotices') IS NOT NULL DROP TABLE [#DimAffordableNotices]; IF OBJECT_ID('tempdb..#Notices') IS NOT NULL DROP TABLE [#Notices]; IF OBJECT_ID('tempdb..#Recertification') IS NOT NULL DROP TABLE [#Recertification]; IF OBJECT_ID('tempdb..#AllRecerts') IS NOT NULL DROP TABLE [#AllRecerts]; IF OBJECT_ID('tempdb..#ResidentStatus') IS NOT NULL DROP TABLE [#ResidentStatus]; IF OBJECT_ID('tempdb..#FinalResidentStatus') IS NOT NULL DROP TABLE [#FinalResidentStatus]; SELECT [PropertyKey], [PropertyName], [PropertySourceCode] INTO [#DimProperty] FROM [DimProperty] WHERE [PropertyKey] NOT IN ( -1, -2 ) AND [IsDeleted] <> 'Y' AND [PropertyKey] in (98 , 108, 86) ; -----Changed the prop number ----------------------------------------------------------------------------------------------------------------------------- --Select * FROM [#DimProperty] SELECT [PropertyKey], [ResidentHouseHoldID], [ResidentMemberKey], [LastName], [FirstName], [IsCurrentResident] INTO [#ResidentMember] FROM [DimResidentMember] WHERE [CDSExtractType] <> 'D' AND [RelationShipCode] = 'H' AND [IsCurrentResident] = 1; --Select * FROM [#ResidentMember] SELECT DISTINCT [PropertyKey], [osl_UnitID], [BuildingNumber], [UnitNumber] INTO [#DimUnit] FROM [DimUnit] WHERE [IsDeleted] <> 'Y' AND RowIsCurrent = 'Y'; --Select * FROM [#DimUnit] SELECT [C].[PropertyKey], [P].[PropertySourceCode], [CertificationKey], CASE WHEN [CertificationCenter] LIKE '%Compliance%' THEN 'HUD' WHEN [CertificationCenter] LIKE '%TaxCredit%' OR [CertificationCenter] LIKE '%Tax Credit%' THEN 'Tax Credit' WHEN [CertificationCenter] LIKE '%RHS%' THEN 'Rural Housing' WHEN [CertificationCenter] LIKE '%PH50058%' THEN '50058' ELSE CertificationCenter END AS CertificationCenter, [osl_CertificationCenterID], [osl_CertificationTypeID], [osl_CertificationStatusID], [unitID], [osl_ResidentHouseholdID], RTRIM(LTRIM([Program])) AS [Program], [CertificationStatus], [CertificationCreateDate], [CertificationCompletedDate], [LastCertificationDate], [NextCertification], [InterviewDate], ISNULL([ProduceNARCert],0) AS [ProduceNARCert], [Market], [Waiver], [IsCurrentResident] INTO [#DimAffordableCertification] FROM [DimAffordableCertification] C INNER JOIN [#ResidentMember] R -- ON C.PropertyKey = R.PropertyKey AND C.ResidentMemberKey = R.ResidentMemberKey ON C.PropertyKey = R.PropertyKey AND C.osl_ResidentHouseholdID = R.ResidentHouseHoldID INNER JOIN [#DimProperty] P ON C.PropertyKey = P.PropertyKey WHERE [IsDeleted] <> 'Y' AND IsCurrentResident =1 and PropertySourceCode = 1 UNION SELECT [C].[PropertyKey], [P].[PropertySourceCode], [CertificationKey], CASE WHEN [CertificationCenter] LIKE '%Compliance%' THEN 'HUD' WHEN [CertificationCenter] LIKE '%TaxCredit%' OR [CertificationCenter] LIKE '%Tax Credit%' THEN 'Tax Credit' WHEN [CertificationCenter] LIKE '%RHS%' THEN 'Rural Housing' WHEN [CertificationCenter] LIKE '%PH50058%' THEN '50058' ELSE CertificationCenter END AS CertificationCenter, [osl_CertificationCenterID], [osl_CertificationTypeID], [osl_CertificationStatusID], [unitID], [osl_ResidentHouseholdID], RTRIM(LTRIM([Program])) AS [Program], [CertificationStatus], [CertificationCreateDate], [CertificationCompletedDate], [LastCertificationDate], [NextCertification], [InterviewDate], ISNULL([ProduceNARCert],0) AS [ProduceNARCert], [Market], [Waiver], [IsCurrentResident] --INTO -- [#DimAffordableCertification] FROM [DimAffordableCertification] C INNER JOIN [#ResidentMember] R ON C.PropertyKey = R.PropertyKey AND C.ResidentMemberKey = R.ResidentMemberKey --ON C.PropertyKey = R.PropertyKey AND C.osl_ResidentHouseholdID = R.ResidentHouseHoldID INNER JOIN [#DimProperty] P ON C.PropertyKey = P.PropertyKey WHERE [IsDeleted] <> 'Y' AND IsCurrentResident =1 and PropertySourceCode = 2 UPDATE AC SET Market = 1 FROM #DimAffordableCertification AC WHERE Program = 'Exempt' --and certificationstatus <> 'Trial' --and LastCertificationDate Between DATEFROMPARTS(Year(DATEADD(Day,-1,GETDATE()))-1,1,1) and DATEADD(Day,-1,GETDATE()) --osl_CertificationStatusID --ResidentName --select * from [#DimAffordableCertification] CREATE TABLE #MaxDimAffordableCertification (PropertyKey INT, PropertySourceCode INT, CertificationKey BIGINT, CertificationCenter VARCHAR(100), osl_CertificationCenterID INT, osl_CertificationTypeID VARCHAR(100), osl_CertificationStatusID INT, UnitID INT, osl_ResidentHouseholdID VARCHAR(50), Program VARCHAR(MAX), CertificationStatus VARCHAR(100), CertificationCreateDate DATETIME, CertificationCompletedDate DATETIME, LastCertificationDate DATETIME, NextCertification DATETIME, InterviewDate DATETIME, ProduceNARCert BIT, [Market] BIT, [Waiver] BIT, RankOrder SMALLINT, ) INSERT INTO #MaxDimAffordableCertification SELECT PropertyKey, PropertySourceCode, CertificationKey, CertificationCenter, osl_CertificationCenterID, osl_CertificationTypeID, osl_CertificationStatusID, UnitID, osl_ResidentHouseholdID, ISNULL(Program, '') AS Program, NULL AS CertificationStatus, CertificationCreateDate, CertificationCompletedDate, LastCertificationDate, NextCertification, InterviewDate, ProduceNARCert, Market, Waiver, RANK() OVER(PARTITION BY PropertyKey, UnitID, osl_ResidentHouseholdID, osl_CertificationCenterID ORDER BY CertificationCreateDate DESC) AS RankOrder FROM [#DimAffordableCertification] WHERE PropertySourceCode = 1 AND [IsCurrentResident] = 1 AND ProduceNARCert = 1 --Select * from #MaxDimAffordableCertification UPDATE MAC SET MAC.CertificationStatus = DAC.CertificationStatus --Select MAC.* FROM #MaxDimAffordableCertification MAC INNER JOIN #DimAffordableCertification DAC ON MAC.PropertyKey = DAC.PropertyKey AND MAC.osl_ResidentHouseholdID = DAC.osl_ResidentHouseholdID Collate SQL_Latin1_General_CP1_CI_AS AND MAC.UnitID = DAC.UnitID AND MAC.osl_CertificationCenterID = DAC.osl_CertificationCenterID WHERE DAC.CertificationCreateDate = (SELECT MAX(AC.CertificationCreateDate) FROM #DimAffordableCertification AC WHERE AC.PropertyKey = MAC.PropertyKey AND AC.osl_ResidentHouseholdID = MAC.osl_ResidentHouseholdID Collate SQL_Latin1_General_CP1_CI_AS AND AC.UnitID = MAC.UnitID AND AC.osl_CertificationCenterID = MAC.osl_CertificationCenterID --AND AC.CertificationCompletedDate IS NULL AND AC.osl_CertificationTypeID = CASE WHEN MAC.CertificationCenter = 'Tax Credit' THEN '3' WHEN MAC.CertificationCenter = 'HUD' THEN 'AR' WHEN MAC.CertificationCenter = 'Rural Housing' THEN 'R' WHEN MAC.CertificationCenter = '50058' THEN '2' END ) --Select * from #MaxDimAffordableCertification INSERT INTO #MaxDimAffordableCertification SELECT PropertyKey, PropertySourceCode, CertificationKey, CertificationCenter, osl_CertificationCenterID, osl_CertificationTypeID, osl_CertificationStatusID, UnitID, osl_ResidentHouseholdID, ISNULL(Program, '') AS Program, CertificationStatus, CertificationCreateDate,CertificationCompletedDate, LastCertificationDate, NextCertification, InterviewDate, ProduceNARCert, Market, Waiver, 1 AS RankOrder FROM [#DimAffordableCertification] WHERE PropertySourceCode = 2 AND osl_CertificationCenterID IN (1,2,6,7) AND osl_CertificationStatusID IN (1,2) AND IsCurrentResident=1 --Select * from #MaxDimAffordableCertification SELECT * INTO #FinalMaxDimAffordableCertification FROM #MaxDimAffordableCertification WHERE RankOrder = 1 --Select * from #FinalMaxDimAffordableCertification SELECT [PropertyKey], [CertificationKey], (Case when LetterSequence = 1 Then 'Initial Notice' When LetterSequence = 2 Then 'Second Notice' When LetterSequence = 3 Then 'Third Notice' When LetterSequence = 4 Then 'Fourth Notice' END ) as [LetterSent], [LetterGenerated] INTO [#DimAffordableNotices] FROM [vwDimAffordableNotices] WHERE [IsDeleted] <> 'Y'; --Select * from [#DimAffordableNotices] SELECT * INTO [#Notices] FROM [#DimAffordableNotices] PIVOT ( MAX([LetterGenerated]) FOR [LetterSent] IN ( [Initial Notice], [Second Notice], [Third Notice], [Fourth Notice] ) ) AS [PT]; --Select * from [#Notices] CREATE TABLE [#Recertification] ( [PropertyKey] INT, [CertificationKey] INT, [CertificationCenter] VARCHAR(100), [UnitID] INT, [ResidentHouseholdID] VARCHAR(50), [Status] VARCHAR(25), [Program] VARCHAR(MAX), [CertificationStatus] VARCHAR(100), [LastCertificationDate] DATE, [CertificationCompletedDate] DATE, [NextCertificationDate] DATE, [InterviewDate] DATE, [RecertMarket] BIT, [RecertWaiver] BIT ); INSERT INTO [#Recertification] SELECT [PropertyKey], [CertificationKey], [CertificationCenter], [unitID], [osl_ResidentHouseholdID], 'Past Due', [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertification], [InterviewDate], [Market], [Waiver] FROM [#FinalMaxDimAffordableCertification] WHERE ( ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '34' AND [osl_CertificationTypeID] IN ( '1', '2', '3' ) AND [NextCertification] < CAST(CURRENT_TIMESTAMP AS DATE) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '41' AND [osl_CertificationTypeID] IN ( 'A', 'B', 'C', 'GR', 'I', 'M', 'R', 'S', 'T', 'XA', 'XB', 'XC', 'XE', 'XEB', 'XET', 'XI', 'XR', 'XS', 'XT', 'XV' ) AND [NextCertification] < CAST(CURRENT_TIMESTAMP AS DATE) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '72' AND [osl_CertificationTypeID] IN ( '1', '2', '3', '7', '8', '14', '15' ) AND [NextCertification] < CAST(CURRENT_TIMESTAMP AS DATE) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '6' AND [osl_CertificationTypeID] IN ( 'MI', 'UT', 'AR', 'IR', 'GR', 'TR', 'TI', 'TF', 'CE', 'DS', 'ST', 'ND', 'AB', 'RR', 'NS', 'OT', 'IC', 'TC' ) AND [NextCertification] < CAST(CURRENT_TIMESTAMP AS DATE) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '1' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] < CAST(CURRENT_TIMESTAMP AS DATE)) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] < CAST(CURRENT_TIMESTAMP AS DATE)) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '2' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] < CAST(CURRENT_TIMESTAMP AS DATE)) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] < CAST(CURRENT_TIMESTAMP AS DATE)) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '6' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] < CAST(CURRENT_TIMESTAMP AS DATE)) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] < CAST(CURRENT_TIMESTAMP AS DATE)) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '7' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] < CAST(CURRENT_TIMESTAMP AS DATE)) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] < CAST(CURRENT_TIMESTAMP AS DATE)) ) ) ); --Select * from [#Recertification] INSERT INTO [#Recertification] SELECT [PropertyKey], [CertificationKey], [CertificationCenter], [unitID], [osl_ResidentHouseholdID], '0-30 Days', [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertification], [InterviewDate], [Market], [Waiver] FROM [#FinalMaxDimAffordableCertification] WHERE ( ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '34' AND [osl_CertificationTypeID] IN ( '1', '2', '3' ) AND [NextCertification] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '41' AND [osl_CertificationTypeID] IN ( 'A', 'B', 'C', 'GR', 'I', 'M', 'R', 'S', 'T', 'XA', 'XB', 'XC', 'XE', 'XEB', 'XET', 'XI', 'XR', 'XS', 'XT', 'XV' ) AND [NextCertification] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '72' AND [osl_CertificationTypeID] IN ( '1', '2', '3', '7', '8', '14', '15' ) AND [NextCertification] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '6' AND [osl_CertificationTypeID] IN ( 'MI', 'UT', 'AR', 'IR', 'GR', 'TR', 'TI', 'TF', 'CE', 'DS', 'ST', 'ND', 'AB', 'RR', 'NS', 'OT', 'IC', 'TC' ) AND [NextCertification] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '1' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '2' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '6' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '7' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN CAST(CURRENT_TIMESTAMP AS DATE) AND DATEADD( DAY, 29, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) ); INSERT INTO [#Recertification] SELECT [PropertyKey], [CertificationKey], [CertificationCenter], [unitID], [osl_ResidentHouseholdID], '31-60 Days', [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertification], [InterviewDate], [Market], [Waiver] FROM [#FinalMaxDimAffordableCertification] WHERE ( ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '34' AND [osl_CertificationTypeID] IN ( '1', '2', '3' ) AND [NextCertification] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '41' AND [osl_CertificationTypeID] IN ( 'A', 'B', 'C', 'GR', 'I', 'M', 'R', 'S', 'T', 'XA', 'XB', 'XC', 'XE', 'XEB', 'XET', 'XI', 'XR', 'XS', 'XT', 'XV' ) AND [NextCertification] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '72' AND [osl_CertificationTypeID] IN ( '1', '2', '3', '7', '8', '14', '15' ) AND [NextCertification] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '6' AND [osl_CertificationTypeID] IN ( 'MI', 'UT', 'AR', 'IR', 'GR', 'TR', 'TI', 'TF', 'CE', 'DS', 'ST', 'ND', 'AB', 'RR', 'NS', 'OT', 'IC', 'TC' ) AND [NextCertification] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '1' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '2' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '6' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '7' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 30, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 59, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) ); INSERT INTO [#Recertification] SELECT [PropertyKey], [CertificationKey], [CertificationCenter], [unitID], [osl_ResidentHouseholdID], '61-90 Days', [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertification], [InterviewDate], [Market], [Waiver] FROM [#FinalMaxDimAffordableCertification] WHERE ( ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '34' AND [osl_CertificationTypeID] IN ( '1', '2', '3' ) AND [NextCertification] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '41' AND [osl_CertificationTypeID] IN ( 'A', 'B', 'C', 'GR', 'I', 'M', 'R', 'S', 'T', 'XA', 'XB', 'XC', 'XE', 'XEB', 'XET', 'XI', 'XR', 'XS', 'XT', 'XV' ) AND [NextCertification] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '72' AND [osl_CertificationTypeID] IN ( '1', '2', '3', '7', '8', '14', '15' ) AND [NextCertification] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '6' AND [osl_CertificationTypeID] IN ( 'MI', 'UT', 'AR', 'IR', 'GR', 'TR', 'TI', 'TF', 'CE', 'DS', 'ST', 'ND', 'AB', 'RR', 'NS', 'OT', 'IC', 'TC' ) AND [NextCertification] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '1' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '2' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '6' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '7' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 60, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 89, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) ); INSERT INTO [#Recertification] SELECT [PropertyKey], [CertificationKey], [CertificationCenter], [unitID], [osl_ResidentHouseholdID], '91-120 Days', [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertification], [InterviewDate], [Market], [Waiver] FROM [#FinalMaxDimAffordableCertification] WHERE ( ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '34' AND [osl_CertificationTypeID] IN ( '1', '2', '3' ) AND [NextCertification] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '41' AND [osl_CertificationTypeID] IN ( 'A', 'B', 'C', 'GR', 'I', 'M', 'R', 'S', 'T', 'XA', 'XB', 'XC', 'XE', 'XEB', 'XET', 'XI', 'XR', 'XS', 'XT', 'XV' ) AND [NextCertification] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '72' AND [osl_CertificationTypeID] IN ( '1', '2', '3', '7', '8', '14', '15' ) AND [NextCertification] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '6' AND [osl_CertificationTypeID] IN ( 'MI', 'UT', 'AR', 'IR', 'GR', 'TR', 'TI', 'TF', 'CE', 'DS', 'ST', 'ND', 'AB', 'RR', 'NS', 'OT', 'IC', 'TC' ) AND [NextCertification] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '1' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '2' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '6' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '7' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 90, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 119, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) ); INSERT INTO [#Recertification] SELECT [PropertyKey], [CertificationKey], [CertificationCenter], [unitID], [osl_ResidentHouseholdID], '121-145 Days', [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertification], [InterviewDate], [Market], [Waiver] FROM [#FinalMaxDimAffordableCertification] WHERE ( ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '34' AND [osl_CertificationTypeID] IN ( '1', '2', '3' ) AND [NextCertification] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '41' AND [osl_CertificationTypeID] IN ( 'A', 'B', 'C', 'GR', 'I', 'M', 'R', 'S', 'T', 'XA', 'XB', 'XC', 'XE', 'XEB', 'XET', 'XI', 'XR', 'XS', 'XT', 'XV' ) AND [NextCertification] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '72' AND [osl_CertificationTypeID] IN ( '1', '2', '3', '7', '8', '14', '15' ) AND [NextCertification] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '6' AND [osl_CertificationTypeID] IN ( 'MI', 'UT', 'AR', 'IR', 'GR', 'TR', 'TI', 'TF', 'CE', 'DS', 'ST', 'ND', 'AB', 'RR', 'NS', 'OT', 'IC', 'TC' ) AND [NextCertification] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '1' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '2' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '6' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '7' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 120, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( DAY, 144, CAST(CURRENT_TIMESTAMP AS DATE) )) ) ) ); INSERT INTO [#Recertification] SELECT [PropertyKey], [CertificationKey], [CertificationCenter], [unitID], [osl_ResidentHouseholdID], '145+ Days', [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertification], [InterviewDate], [Market], [Waiver] FROM [#FinalMaxDimAffordableCertification] WHERE ( ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '34' AND [osl_CertificationTypeID] IN ( '1', '2', '3' ) AND [NextCertification] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '41' AND [osl_CertificationTypeID] IN ( 'A', 'B', 'C', 'GR', 'I', 'M', 'R', 'S', 'T', 'XA', 'XB', 'XC', 'XE', 'XEB', 'XET', 'XI', 'XR', 'XS', 'XT', 'XV' ) AND [NextCertification] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '72' AND [osl_CertificationTypeID] IN ( '1', '2', '3', '7', '8', '14', '15' ) AND [NextCertification] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) ) ) OR ( [PropertySourceCode] = 1 AND [osl_CertificationCenterID] = '6' AND [osl_CertificationTypeID] IN ( 'MI', 'UT', 'AR', 'IR', 'GR', 'TR', 'TI', 'TF', 'CE', 'DS', 'ST', 'ND', 'AB', 'RR', 'NS', 'OT', 'IC', 'TC' ) AND [NextCertification] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '1' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '2' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '6' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) )) ) ) OR ( [PropertySourceCode] = 2 AND [osl_CertificationCenterID] = '7' AND ( ([osl_CertificationStatusID] = 1 AND [LastCertificationDate] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) )) OR ([osl_CertificationStatusID] = 2 AND [NextCertification] BETWEEN DATEADD(DAY, 145, CAST(CURRENT_TIMESTAMP AS DATE)) AND DATEADD( YEAR, 2, DATEADD( DD, -1, CAST(CURRENT_TIMESTAMP AS DATE) ) )) ) ) ); --Select * from [#Recertification] SELECT DISTINCT [R].[PropertyKey], [UnitID], [R].[ResidentHouseholdID], [CertificationCenter], [Status], [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertificationDate], [InterviewDate], [Initial Notice], [Second Notice], [Third Notice], [Fourth Notice], [RecertMarket], [RecertWaiver], [LastName] + ', ' + [FirstName] AS [ResidentName] INTO [#AllRecerts] FROM [#Recertification] [R] INNER JOIN [#ResidentMember] [RM] ON [R].[PropertyKey] = [RM].[PropertyKey] AND [R].[ResidentHouseholdID] = [RM].[ResidentHouseHoldID] Collate SQL_Latin1_General_CP1_CI_AS LEFT JOIN [#Notices] [N] ON [R].[PropertyKey] = [N].[PropertyKey] AND [R].[CertificationKey] = [N].[CertificationKey]; --select * from [#AllRecerts] SELECT [R].*, [DL].[NoticeOnDate], RANK() OVER (PARTITION BY [R].[PropertyKey], [R].[ResidentHouseholdID] ORDER BY [DL].[LeaseBeginDate] DESC ) AS [RankOrder] INTO [#ResidentStatus] FROM [#AllRecerts] [R] LEFT JOIN [FactLease] [FL] ON [R].[PropertyKey] = [FL].[PropertyKey] AND [R].[ResidentHouseholdID] = [FL].[residentHouseHoldID] Collate SQL_Latin1_General_CP1_CI_AS LEFT JOIN [DimLeaseAttributes] [DL] ON [FL].[PropertyKey] = [DL].[PropertyKey] AND [FL].[LeaseAttributesKey] = [DL].[LeaseAttributesKey] WHERE [FL].[IsDeleted] <> 'Y' AND [DL].[IsDeleted] <> 'Y' AND [DL].[LeaseRejectedDate] IS NULL AND ([ActualMoveOutDate] IS NULL or [ActualMoveOutDate] >= CAST(GETDATE() as DATE)) AND [DL].[ActualMoveInDate] IS NOT NULL; --Select * from [#ResidentStatus] SELECT DISTINCT [PropertyKey], [UnitID], [ResidentHouseholdID], [CertificationCenter], [Status], [Program], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], [NextCertificationDate], [InterviewDate], [Initial Notice], [Second Notice], [Third Notice], [Fourth Notice], [RecertMarket], [RecertWaiver], [ResidentName], CASE WHEN [NoticeOnDate] IS NULL THEN 'Current' WHEN [NoticeOnDate] IS NOT NULL THEN 'On Notice' END AS [ResidentStatus] INTO [#FinalResidentStatus] FROM [#ResidentStatus] WHERE [RankOrder] = 1; --Select * from [#FinalResidentStatus] SELECT [R].[PropertyKey], [PropertyName], [CertificationCenter], [Status] AS [Due Status], [Program], [BuildingNumber], [UnitNumber], CASE WHEN [BuildingNumber] IS NULL OR [BuildingNumber] = '' OR [BuildingNumber] = 'N/A' THEN [UnitNumber] ELSE [BuildingNumber] + '-' + [UnitNumber] END AS [Unit], [ResidentHouseholdID], [ResidentName], [ResidentStatus], [NextCertificationDate], [CertificationStatus], [LastCertificationDate], [CertificationCompletedDate], ISNULL([RecertMarket],0) AS [RecertMarket], ISNULL([RecertWaiver],0) AS [RecertWaiver], CAST([Initial Notice] AS DATE) AS [InitialNotice], CAST([Second Notice] AS DATE) AS [SecondNotice], CAST([Third Notice] AS DATE) AS [ThirdNotice], CAST([Fourth Notice] AS DATE) AS [FinalNotice], [InterviewDate] FROM [#FinalResidentStatus] [R] LEFT JOIN [#DimUnit] [U] ON [R].[PropertyKey] = [U].[PropertyKey] AND [R].[UnitID] = [U].[osl_UnitID] INNER JOIN [#DimProperty] [P] ON [R].[PropertyKey] = [P].[PropertyKey] --Select * from [#FinalResidentStatus]