May 18, 2005 at 8:55 am
The Background:
Let's say you're putting together a database that will keep track of a list of support clients. There's a "Clients" table with and ID and a client name. Then there's a "Services" table with a ServiceID, a description, and a StandardRate field. But what if you want to have a custom rate for a specific client/service combination?
Enter the CustomRates table, which is essentially a many to many linker between Services and Clients that has a CustomRate field in addition to the Client and Service keys. If the client has a custom rate, there will be a record in the CustomRates table that links a client to a service and specifies a rate. If not, a record will not exist in the CustomRates table for that client/service combination.
The Question:
So let's say that I want a basic, non-cursorized query that gets me all of the clients with all of their rates grouped by client (noting that all services are relevant to each client whether they have a custom rate or not). So the output should look something like:
Company ServiceName Rate
Joe Schmoe Standard 90.00
Joe Schmoe Critical 120.00
Joe Schmoe Emergency 150.00
Bill Bob Standard 95.00
Bill Bob Critical 130.00
Bill Bob Emergency 150.00
The Rate column should be populated by the "CustomRate" field in the CustomRates table if a record exists in that table for that client/service. Otherwise it should be populated by the "StdRate" field in the Services table. Make sense? Possible?
May 18, 2005 at 9:13 am
An outer join and the coalesce function on rates column should do the trick.
Basically something along these lines.
SELECT c.company,
s.servicename,
COALESCE(r.customRate, s.standardRate) as 'rate'
FROM clients c
JOIN services s
ON c.serviceid = s.serviceid
LEFT JOIN customRates r
ON c.custid = r.custid
AND s.serviceid = r.serviceid
ORDER BY c.custid, s.servicename
/Kenneth
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply