Top 2 sets of data by date - best way? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Top 2 sets of data by date - best way?

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2009-07-06 : 09:08:47

Hi,

I've got a table that contains a personid, effective date, type and value:

create table #test (PersonID int, effdate datetime, TypeID int, value decimal(10,2))
insert into #test
select 1, '01/01/2001', 1, 100 union
select 1, '01/01/2001', 2, 100 union
select 1, '01/01/2001', 3, 100 union
select 1, '01/01/2002', 1, 200 union
select 1, '01/01/2002', 2, 200 union
select 1, '01/01/2002', 3, 200 union
select 1, '01/01/2003', 1, 300 union
select 1, '01/01/2003', 2, 300 union
select 1, '01/01/2003', 3, 300 union
select 2, '01/01/2001', 1, 100 union
select 2, '01/01/2001', 2, 100 union
select 2, '01/01/2001', 3, 100 union
select 2, '01/01/2002', 1, 200 union
select 2, '01/01/2002', 2, 200 union
select 2, '01/01/2002', 3, 200 union
select 2, '01/01/2003', 1, 300 union
select 2, '01/01/2003', 2, 300 union
select 2, '01/01/2003', 3, 300 union
select 3, '01/01/2001', 1, 100 union
select 3, '01/01/2001', 2, 100 union
select 3, '01/01/2001', 3, 100 union
select 3, '01/01/2002', 1, 200 union
select 3, '01/01/2002', 2, 200 union
select 3, '01/01/2002', 3, 200 union
select 3, '01/01/2003', 1, 300 union
select 3, '01/01/2003', 2, 300 union
select 3, '01/01/2003', 3, 300

I need to get the values for each person at the latest 2 dates. I've provided data at 3 dates for each person, but they could have loads. They could also have more than 3 types at each date.

So for person 1, my expected results are:


Personid    effdate    typeid    value
--------    -------    ------    -----
1           2002-01-01    1      200
1           2002-01-01    2      200
1           2002-01-01    3      200
1           2003-01-01    1      300
1           2003-01-01    2      300
1           2003-01-01    3      300

I've come up with a solution - getting the latest date, then getting the latest date less than the latest date in separate queries then unioning them together (see below), but it feels a bit messy. Is there a better way of doing this?

select * from 
	
	(
	
	--1st query gets the latest date
	select
	t.personid,
	t.effdate,
	t.typeid,
	t.value
	from 
	#test t
	join
		(select 
		personid,
		max(effdate) effdate
		from #test
		group by 
		personid) m
	
	on t.personid = m.personid and t.effdate = m.effdate
	
	union
	
	--2nd qery uses the 1st query as an alias and joins to it
	--where the max effdate < the effdate from query 1
	select
	t.personid,
	t.effdate,
	t.typeid,
	t.value
	from 
	#test t
	join
		(select
		t.personid,
		max(t.effdate) effdate
		from 
		#test t
		join
			(select 
			personid,
			max(effdate) effdate
			from #test
			group by 
			personid) m
			on t.personid = m.personid and t.effdate < m.effdate
		group by t.personid) m2
	
	on t.personid = m2.personid and t.effdate = m2.effdate
	
	) a

order by personid, effdate

Cheers,

Yonabout

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 12:31:55

Isn't your statement and data contradictory?

quote:

I need to get the values for each person at the latest 2 dates. I've provided data at 3 dates for each person, but they could have loads. They could also have more than 3 types at each date.

So for person 1, my expected results are:

Personid effdate typeid value
-------- ------- ------ -----
1 2002-01-01 1 200
1 2002-01-01 2 200
1 2002-01-01 3 200
1 2003-01-01 1 300
1 2003-01-01 2 300
1 2003-01-01 3 300

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-06 : 12:42:25

In any Case


DECLARE @test table  (PersonID int, effdate datetime, TypeID int, [value] decimal(10,2))

INSERT INTO  @test( PersonID, effdate, TypeID, [value])
SELECT 1, '01/01/2001', 1, 100 UNION ALL
SELECT 1, '01/01/2001', 2, 100 UNION ALL
SELECT 1, '01/01/2001', 3, 100 UNION ALL
SELECT 1, '01/01/2002', 1, 200 UNION ALL
SELECT 1, '01/01/2002', 2, 200 UNION ALL
SELECT 1, '01/01/2002', 3, 200 UNION ALL
SELECT 1, '01/01/2003', 1, 300 UNION ALL
SELECT 1, '01/01/2003', 2, 300 UNION ALL
SELECT 1, '01/01/2003', 3, 300 UNION ALL
SELECT 2, '01/01/2001', 1, 100 UNION ALL
SELECT 2, '01/01/2001', 2, 100 UNION ALL
SELECT 2, '01/01/2001', 3, 100 UNION ALL
SELECT 2, '01/01/2002', 1, 200 UNION ALL
SELECT 2, '01/01/2002', 2, 200 UNION ALL
SELECT 2, '01/01/2002', 3, 200 UNION ALL
SELECT 2, '01/01/2003', 1, 300 UNION ALL
SELECT 2, '01/01/2003', 2, 300 UNION ALL
SELECT 2, '01/01/2003', 3, 300 UNION ALL
SELECT 3, '01/01/2001', 1, 100 UNION ALL
SELECT 3, '01/01/2001', 2, 100 UNION ALL
SELECT 3, '01/01/2001', 3, 100 UNION ALL
SELECT 3, '01/01/2002', 1, 200 UNION ALL
SELECT 3, '01/01/2002', 2, 200 UNION ALL
SELECT 3, '01/01/2002', 3, 200 UNION ALL
SELECT 3, '01/01/2003', 1, 300 UNION ALL
SELECT 3, '01/01/2003', 2, 300 UNION ALL
SELECT 3, '01/01/2003', 3, 300

The MAX Dates by Id's



SELECT PersonID, TypeID, MAX(effdate)
FROM @Test
GROUP BY PersonID, TypeID
ORDER BY PersonId, TypeID

The next highest dates



SELECT PersonID, TypeID, MAX(effdate)
FROM @Test o
WHERE effdate < (SELECT MAX(effdate) 
				   FROM @Test i 
				  WHERE i.PersonId = o.PersonId AND i.TypeId = o.TypeId
               GROUP BY i.PersonId, i.TypeId)
GROUP BY PersonID, TypeID
ORDER BY PersonId, TypeID

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-07 : 01:48:05

Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

yonabout
Posting Yak Master

112 Posts

Posted - 2009-07-07 : 10:30:39

Thanks for the replies - they both helped.

Sorry if it looked contradictory - I provided more data than I needed so I could check the query was working by making sure it was excluding the values I didn't want. If that makes sense?

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources