June 17, 2008 at 10:23 am
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
June 17, 2008 at 11:52 am
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