Previous date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Previous date

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-03-15 : 13:41:47

Hi,

Here is my table:


declare @a table (id char(2), ct int, dt datetime)
insert @a
select 'A2', 18, '04/04/1980' union all
select 'A3', 18, '01/14/1983' union all
select 'A4', 18, '11/26/1982' union all
select 'A5', 18, '11/03/1989' union all
select 'A6', 18, '02/14/1986' union all
select 'A7', 18, '10/23/1981' union all
select 'A8', 18, '09/15/1983' union all
select 'A9', 20, '06/07/1985' union all
select 'AA', 20, '02/17/1988'

I need to select id, dt, prev_id, prev_dt for each id. prev_dt is the highest (but lower) dt from the same table, where cert is the same as in my record. prev_id is id for that record.

id  dt          prev_id     prev_dt
A2  04/04/1980  NULL        NULL
A3  01/14/1983  11/26/1982  A4
A4  11/26/1982  10/23/1981  A7
A5  11/03/1989  2/14/1986   A6
A6  02/14/1986  9/15/1983   A8
A7  10/23/1981  4/4/1980    A2
A8  09/15/1983  1/14/1983   A3
A9  06/07/1985  NULL        NULL
AA  02/17/1988  6/7/1985    A9

How do I do it?

Any help appreciated...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-15 : 13:58:47

[code]-- prepare sample data
declare @a table (id char(2), ct int, dt datetime)

insert @a
select 'A2', 18, '04/04/1980' union all
select 'A3', 18, '01/14/1983' union all
select 'A4', 18, '11/26/1982' union all
select 'A5', 18, '11/03/1989' union all
select 'A6', 18, '02/14/1986' union all
select 'A7', 18, '10/23/1981' union all
select 'A8', 18, '09/15/1983' union all
select 'A9', 20, '06/07/1985' union all
select 'AA', 20, '02/17/1988'

-- show the expected output
SELECT a.ID,
a.CT,
a.DT,
x.ET AS Prev_DT,
b.ID AS Prev_ID
FROM @a AS a
INNER JOIN (
SELECT t1.DT,
t1.CT,
(SELECT MAX(t2.DT) FROM @a AS t2 WHERE t2.CT = t1.CT AND t2.DT < t1.DT) as ET
FROM @a AS t1
) AS x ON x.DT = a.DT AND x.CT = a.CT
LEFT JOIN @a AS b ON b.DT = x.ET AND b.CT = x.CT
ORDER BY a.ID[/code]

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 14:16:12

you can make it just a little simpler .. basically exactly the same as Peso's, only one less self-join:


select x.*, c.id as prevID
from
(	
	select a.id, a.ct, a.dt, 
		(select max(dt) from @a b where a.ct = b.ct and a.dt > b.dt) as PrevDt
	from @a a
) x
left outer join
	@a c on x.ct = c.ct and x.prevDt = c.dt

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-15 : 14:17:33

by the way -- GREAT job asking the question! Very clear, sample data, expected results, everything! WELL DONE!