DWOperations
 usp_DimInstitutionalRetentionCohortLoad_DupKeyCheck (Stored Procedure)
Home  Diagrams  Documents  Images  DWOperations  Storage  Users  Roles  Schemas  Tables  Views  Indexes  Triggers  Stored Procedures  Functions  Data Types  XML Schemas  Rules  Defaults  Fulltext Catalogs  Synonyms  Keys  Partitions  Assemblies 
Current Path:  DWOperations   Stored Procedures   usp_DimInstitutionalRetentionCohortLoad_DupKeyCheck
  Properties
Property Value
Name usp_DimInstitutionalRetentionCohortLoad_DupKeyCheck
Schema dbo
Is Encrypted False
Ansi Nulls Status True
Quoted Identifier Status True
Description
  Parameters
Name Data Type Direction Description
  Parent Dependencies (objects that usp_DimInstitutionalRetentionCohortLoad_DupKeyCheck depends on)
Name Type
Table
  Child Dependencies (objects that depend on usp_DimInstitutionalRetentionCohortLoad_DupKeyCheck)
Name Type
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: StoredProcedure [dbo].[usp_DimInstitutionalRetentionCohortLoad_DupKeyCheck] Script Date: 03/09/2017 17:21:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* Primary Student Term Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_DimInstitutionalRetentionCohortLoad_DupKeyCheck]
AS 
SET NOCOUNT ON
-- Create in-memory table for later use within this proc
CREATE TABLE #Stdnt_Cohort_Dup
    (
    [InstitutionalRetentionCohortAK] VARCHAR(30) NOT NULL
    )
-- populate table with duplicate StudentSuccessCohortAK values
INSERT INTO #Stdnt_Cohort_Dup
    (
    [InstitutionalRetentionCohortAK]
    )
    select InstitutionalRetentionCohortAK
    from Verify_dbo_DimInstitutionalRetentionCohort
    group by InstitutionalRetentionCohortAK
    having COUNT(InstitutionalRetentionCohortAK) > 1
    
--Detect duplicate records and set flag
UPDATE Verify_dbo_DimInstitutionalRetentionCohort
SET RowIsDuplicate = 'Y'
FROM Verify_dbo_DimInstitutionalRetentionCohort
WHERE InstitutionalRetentionCohortAK 
IN
 (
 select InstitutionalRetentionCohortAK
 from #Stdnt_Cohort_Dup
 );
--Set RowIsSelected = N for duplicated records (detected above)
UPDATE RecordsToIgnore
SET RowIsSelected = 'N'
FROM dbo.Verify_dbo_DimInstitutionalRetentionCohort RecordsToIgnore
INNER JOIN
 (
 --Returns the first record for a set of duplicated StudentTerms
 --(as defined as minimum of surrogate key of temp table)
 SELECT InstitutionalRetentionCohortAK, MIN(VerifyInstitutionalRetentionCohortSK) as Min_VerifyInstitutionalRetentionCohortSK
 FROM Verify_dbo_DimInstitutionalRetentionCohort
 WHERE InstitutionalRetentionCohortAK in
  (
    select InstitutionalRetentionCohortAK
    from #Stdnt_Cohort_Dup
  ) 
 GROUP BY InstitutionalRetentionCohortAK 
 ) as Keeper
ON (
 Keeper.InstitutionalRetentionCohortAK = RecordsToIgnore.InstitutionalRetentionCohortAK
 AND
 Keeper.Min_VerifyInstitutionalRetentionCohortSK <> RecordsToIgnore.VerifyInstitutionalRetentionCohortSK
 )
WHERE RecordsToIgnore.RowIsDuplicate = 'Y';
Powered by BI Documenter