CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user2363207
user2363207

Reputation:

Sum Amounts That Fall Within Variable Date Ranges

I have two tables similar to the example tables below. The PAY table shows how much an employee was paid and when. The ENROLL table shows when an employee was enrolled 'E' and when they later terminated 'T'. So, in this case, the employee was enrolled from 3/1/14 to 5/31/14 and then enrolled again from 10/01/14 to 11/30/14.

PAY table

EMPLID      PAY_END_DT    PAY_AMT
00100001    31-JAN-14     110
00100001    28-FEB-14     120
00100001    31-MAR-14     130     <-- should be included in SUM
00100001    30-APR-14     140     <-- should be included in SUM
00100001    31-MAY-14     150     <-- should be included in SUM
00100001    30-JUN-14     160
00100001    31-JUL-14     170
00100001    31-AUG-14     180
00100001    30-SEP-14     190
00100001    31-OCT-14     200     <-- should be included in SUM
00100001    30-NOV-14     210     <-- should be included in SUM
00100001    31-DEC-14     220

ENROLL table

EMPLID      EFFDT       STATUS
00100001    01-MAR-14   E
00100001    31-MAY-14   T
00100001    01-OCT-14   E
00100001    30-NOV-14   T

What I want is a SQL that will sum the PAY_AMT column but only for the PAY_AMTs that have PAY_END_DTs that occur while the employee was enrolled. For instance, in this case, I only want to sum the PAY_AMTs that have PAY_END_DTs that fall either within 3/1/14 to 5/31/14 or 10/01/14 to 11/30/14. So, the correct result would be:

EMPLID    SUM
00100001  830

I would need to run this SQL for a lot of different EMPLIDs where some may have two enrollment periods like this example and others may have zero, one, three or more enrollment periods.

I had thought about using lead() but can't quite figure out how to use it in this case. I had also thought about using between...and... but since the number of enrollment periods can vary, I don't know how to handle that.

I would prefer not to use PL/SQL.

To make things a little easier, a template for these two tables is below:

WITH 
PAY AS(
SELECT '00100001' AS EMPLID, TO_DATE('2014-01-31', 'YYYY-MM-DD') AS PAY_END_DT, 110.00 AS PAY_AMT FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-02-28', 'YYYY-MM-DD'), 120.00 AS PAY_AMT FROM DUAL
UNION ALL
--SUM column would include this PAY_AMT
SELECT '00100001', TO_DATE('2014-03-31', 'YYYY-MM-DD'), 130.00 AS PAY_AMT FROM DUAL
UNION ALL
--SUM column would include this PAY_AMT
SELECT '00100001', TO_DATE('2014-04-30', 'YYYY-MM-DD'), 140.00 AS PAY_AMT FROM DUAL
UNION ALL
--SUM column would include this PAY_AMT
SELECT '00100001', TO_DATE('2014-05-31', 'YYYY-MM-DD'), 150.00 AS PAY_AMT FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-06-30', 'YYYY-MM-DD'), 160.00 AS PAY_AMT FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-07-31', 'YYYY-MM-DD'), 170.00 AS PAY_AMT FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-08-31', 'YYYY-MM-DD'), 180.00 AS PAY_AMT FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-09-30', 'YYYY-MM-DD'), 190.00 AS PAY_AMT FROM DUAL
UNION ALL
--SUM column would include this PAY_AMT
SELECT '00100001', TO_DATE('2014-10-31', 'YYYY-MM-DD'), 200.00 AS PAY_AMT FROM DUAL
UNION ALL
--SUM column would include this PAY_AMT
SELECT '00100001', TO_DATE('2014-11-30', 'YYYY-MM-DD'), 210.00 AS PAY_AMT FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-12-31', 'YYYY-MM-DD'), 220.00 AS PAY_AMT FROM DUAL
),
ENROLL AS (
SELECT '00100001' AS EMPLID, TO_DATE('2014-03-01', 'YYYY-MM-DD') AS EFFDT, 'E' AS STATUS FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-05-31', 'YYYY-MM-DD'), 'T' FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-10-01', 'YYYY-MM-DD'), 'E' FROM DUAL
UNION ALL
SELECT '00100001', TO_DATE('2014-11-30', 'YYYY-MM-DD'), 'T' FROM DUAL
)

Upvotes: 1

Views: 82

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

Your guess about lead() is correct, and solution is quite simple:

with pay as (...),
     enroll as (...)
select pay.emplid, sum(pay_amt) 
    from pay, 
         (select emplid, effdt effdt_start, status, 
                 lead(effdt, 1) over (partition by emplid order by effdt) effdt_end
            from enroll) enr
   where enr.emplid = pay.emplid
     and enr.status = 'E'
     and pay.pay_end_dt between enr.effdt_start and enr.effdt_end
   group by pay.emplid;

Upvotes: 2

Related Questions