Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Mar 25, 1997

Finding the Most Recent and Consecutive Data

[Joe Celko's Pension Plan Puzzle]

Luke Tymowski, a Canadian programmer, posted an interesting problem on the MSAccess forum on CompuServe in 1994 November. He was working on a pension fund problem. In SQL-92, the table involved would look like this:

    CREATE TABLE Pensions
        (sin CHAR(10) NOT NULL,
        pyear INTEGER NOT NULL,
        service INTEGER NOT NULL DEFAULT 0 
            CHECK (year BETWEEN 0 AND 12),
        earnings DECIMAL (8,2) NOT NULL DEFAULT 0.00);
The SIN column is the Social Insurance Number, something like the SSN Social Security Number used in the United States to identify taxpayers. The pyear column is the calendar year, the service column is the number of months in that year the person worked, and earnings is their total earnings for year.

The problem is to find the total earnings of each employee for the most recent consecutive 60 months of service. This number is used to compute their pension. The shortest period going back could be five years with 12 months of service each. The longest period could be 60 years with 1 month of service each. Some people might work four years and not the fifth, and thus not qualify for a pension at all.

The reason this is a beast to solve is that "most recent" and "consecutive" are hard to write in SQL. HINT: For each employee in each year, insert a row even in the years they did not work. It not only makes the query easier, but you also have a record to update when you get in new information.

This query will get me the starting and ending years of consecutive periods where (1) the employee worked (i.e. service greater than 0 months) and (2) the service totaled 60 or more consecutive months:

        PenPeriods (sin, startyear, endyear, totearnings)
        AS SELECT P0.sin, P0.pyear, P1.pyear,
            (SELECT SUM (earnings) -- total earnings for period
                FROM Pensions AS P2
                WHERE P2.sin = P0.sin
                    AND P2.pyear BETWEEN P0.pyear AND P1.pyear)
            FROM Pensions AS P0, Pensions AS P1
            WHERE P1.sin = P0.sin        -- self join to make intervals
                AND P1.pyear >= (P0.pyear - 4)  -- why look sooner?
                AND 0 < ALL (SELECT service     -- consecutive service
                    FROM Pensions AS P3
                    WHERE P3.sin = P0.sin
                        AND P3.pyear BETWEEN P0.pyear AND P1.pyear)
                AND 60 <= (SELECT SUM (service) -- more than 60 months
                    FROM Pensions AS P4
                    WHERE P4.sin = P0.sin
                        AND P4.pyear BETWEEN P0.pyear AND P1.pyear);
The subquery expression in the SELECT list is a SQL-92 trick, but a number of products already have it.

The gimmick is that this will give you ALL the periods of 60 months or more. What we really want is the most recent endyear. I would handle this with the Pension Period VIEW I just defined and a MAX(endyear) predicate:

    SELECT * 
        FROM PenPeriod AS P0
        WHERE endyear = (SELECT MAX(endyear)
            FROM PenPeriod AS P1
            WHERE P1.sin = P0.sin);
I could handle that with some ugly HAVING clauses in SQL-92, I could combine both those subquery predicates with an EXISTS clause, etc. The trouble is that these features would not be as portable right now.

As an exercise, you can try to add another predicate to the final subquery which says there does not exists a year between P0.pyear and P1.pyear which is greater than the P4.pyear and still gives a total of 60 or more consecutive months.

Puzzle provided courtesy of:
Joe Celko

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date