Ignoring rows where some data is different

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)

Ignoring rows where some data is different

Author

Topic

rgjb
Starting Member

19 Posts

Posted - 2004-08-19 : 02:13:02

Hi, I would like to be able to select the rows in a table when the data in proceeding column is the same, then to select the minimum row of that value based on the date and ignore the rest untilt the next change in the amount column.

For example:

The data in the table is:

row1 847 1000.0000 1905-06-03 00:00:00.000
row2 847 2000.0000 1905-06-11 00:00:00.000
row3 847 2500.0000 1905-06-17 00:00:00.000
row4 847 2000.0000 1905-06-14 00:00:00.000
row5 847 2000.0000 1905-06-21 00:00:00.000
row6 847 3000.0000 1905-06-19 00:00:00.000

Is there an easyway to get all the data returned except for row5 because the amount is the same as in row 4. But still get row2 and row4 returned.

The table create follows:

Create table empinf
(empno char(10),
amount money,
eDate datetime)
go

insert into empinf values ('847',1000,1998-03-16)
insert into empinf values ('847',2000,1998-10-01)
insert into empinf values ('847',2500,1999-05-01)
insert into empinf values ('847',2000,2000-09-01)
insert into empinf values ('847',2000,2001-03-01)
insert into empinf values ('847',3000,2002-06-01)
go

So far by using this query:

select e1.empno,
e1.amount,
e1.eDate,
(select min(e2.amount)
from empinf e2
where e1.amount = e2.amount and
e2.empno = e1.empno and
e1.eDate < e2.eDate) as ff
from empinf e1
where e1.empno = 847

I get:

847 1000.0000 1905-06-03 00:00:00.000 NULL
847 2000.0000 1905-06-11 00:00:00.000 2000.0000
847 2500.0000 1905-06-17 00:00:00.000 NULL
847 2000.0000 1905-06-14 00:00:00.000 2000.0000
847 2000.0000 1905-06-21 00:00:00.000 NULL
847 3000.0000 1905-06-19 00:00:00.000 NULL

The closest I've been able to come to is getting rows 2&4 returned (which is what I want) but the rest of the unique amounts are missing.

(Whew, hope this makes sense!)

Many thanks
Gregg

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 03:30:36

This may do it:


Create table empinf
(empno char(10),
amount money,
eDate datetime)

insert into empinf values ('847',1000,'1998-03-16')
insert into empinf values ('847',2000,'1998-10-01')
insert into empinf values ('847',2500,'1999-05-01')
insert into empinf values ('847',2000,'2000-09-01')
insert into empinf values ('847',2000,'2001-03-01') -- Omit this
insert into empinf values ('847',3000,'2002-06-01')

Select empno, max(amount) amount, min(eDate) eDate from 
(
select *, (select count(1) from empinf where amount <> o.amount and eDate > o.edate) rungroup
from empinf o
) x
group by empno, rungroup
order by eDate

empno      amount                eDate                  
---------- --------------------- ------------------------
847        1000.0000             1998-03-16 00:00:00.000
847        2000.0000             1998-10-01 00:00:00.000
847        2500.0000             1999-05-01 00:00:00.000
847        2000.0000             2000-09-01 00:00:00.000
847        3000.0000             2002-06-01 00:00:00.000

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

rgjb
Starting Member

19 Posts

Posted - 2004-08-19 : 20:53:36

Ken,

Thanks so much for this. And sorry for the delay getting back. And indeed it works brilliantly when there’s only one empno in the table. The fun begins when another empno is added.

For example, If the following values are added

insert into empinf values ('848',1000,'1998-03-01')
insert into empinf values ('848',1000,'1998-11-01')
insert into empinf values ('848',2000,'1999-08-01')
insert into empinf values ('848',1000,'2000-08-01')
insert into empinf values ('848',2000,'2001-08-01')
insert into empinf values ('848',1000,'2002-08-01')

If I add the following text then to the view:

where empno='848'
group by empno,rungroup
order by eDate

I get this as the result set.

848 2000.0000 1998-03-01 00:00:00.000
848 2000.0000 1999-08-01 00:00:00.000
848 1000.0000 2000-08-01 00:00:00.000
848 1000.0000 2002-08-01 00:00:00.000

which as you can see is all over the place.
I also tried modifying the line to read

where amount <> o.amount and eDate > o.edate and empno = o.empno

which then gives me

848 2000.0000 1998-03-01 00:00:00.000
848 2000.0000 2000-08-01 00:00:00.000
848 1000.0000 2002-08-01 00:00:00.000

Any ideas? Appreciate your help with this.

Thanks
Gregg

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 22:11:19

This should do it

Select empno, max(amount) amount, min(eDate) eDate from
(
select *, (select count(1) from empinf where empno = o.empno and amount <> o.amount and eDate > o.edate) rungroup
from empinf o
) x
group by empno, rungroup
order by empno, eDate

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

rgjb
Starting Member

19 Posts

Posted - 2004-08-19 : 22:31:33

Hi Ken ,

Thank you for your response. But I start getting invalid responses when I do what you have suggested. I would expect then to see the following details for empno 848:

row1a 848 1000 1998-03-01
row2a 848 2000 1999-08-01
row3a 848 1000 2000-08-01
row4a 848 2000 2001-08-01
row5a 848 1000 2002-08-01

But instead what I’m getting is:

Row1b 848 2000 1998-03-01
Row2b 848 2000 2000-08-01
Row 3b 848 1000 2002-08-01

As you can see the results are quite different. Apart from the dates matching with the wrong amounts (apart from row3b), I’m missing a couple of values.

Thanks
Gregg

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-19 : 23:29:36

I see. How about this

Select empno, amount, eDate From
(
select top 100 percent * ,
Case When (select top 1 amount from empinf where empno = o.empno and eDate < o.eDate order by eDate desc) = amount Then 1 Else Null End ommit
from empinf o
order by empno, eDate
) x
where ommit is null

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

rgjb
Starting Member

19 Posts

Posted - 2004-08-19 : 23:58:05

Ken ,
Thanks a million. It works perfectly with every situation I've been able to throw at it.
Gregg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources