SQL Query challenge - 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)

SQL Query challenge

Author

Topic

Christoph
Starting Member

2 Posts

Posted - 2002-09-05 : 09:34:24

Hi,

I have a challenge for all SQL-Query Gurus. At least I think it's not that simple, but I surely need an answer to that question.

I have a recordset like this.

IDService Price IDRatePlan RateCode ParentFrom ParentTo
--------- ---------- ----------- -------- ----------- -----------
1 100.0000 0 RCK NULL NULL
1 90.0000 -1 DAY NULL NULL
2 100.0000 0 RCK NULL NULL
2 100.0000 -1 DAY NULL NULL
3 80.0000 0 RCK NULL NULL
3 70.0000 1 C NULL NULL
4 140.0000 0 RCK NULL NULL
4 130.0000 -4 FIX NULL 3
4 150.0000 -4 FIX 4 NULL

These are different prices for 4 different services. The rateplan defines where the specific price comes from. One of them has different prices, depending on parents ("4, null" means at least 4!). I want the row with the best price per service, DEPENDING on the parents - like this:
(if the prices are the same, take the one with max. IDRatePlan)

IDService Price IDRatePlan RateCode ParentFrom ParentTo
----------- --------------------- ----------- -------- ----------- -----------
1 90.0000 -1 DAY NULL NULL
2 100.0000 0 RCK NULL NULL
3 70.0000 1 C NULL NULL
4 140.0000 0 RCK 4 NULL
4 130.0000 -4 FIX NULL 3

See what happend? There are still two lines for IDService=4, on old one, and a new one (RCK, 4, null!)

How can I do this WITHOUT using a curson (cause everybody says I must not use it!).

Thanx for any solutions or hints.

- kri -

1fred
Posting Yak Master

158 Posts

Posted - 2002-09-05 : 14:17:29

try this select * from table, (select IDService, min(price) from Table group by IDService) a
where a.idservice = table.idservice and
a.price = table.price

Christoph
Starting Member

2 Posts

Posted - 2002-09-09 : 02:51:47

Hi,

thanx fpor your reply - but it does not work. I think I got you idea, but it leaves still two problems: (a) IDService=2 would generate still 2 rows instead of one (but I can solve that) but (b) the problem with different parents is the real trouble.

- kri -

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-09 : 07:26:20

Not sure what you want.
I guess it's just the minimum price per service (easy) apart from this parent thing.
Why do you want the 140 and 130 prices for 4?
Is it because they both have different ParentTo?
in which case something like

select *
from tbl
where not exists
(select * from tbl t2 where t2.IDService = tbl.IDService
and coalesce(t2.ParentTo,-1) = coalesce(tbl.ParentTo,-1)
and t2.Price < tbl.Price)

This will still leave duplicate prices for which you need to get the max rate plan.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources