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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy