June 9, 2011 at 1:53 am
Hi experts, I have a very unique sql request here. I have 2 tables
create table #Customer
(
CustomerName varchar(100),
ServiceID int,
Rendered bit
)
insert into #Customer
values
('John',1, 1),
('John',2, 1),
('John',3, 1),
('James',1, 1),
('James',2, 1),
('James',3, 0)
create table #Services
(
ServiceID int,
ServiceName varchar(100)
)
insert into #Services
values
(1,'TeethCleaning'),
(2,'EyeCleaning'),
(3,'Massage')
select * from #services
select * from #Customer
select * from #Customer c join #Services s on c.ServiceID = s.ServiceID
Here is the problem,
customer john had all 3 services rendered to him, customer james had 2 of 3 services rendered, what would be a clean optimal
way of writing a sql query to return only customers that had all 3 services rendered only.
So the expected result of the query would be only John and all the services rendered to him
--Expected Values
select * from
(
values
('John',1,1,'TeethCleaning'),
('John',2,1,'EyeCleaning'),
('John',3,1,'Massage')
)as ExpectedResults (CustomerName, ServiceID, Rendered, ServiceName)
June 9, 2011 at 2:23 am
How abt this?
; with allServicesRenderedCustomers As
(
select c.CustomerName
from #Customer c
group by
c.CustomerName
HAVING AVG ( CAST( c.Rendered as decimal(4,2))) = 1.00
)
select *
from #Customer c
join allServicesRenderedCustomers a
on a.CustomerName = c.CustomerName
join #Services s
on c.ServiceID = s.ServiceID
June 9, 2011 at 2:38 am
Or
SELECT C.CustomerName,C.ServiceID,C.Rendered ,S.ServiceName
FROM #Customer C
Inner JOIN #Services S ON S.ServiceID = C.ServiceID
WHERE CustomerName IN (
SELECT CustomerName FROM #Customer
WHERE Rendered = 1
GROUP BY CustomerName
HAVING COUNT (CustomerName) = (SELECT COUNT(*) FROM #Services))
June 9, 2011 at 8:44 am
Both great solutions, I appreciate them. Seems like there is almost no way to get around using the "Having" clause. Interesting.
June 9, 2011 at 8:47 am
Using the HAVING clause is the most efficient way of doing it, but there are other ways.
Are you required to not use the HAVING clause?
June 9, 2011 at 8:50 am
Oh not at all, I can do it any way I like, it was just something I noticed that both solutions had in common. Thanks guys
June 9, 2011 at 8:55 am
Probably not very performant, but you could always use:
;with customer_name as
(
SELECT *,SUM(rendered) OVER(PARTITION BY customername) as totalrendered from #customer
)
SELECT
customer.customername
,customer.serviceid
,serv.servicename
FROM customer_name customer
INNER JOIN #services serv on serv.serviceid = customer.serviceid
WHERE totalrendered = 3
which manages to avoid using the HAVING clause!
June 9, 2011 at 8:59 am
if you need to avoid the having you can do this... not sure if it's the most efficient... (using the limited data it's 2 ms and 6 scans and 6 reads better than the having clause)
select *
from
(
select
customername,
rendered = MIN(cast(rendered as int))
from
#Customer c
group by
customername) as c,
#Services s
where
c.rendered <> 0
June 9, 2011 at 9:01 am
Davin21 (6/9/2011)
Probably not very performant, but you could always use:
;with customer_name as(
SELECT *,SUM(rendered) OVER(PARTITION BY customername) as totalrendered from #customer
)
SELECT
customer.customername
,customer.serviceid
,serv.servicename
FROM customer_name customer
INNER JOIN #services serv on serv.serviceid = customer.serviceid
WHERE totalrendered = 3
which manages to avoid using the HAVING clause!
could be my settings, but I get the error "Operand data type bit is invalid for sum operator."
June 9, 2011 at 9:04 am
SUM(CONVERT(INT, rendered))
June 9, 2011 at 9:51 am
Sorry didn't spot it was a BIT! As Ninja's said, casting / converting to INT should do the trick
June 9, 2011 at 9:59 am
The reason it's slow is because you pretty much always have to scan the whole table to figure out if all the matches are done. Unless and until you find a way around that you're pretty much screwed... having or not having won't play much in there.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply