Combine multiple selects to one row.

• Erin.

  SSCertifiable

  Points: 5237

  More actions

  June 17, 2008 at 10:23 am

  #192698

  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

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  June 17, 2008 at 11:52 am

  #829961

  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