SQL Script that aggregates user accrual data from Kronos transaction extract

Kronos-Accruals-Aggregation.sql

DECLARE {
  @company as varchar(4)
  ,@user as varchar(7)
}

WITH
ACCRUALTRANS AS (
  SELECT 
    N.HOMELABORLEVELNM1 AS 'COMPANY'
    ,A.PERSONNUM
    ,A.PERSONFULLNAME
    ,A.ACCRUALCODENAME
    ,A.ACCRUALCODESHORTNM
    ,A.EFFECTIVEDATE
    ,A.ACCRUALTRANTYPE
    ,A.ACCRUALTRANTYPENAME
    ,A.ACCRUALTRANAMOUNT
    ,A.CARRYFORWARDAMOUNT
  FROM [KronosServer].[dbo].VP_ACCRUAL A
    INNER JOIN [KronosServer].[dbo].VP_ALLPERSON N ON
      A.PERSONNUM = N.PERSONNUM
      AND A.PERSONID = N.PERSONID
),
PAYPERIODS_RAW AS (
  SELECT * FROM [KronosServerArchive].[dbo].[PAYPERIOD]
),
PAYPERIODS AS (
  SELECT distinct --[PAYPERIODID]
      --,[PAYRULEID]
      [START_DT]
      ,[END_DT]
      --,[START_DATE]
      --,[END_DATE]
  FROM PAYPERIODS_RAW
  WHERE START_DT <= GETDATE()
  ORDER BY START_DT ASC
),
TRANSACTIONS AS ( -- Returns a result-set of transactions adhering to conditions set below
  SELECT A.PERSONNUM, A.PERSONFULLNAME, A.ACCRUALCODENAME, A.EFFECTIVEDATE, A.ACCRUALTRANTYPE, A.ACCRUALTRANAMOUNT, A.CARRYFORWARDAMOUNT
  FROM ACCRUALTRANS A
  WHERE (A.COMPANY = @company OR @company IS NULL)
  AND (A.PERSONNUM = @user OR @user IS NULL)
  AND A.ACCRUALTRANTYPE IN (1, 2, 22)
),
TRANSACTIONS_PER_PAYPERIOD AS ( -- Organizes transactions based on where they fall in the defined pay periods
-- (REFERRED TO AS TPP IN CTE'S BELOW) --
  SELECT T.PERSONNUM, T.PERSONFULLNAME, T.ACCRUALCODENAME, T.EFFECTIVEDATE, T.ACCRUALTRANTYPE, T.ACCRUALTRANAMOUNT, T.CARRYFORWARDAMOUNT, P.START_DATE, P.END_DATE
  FROM TRANSACTIONS T
    CROSS JOIN [KronosData].[dbo].PAYPERIODS P
  WHERE T.EFFECTIVEDATE BETWEEN P.START_DATE AND P.END_DATE
  AND P.END_DATE BETWEEN '2016/12/25' AND GETDATE()
  GROUP BY T.PERSONNUM, T.PERSONFULLNAME, T.ACCRUALCODENAME, T.EFFECTIVEDATE, T.ACCRUALTRANTYPE, T.ACCRUALTRANAMOUNT, T.CARRYFORWARDAMOUNT, P.START_DATE, P.END_DATE
),
TPP_GRANTS AS ( -- Calculates GRANT accruals per pay period, per accrual code
  SELECT T.PERSONNUM, T.ACCRUALCODENAME, SUM(ACCRUALTRANAMOUNT)/3600 AS 'GRANTED', T.START_DATE, T.END_DATE
  FROM TRANSACTIONS_PER_PAYPERIOD T
  WHERE T.ACCRUALTRANTYPE = 2 
  --AND ACCRUALTRANTYPENAME = 'GRANT'
  GROUP BY T.PERSONNUM, T.ACCRUALCODENAME, T.START_DATE, T.END_DATE
),
TPP_TAKEN AS ( -- Calculates TAKEN accruals per pay period, per accrual code
  SELECT T.PERSONNUM, T.ACCRUALCODENAME, SUM(ACCRUALTRANAMOUNT)/3600 AS 'TAKEN', T.START_DATE, T.END_DATE
  FROM TRANSACTIONS_PER_PAYPERIOD T
  WHERE T.ACCRUALTRANTYPE = 1
  --AND ACCRUALTRANTYPENAME = 'TAKEN'
  GROUP BY T.PERSONNUM, T.ACCRUALCODENAME, T.START_DATE, T.END_DATE
),
TPP_BALANCE AS ( -- Calculates balance snapshots per pay period, per accrual code
  -- include personfullname to mitigate issues with JOINs after the fact
  SELECT T.PERSONNUM, T.PERSONFULLNAME, T.ACCRUALCODENAME, (T.CARRYFORWARDAMOUNT/3600) AS 'BALANCE', T.START_DATE, T.END_DATE
  FROM TRANSACTIONS_PER_PAYPERIOD T
  WHERE T.ACCRUALTRANTYPE = 22
  --AND ACCRUALTRANTYPENAME = 'VESTED LIMIT ADJUSTED CARRYFORWARD'
  GROUP BY T.PERSONNUM, T.PERSONFULLNAME, T.ACCRUALCODENAME, T.START_DATE, T.END_DATE, T.CARRYFORWARDAMOUNT
)


-------------------------------------------
--      AGGREGATE SELECT STATEMENT       --
-------------------------------------------
SELECT
  B.PERSONNUM
  ,B.PERSONFULLNAME
  ,B.ACCRUALCODENAME
  ,G.GRANTED
  ,T.TAKEN
  ,B.BALANCE
  ,B.START_DATE
  ,B.END_DATE

INTO [KronosData].dbo.EMPACCRUALS
--INTO #EMPACCRUALS
FROM TPP_BALANCE B
  FULL OUTER JOIN TPP_TAKEN T ON
    B.PERSONNUM = T.PERSONNUM AND
    B.ACCRUALCODENAME = T.ACCRUALCODENAME AND
    B.START_DATE = T.START_DATE AND
    B.END_DATE = T.END_DATE
  FULL OUTER JOIN TPP_GRANTS G ON
    B.PERSONNUM = G.PERSONNUM AND
    B.ACCRUALCODENAME = G.ACCRUALCODENAME AND
    B.START_DATE = G.START_DATE AND
    B.END_DATE = G.END_DATE;