a complex query - 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

SQL Server Development (2000)

a complex query

Author

Topic

php95saj
Starting Member

43 Posts

Posted - 2002-05-24 : 10:59:12

OK I have this table:

tbl_MinimumCharges
serviceLevelID tinyint,
currencyID tinyint,
customerID int,
minCharge decimal(9,2)

the values I have are
serviceLevelID currencyID customerID minCharge
3 1 0 49.90
2 1 0 17.95
1 1 0 1.00
1 1 12 0.99
2 1 12 16.50

The values where custmerID is 0 are the default values. In this scenario I want to select charges for three service levels for a given currency. I want to select charges for a customer. If there is no charge for a service level for that customer, I want to use the default value. Is it possible in one query?

what I have done is to create a tempTable and drop values into it and then select values from the tempTable.

php95saj
Starting Member

43 Posts

Posted - 2002-05-24 : 11:20:46

OK 9 people have read this post and no reply. May be it is not clear enough.

Here is my table
serviceLevelID currencyID customerID minCharge
3 1 0 49.90
2 1 0 17.95
1 1 0 1.00
1 1 12 0.99
2 1 12 16.50

Here is my query that works fine. I just want to do it in a slightly neater way:

DECLARE @step int
SET @step = 3
DECLARE @customerID int
SET @customerID = 12
drop table #tempMinCharge
create table #tempMinCharge(serviceLevelID tinyint, currencyID tinyint, customerID int, minCharge decimal(9,2))
WHILE @step >= 1
BEGIN IF EXISTS(SELECT minCharge FROM tbl_Costs_MinimumCharges WHERE customerID = @customerID AND serviceLevelID = @step)
BEGIN
INSERT INTO #tempMinCharge
SELECT serviceLevelID, currencyID, customerID, minCharge
FROM tbl_Costs_MinimumCharges
WHERE customerID = @customerID AND serviceLevelID = @step AND currencyID = 1
ORDER BY customerID
END
ELSE
BEGIN
INSERT INTO #tempMinCharge
SELECT serviceLevelID, currencyID, customerID, minCharge
FROM tbl_Costs_MinimumCharges
WHERE customerID = 0 AND serviceLevelID = @step AND currencyID = 1
ORDER BY customerID
END
SET @step = @step - 1
END
SELECT serviceLevelID, currencyID, customerID, minCharge FROM #tempMinCharge order by serviceLevelID DESC

Please SQL gurus save me on Friday afternoon.

TIA
Sharjeel

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-24 : 11:35:20

saved ...

select *
from #temp t
where exists (
select 1
from #temp
where servicelevelID = t.servicelevelID
having max(customerID) = t.customerID )
order by serviceLevelID desc

where #temp is tbl_costs_minimumcharges

setBasedIsTheTruepath
<O>

php95saj
Starting Member

43 Posts

Posted - 2002-05-24 : 11:43:41

Not quite with you at this point. Can you explain a bit what is happenning?

select *
from #temp t
where exists (
select 1
from #temp
where servicelevelID = t.servicelevelID
having max(customerID) = t.customerID )
order by serviceLevelID desc

where #temp is tbl_costs_minimumcharges

setBasedIsTheTruepath
<O>
[/quote]

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-24 : 11:48:08

well, at least tell me you think it will work for you or not ...

setBasedIsTheTruepath
<O>

php95saj
Starting Member

43 Posts

Posted - 2002-05-24 : 11:51:00

How can I tell you if I cann't figure out what is happennign out there.

well, at least tell me you think it will work for you or not ...

setBasedIsTheTruepath
<O>
[/quote]

php95saj
Starting Member

43 Posts

Posted - 2002-05-24 : 12:04:36

Ok I have figured out what your point was but it is not the one that I want: This is what your query will return
serviceLevelID currencyID customerID minCharge
-------------- ---------- ----------- --------------------
3 1 12 49.90
2 1 12 17.95
1 1 10 5.00

while I want something (for a customerID=12)
serviceLevelID currencyID customerID minCharge
-------------- ---------- ----------- --------------------
3 1 12 49.90
2 1 12 17.95
1 1 0 1.00

while my original table is:

serviceLevelID currencyID customerID minCharge
-------------- ---------- ----------- --------------------
1 1 0 1.00
1 2 0 .69
1 3 0 1.14
2 1 0 19.95
2 2 0 13.80
2 3 0 22.69
3 1 0 49.95
3 2 0 34.55
3 3 0 56.82
3 1 5 49.00
3 2 5 34.00
3 3 5 56.00
1 1 10 5.00
1 2 10 3.00
1 3 10 6.00
2 1 10 15.00
2 2 10 10.00
2 3 10 16.00
3 1 10 49.95
3 2 10 34.56
3 3 10 56.82
2 1 12 17.95
2 2 12 12.80
2 3 12 20.69
3 1 12 49.90
3 2 12 34.65
3 3 12 56.56

quote:

How can I tell you if I cann't figure out what is happennign out there.

well, at least tell me you think it will work for you or not ...

setBasedIsTheTruepath
<O>

[/quote]

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-24 : 13:10:02

In that case you can modify my query slightly to add a "customerID = @customerID" and it should be what you want.

setBasedIsTheTruepath
<O>

drsloat
Starting Member

45 Posts

Posted - 2002-05-24 : 13:39:53

I hard coded in the customerID=12 and currencyID=1 but you can pass
these as parameters

Select t1.ServiceLevelID, t1.currencyID, IsNull(tm.CustomerID,0) as 'customerID',
IsNull(tm.minCharge,t1.defaultMinCharge) as 'minCharge'
From
(
Select ServiceLevelID, 12 as 'CustomerID', minCharge as 'defaultMinCharge', currencyID
From tbl_Costs_MinimumCharges
Where customerID = 0 and currencyID=1
) as t1 Left Outer Join tbl_Costs_MinimumCharges tm
on (t1.ServiceLevelID = tm.ServiceLevelID and t1.CustomerID = tm.CustomerID and t1.currencyID = tm.currencyID)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources