Comparing rows in the same table

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

General SQL Server Forums

New to SQL Server Programming

Comparing rows in the same table

Author

Topic

maggie1
Starting Member

1 Post

Posted - 2009-12-29 : 14:40:30

I need assistance with comparing old vs. new rates in the same table. The new rates have an effective_date = '16-oct-2009' and a status = 'C'. The old rates are status ='O' and have varying dates and could have multiple rows. I would want the max date for comparasion. Any help would be greatly appreciated.

maggie

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-29 : 15:09:48

If you post table structure and sample date I could help you more, but this should point you in the right direction.

Jim

DECLARE @Table Table (EffectiveDate datetime,Status char(1),rate float)

INSERT INTO @TABLE
SELECT '10/16/2009','C',1.234 UNION ALL
SELECT '09/15/2009','O',1.456 UNION ALL
SELECT '08/14/2009','O',2.345

SELECT [NewRate] = MAX(CASE WHEN EffectiveDate = '10/16/2009' and status = 'C' THEN rate else 0 END)
,[OldRate] = MAX(CASE WHEN status = 'O' THEN rate ELSE 0 END)
FROM
(
select EffectiveDate,status,rate
,[rank] = rank() OVER(Partition by status order by effectivedate)
from @table
) t1

WHERE t1.rank = 1

Everyday I learn something that somebody else already knew

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-29 : 15:19:02

Table structure and Sample data will help...but maybe this?

declare @t table(loggedon datetime, rate int,stat varchar(1))
insert @t
select '2009-12-29 13:53:34.927', 2,'C' union all
select '2009-12-24 13:03:34.927',3,'O' union all
select '2009-12-28 13:53:34.927', 4,'O'


select max(case when stat = 'C' then loggedon end) as new_eff_date
,max(case when stat = 'O' then loggedon end) as old_eff_date
,max(case when stat = 'C' then rate end) as new_rate
,max(case when stat = 'O' then rate end)
from @t

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-29 : 15:19:51

Can't believe I was 10 minutes late..I thought I started replying as soon as I saw the post...

byrdzeye
Starting Member

14 Posts

Posted - 2009-12-29 : 16:59:23

vijay - you still have time. both of the solutions are getting the max rate and not the last rate from the expired values.
swap the 3 and 4 and see.

select '2009-12-29 13:53:34.927', 2,'C' union all
select '2009-12-24 13:03:34.927',4,'O' union all
select '2009-12-28 13:53:34.927', 3,'O'

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-29 : 17:12:06

Yeah...thats coz I was just assuming how your data looks like. If you can actually show us your sample data and table structure..we can provide a better query.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-29 : 17:22:58

Try this

declare @t table(loggedon datetime, rate int,stat varchar(1))
insert @t
select '2009-12-29 13:53:34.927', 2,'C' union all
select '2009-12-24 13:03:34.927',4,'O' union all
select '2009-12-28 13:53:34.927', 3,'O'
--
select max(case when a.stat = 'C' then a.loggedon else null end) as new_eff_date
,max(case when a.stat = 'O' and a.seq = 1 then a.loggedon else null end) as old_eff_date
,max(case when a.stat = 'C' then a.rate else 0 end) as new_rate
,max(case when a.stat = 'O' and a.seq = 1 then a.rate else 0 end) as old_rate
from (
select row_number() over(partition by stat order by loggedon desc) as seq,* from @t
) a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources