Home Forums SQL Server 7,2000 T-SQL T-SQL brain teaser, tricky sum for employee data. RE: T-SQL brain teaser, tricky sum for employee data.

  • Here is one solution...

    I do have one extra join to over state my thought process

    --Join pass for attributes and amount

    select sum(CD.amount)as amount from check_data CD,

    --Choose employees and dates for query

    (select SUB1.emplid,SUB1.payend_dt from

    --Temp pay

    (select a.emplid,a.payend_dt from check_data a, ps_job b where a.emplid=b.emplid and b.reg_temp='T' and b.effdt<=a.payend_dt)SUB1

    LEFT OUTER JOIN

    --Regular pay

    (select a.emplid,a.payend_dt from check_data a, ps_job b where a.emplid=b.emplid and b.reg_temp='R' and b.effdt<=a.payend_dt)SUB2

    --Join properties excluding pay dates for full-time

    ON SUB1.emplid=SUB2.emplid and SUB1.payend_dt=SUB2.payend_dt

    WHERE SUB2.emplid is null and SUB2.payend_dt IS null)SUB3

    where SUB3.emplid=CD.emplid and SUB3.payend_dt=CD.payend_dt