CTE Example showing multiple sources, ddv_401k

This blog entry does two things - first, it shows a view that brinks in 401K detail for a company. The view (obviously) could be altered to bring in different pay codes. 

Second, it shows a cool CTE technique that can be modified to work for any view; and it's worth reading the code just to see how it's done. 

We talk multiple data sets in a CTE, all comma separated, and then merge them into the final output. 

Normally I'd use temp tables, but that's not available in the simple view format (but would be in the more complicated view format)

 

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

alter view ddv_401K
--  select * from ddv_401K order by 10 desc
  
AS
  
  
--since we're in a VIEW, we can only have one SQL statement.
--use multiple CTEs to bring in separate table sets to accomplish the goal
  
--get the 401k benefit
With Benefit_CTE (AuditTrail, CheckNumber, CheckDate, EmployeeID, BenCode, BenAmount) as
(Select
        B.AUCTRLCD AuditTrail,
        B.CHEKNMBR CheckNumber,
        B.CHEKDATE CheckDate,
        B.EMPLOYID EmployeeID,
        B.PAYROLCD BenCode,
        B.UPRTRXAM BenAmount
    from
        UPR30300 B
        where     b.PAYROLCD in ('401KER')
        and B.pyrlrtyp = 3
)
,
--2nd DTE for the 401K deduction
Deduction_CTE (AuditTrail, CheckNumber, CheckDate, EmployeeID, DedCode, DedAmount) as
(Select
        A.AUCTRLCD AuditTrail,
        A.CHEKNMBR CheckNumber,
        A.CHEKDATE CheckDate,
        A.EMPLOYID EmployeeID,
        A.PAYROLCD DedCode,
        A.UPRTRXAM DedAmount
        from         UPR30300 A
        where
        A.PYRLRTYP = 2
        and A.PAYROLCD in ('401K')
)
,
--get the 401k loan amount, if any
LoanCTE (CHEKNMBR, EMPLOYID, LoanAmount) as
(Select
        A.CHEKNMBR,
        A.EMPLOYID,
        sum(A.UPRTRXAM) as  LoanAmount
    from UPR30300 A
        where A.PYRLRTYP = 2
        and A.PAYROLCD in ('401KL')
       group by a.CHEKNMBR, a.EMPLOYID
)
,
--get the ROTH amount, if any
RothCTE (CHEKNMBR, employid, RothAmount) as
(Select
              A.CHEKNMBR,
              A.EMPLOYID,
              sum(A.UPRTRXAM) as  RothAmount
    from         UPR30300 A
    where     A.PYRLRTYP = 2
           and A.PAYROLCD in ('ROTH')
       group by a.CHEKNMBR, a.EMPLOYID
)
--link all the CTEs together
    Select
        Deduction_CTE.EmployeeID,
        Deduction_CTE.CheckNumber,
        Deduction_CTE.CheckDate,
        Deduction_CTE.DedCode,
        Deduction_CTE.DedAmount,
        Benefit_CTE.BenCode,
        Benefit_CTE.BenAmount,
              --'' as D401kDeferral,
              --'' as D401kBenefit,
              l.LoanAmount as D401kLoan,
              r.RothAmount as RothDeduction
    from Deduction_CTE
        left join Benefit_CTE         On Deduction_CTE.AuditTrail = Benefit_CTE.AuditTrail         and Deduction_CTE.CheckNumber = Benefit_CTE.CheckNumber         and Deduction_CTE.CheckDate = Benefit_CTE.CheckDate         and Deduction_CTE.EmployeeID = Benefit_CTE.EmployeeID
              LEFT JOIN RothCTE r on r.employid = Deduction_CTE.EmployeeID and r.cheknmbr = Deduction_CTE.CheckNumber
              LEFT JOIN LoanCTE l on l.employid = Deduction_CTE.EmployeeID and l.cheknmbr = Deduction_CTE.CheckNumber
  
        GO
--  select * from ddv_401K order by 1,2

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences