Combine multiple selects to one row.

  • I have the following script and get the following results.

    I want to show the P2 value as the last date for the previous jobcode. Does anyone have any suggestions? How would I make sure a NULL is shown for the last row.

    SELECT P1.[emplid] AS [EMPLID],

    MAX(P1.[EFFDT]) AS [P1],

    MAX(P2.[EFFDT]) AS [P2],

    P1.[jobcode] AS [JOBCODE]

    FROM dbo.PS_JOB P1

    LEFT JOIN PS_JOB P2 ON

    (P2.EFFDT = DATEADD(DAY, -1, P1.EFFDT))

    where P1.emplid = '31231'

    group by P1.emplid, P1.jobcode

    ORDER BY EMPLID, p1

    Current results.

    EMPLID P1 P2 JOBCODE

    31231 2001-12-31 NULL 1171

    31231 2005-08-16 2005-08-151170

    31231 2007-01-01 2006-12-311425

    31231 2008-01-01 2007-12-311453

    What I want results to be.

    EMPLID P1 P2 JOBCODE

    31231 2001-12-31 2005-08-15 1171

    31231 2005-08-16 2006-12-311170

    31231 2007-01-01 2007-12-311425

    31231 2008-01-01 NULL 1453

  • Please read "Forum Etiquette: How to post data/code on a forum to get the best help"

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    SQL = Scarcely Qualifies as a Language

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply