Stored Procedure - usp_FactEnrollmentSnapshot_TermRelativeDays

Property	Value
Name	usp_FactEnrollmentSnapshot_TermRelativeDays_LOAD
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name	Data Type	Direction	Description
@CurrentDayDate	date(10, 0)	Input
@NumberOfDays	int(10, 0)	Input

Name	Type
DimRelativeDay_CopyFromDW	Table
FactEnrollmentSnapshot_TermRelativeDays	Table
Staged_dbo_DimTerm	Table

Name	Type
FactEnrollmentSnapshot_TermRelativeDays	TABLE
Staged_dbo_DimTerm	TABLE
DimRelativeDay_CopyFromDW	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactEnrollmentSnapshot_TermRelativeDays_LOAD] Script Date: 03/09/2017 17:21:49 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            Craig Love
Date:            4/05/2013
Company:        PragmaticWorks
Description:    Procedure emtpies and loads table FactEnrollmentSnapshot_TermRelativeDays
    with the Terms and RelativeDays and Dates for the Active Terms and the prior two years
    for each of the Active Terms.

    Active Terms is determined by looking at the table DWOperations.dbo.Staged_dbo_DimTerm

    For example:
    @CurrentDayDate = '3/28/2013', @NumberOfDays = 7

    For the Active Term 20141, the procedure will populate the table FactEnrollmentSnapshot_TermRelativeDays
    with @NumberOfDays entries for the 20141 term starting with the ActiveDay as of 3/28/2013 and the
    previous (@NumberOfDays - 1) days.

    For sake of this example, assume the 20141 RelativeDays are -148 (3/28/2013) through -154 (3/22/2018).
    The    script will look at the previous year's same term (20131) and insert @NumberOfDays more records
    for the same RelativeDays.

*/
CREATE PROCEDURE usp_FactEnrollmentSnapshot_TermRelativeDays_LOAD
@CurrentDayDate date = '3/28/2013',
@NumberOfDays int = 7
AS
SET NOCOUNT ON
TRUNCATE TABLE FactEnrollmentSnapshot_TermRelativeDays
DECLARE @Term varchar(6)
DECLARE curActiveTerms CURSOR FAST_FORWARD
FOR
    SELECT CAST(TermAK as varchar(6)) as TermResult
    FROM dbo.Staged_dbo_DimTerm
    WHERE ActiveTerm = 'Yes'
OPEN curActiveTerms
FETCH NEXT FROM curActiveTerms INTO @Term
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @RelativeDayNumber_Max int
    DECLARE @RelativeDayNumber_Min int
    SELECT @RelativeDayNumber_Max = RelativeDayNumber
    FROM dbo.DimRelativeDay_CopyFromDW rd
    WHERE Term = @Term
    AND RelativeDayDate = @CurrentDayDate
    SET @RelativeDayNumber_Min =
            CASE
            WHEN @RelativeDayNumber_Max - @NumberOfDays + 1 >= -365 THEN @RelativeDayNumber_Max - @NumberOfDays + 1
            ELSE -365
        END
    --(Current Year)
    INSERT INTO FactEnrollmentSnapshot_TermRelativeDays
    SELECT rd.Term, rd.RelativeDayNumber, rd.RelativeDayDate, @Term
    FROM dbo.DimRelativeDay_CopyFromDW rd
    WHERE Term = @Term
    AND RelativeDayNumber BETWEEN @RelativeDayNumber_Min AND @RelativeDayNumber_MAX
    --(Current Year - 1)
    INSERT INTO FactEnrollmentSnapshot_TermRelativeDays
    SELECT rd.Term, rd.RelativeDayNumber, rd.RelativeDayDate, @Term
    FROM dbo.DimRelativeDay_CopyFromDW rd
    WHERE Term = CAST((CAST(LEFT(@Term, 4) AS Int) -  1) AS varchar(4)) + RIGHT(@Term,LEN(@Term) -4)
    AND RelativeDayNumber BETWEEN @RelativeDayNumber_Min AND @RelativeDayNumber_MAX
    --(Current Year - 2)
    INSERT INTO FactEnrollmentSnapshot_TermRelativeDays
    SELECT rd.Term, rd.RelativeDayNumber, rd.RelativeDayDate, @Term
    FROM dbo.DimRelativeDay_CopyFromDW rd
    WHERE Term = CAST((CAST(LEFT(@Term, 4) AS Int) -  2) AS varchar(4)) + RIGHT(@Term,LEN(@Term) -4)
    AND RelativeDayNumber BETWEEN @RelativeDayNumber_Min AND @RelativeDayNumber_MAX
    FETCH NEXT FROM curActiveTerms INTO @Term
END
CLOSE curActiveTerms
DEALLOCATE curActiveTerms