Stored Procedure - usp_FactActiveEnrollmentSnapshot

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

Name	Data Type	Direction	Description
@CurrentDayDate	datetime(23, 3)	Input
@Term	varchar(6)	Input

Name	Type
ST_STDNT_CLS_LOG	Table

Name	Type
ST_STDNT_CLS_LOG	TABLE
udf_StringToDate	USERDEFINEDFUNCTION

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactActiveEnrollmentSnapshot_FullSelect] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* 10/22/2013 Sophia Cowan - Change usp all Select of ST_STDNT_CLS_LOG to include ISNULL for LOG_TIME
LOG_TIME of NULL (midnight) was not processed correctly
*/
CREATE PROC [dbo].[usp_FactActiveEnrollmentSnapshot_FullSelect]
@CurrentDayDate datetime
, @Term varchar(6)
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
          CAST('aaa' AS varchar(9)) AS STDNT_ID
        , CAST('aaa' AS varchar(6)) AS EFF_TRM
        , CAST(123 AS int) AS REF_NUM
        , CAST(2011-04-21 as datetime) as EnrollmentDate
        , CAST(1 as Int) as Enrolled
        , CAST(1 as Int) as PaidEnrollment
    END

CREATE TABLE #Stdnt_Term_Ref
    (
    [STDNT_ID] VARCHAR(9)  NOT NULL
    ,[EFF_TRM] VARCHAR(6) NOT NULL
    ,[REF_NUM] int NOT NULL
    ,MaxLogDtTm BIGINT NOT NULL
    ,Enrolled INT NULL
    ,PaidEnrollment INT NULL
    )
CREATE TABLE #Stdnt_Term_Ref_PaidFlag
    (
    [STDNT_ID] VARCHAR(9)  NOT NULL
    ,[EFF_TRM] VARCHAR(6) NOT NULL
    ,[REF_NUM] int NOT NULL
    ,MaxLogDtTm BIGINT NOT NULL
    ,Enrolled INT NULL
    ,PaidEnrollment INT NULL
    ,NotPaidFlag INT NULL
    )

INSERT INTO #Stdnt_Term_Ref
    (
    [STDNT_ID]
    ,[EFF_TRM]
    ,[REF_NUM]
    ,MaxLogDtTm
    )
     SELECT   [STDNT_ID]
             ,[EFF_TRM]
             ,[REF_NUM]  --Determines if class log
             ,MAX((CAST([LOG_DATE] AS bigint) * 10000000) + ISNULL([LOG_TIME],0)) AS MaxLogDtTm
     FROM [dbo].[ST_STDNT_CLS_LOG] clslog_inner
           WHERE EFF_TRM >= @Term
           AND  [REF_NUM] IS NOT NULL
           AND dbo.udf_StringToDate([LOG_DATE]) <=  @CurrentDayDate
     GROUP BY [STDNT_ID],[EFF_TRM],[REF_NUM]
    ORDER BY [STDNT_ID],[EFF_TRM],[REF_NUM], MaxLogDtTm

INSERT INTO #Stdnt_Term_Ref_PaidFlag
    (
    [STDNT_ID]
    ,[EFF_TRM]
    ,[REF_NUM]
    ,MaxLogDtTm
    ,NotPaidFlag
    )
     SELECT   #Stdnt_Term_Ref.[STDNT_ID]
             ,#Stdnt_Term_Ref.[EFF_TRM]
             ,#Stdnt_Term_Ref.[REF_NUM]  --Determines if class log
             ,MaxLogDtTm
             ,(CASE
                WHEN NOT_PAID_FLG = 1 THEN 1
                ELSE 0
                END) as NotPaidFlag
     FROM #Stdnt_Term_Ref
     inner join [dbo].[ST_STDNT_CLS_LOG] clslog_inner
     on
       (
         clslog_inner.STDNT_ID = #Stdnt_Term_Ref.STDNT_ID
        AND clslog_inner.EFF_TRM =  #Stdnt_Term_Ref.EFF_TRM
        AND clslog_inner.REF_NUM = #Stdnt_Term_Ref.REF_NUM
        AND CAST(clslog_inner.[LOG_DATE] AS bigint) * 10000000 + ISNULL(clslog_inner.[LOG_TIME],0)  = #Stdnt_Term_Ref.MaxLogDtTm
       )

UPDATE #Stdnt_Term_Ref_PaidFlag
SET
        #Stdnt_Term_Ref_PaidFlag.Enrolled =
            (CASE
                WHEN SSCL.[TRNSCTN_TY] = 'D' THEN 0
                ELSE 1
                End ) -- Enrolled
        ,#Stdnt_Term_Ref_PaidFlag.PaidEnrollment =
            (CASE
                WHEN SSCL.NOT_PAID_FLG = 1 THEN 0
                ELSE 1
                END) -- PaidEnrollment
FROM #Stdnt_Term_Ref_PaidFlag
INNER JOIN dbo.ST_STDNT_CLS_LOG SSCL
    ON (
        SSCL.STDNT_ID = #Stdnt_Term_Ref_PaidFlag.STDNT_ID
        AND SSCL.EFF_TRM =  #Stdnt_Term_Ref_PaidFlag.EFF_TRM
        AND SSCL.REF_NUM = #Stdnt_Term_Ref_PaidFlag.REF_NUM
        AND CAST(SSCL.[LOG_DATE] AS bigint) * 10000000 + ISNULL(SSCL.[LOG_TIME],0)  = #Stdnt_Term_Ref_PaidFlag.MaxLogDtTm
        and CAST(SSCL.NOT_PAID_FLG as Int) = #Stdnt_Term_Ref_PaidFlag.NotPaidFlag
        )
SELECT
  #Stdnt_Term_Ref_PaidFlag.STDNT_ID
, #Stdnt_Term_Ref_PaidFlag.EFF_TRM
, #Stdnt_Term_Ref_PaidFlag.REF_NUM
, CAST(@CurrentDayDate as datetime) AS EnrollmentDate
, 1 AS Enrolled
, MAX(#Stdnt_Term_Ref_PaidFlag.PaidEnrollment) AS PaidEnrollment
FROM #Stdnt_Term_Ref_PaidFlag
WHERE Enrolled = 1
GROUP BY #Stdnt_Term_Ref_PaidFlag.STDNT_ID
        ,#Stdnt_Term_Ref_PaidFlag.EFF_TRM
        ,#Stdnt_Term_Ref_PaidFlag.REF_NUM
        ,#Stdnt_Term_Ref_PaidFlag.MaxLogDtTm
ORDER BY #Stdnt_Term_Ref_PaidFlag.REF_NUM