Combine 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-15 1170
31231 2007-01-01 2006-12-31 1425
31231 2008-01-01 2007-12-31 1453
What I want results to be.
EMPLID P1 P2 JOBCODE
31231 2001-12-31 2005-08-15 1171
31231 2005-08-16 2006-12-31 1170
31231 2007-01-01 2007-12-31 1425
31231 2008-01-01 NULL 1453
-
Can you post the expected output result with some input datas
rajesh
-
If you were to dump the data into a Temp Table with an IDENTITY column, you're life would get a lot easier on this problem.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Here is the input data.
-
Mr. Moden is of course right. Once you have an id (sequential integers), you can left join the table (t1) to itself (t2) on t1.id = t2.id-1. Then you have a result set that makes your output simple.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
How would I create a temp table with identity column and then link them together?
-
I shouldn't write code for you, but I'm still having my morning coffee and this is a good warmup. Here is an example using a table variable. (If you want to create a temp table, just do a create table and start the table name with a pound sign [#]. I trust you can look up the syntax for CREATE TABLE.)
----
declare @temp table (rowID int IDENTITY(1,1) primary key, fruit varchar(20))
insert into @temp (fruit)
select 'Apple' union all
select 'Banana' union all
select 'Tomato'
select t1.rowID,t1.fruit,t2.fruit as previous
from @temp t1
left join @temp t2 on t2.rowid = t1.rowID - 1
-- or
select t1.rowID,t1.fruit,t2.fruit as next
from @temp t1
left join @temp t2 on t2.rowid = t1.rowID + 1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply