November 23, 2011 at 12:07 pm
Hi,
I need your help in creating a query, I am stuck… Attached is a sample table. Each contract may have multiple versions. I need to find out all distinct contracts that don’t have the same client name in all versions associated with that contract. From the sample, the query should return contracts 5427 and 4678. I would appreciate your help!
November 23, 2011 at 12:21 pm
Please post what SQL you have come up with so far, so we can better assist...
Um - This isn't a homework assignment is it?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 23, 2011 at 12:35 pm
This is not a homework assignment :-), I need to do this for my work but I am not very strong with the SQL. I tried group by and having statements but they didn't work for me... Thanks!
November 23, 2011 at 12:37 pm
Glad to hear that 🙂 In either case, any one here is willing to help knowing that you've already gave it your best shot...so if you would please post what you have so far, I'm confident you'll have what you need in no time 😉
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
November 23, 2011 at 12:46 pm
I'm trying to come up with a solution now.
November 23, 2011 at 1:14 pm
More info is needed. Tried to do it but too many holes right.
November 23, 2011 at 2:32 pm
I think I figured out how to do it... using two temp tables. Let me know if there is an easier way please...
CREATE TABLE #Temp(
contract int,
ClientName varchar(100))
CREATE TABLE #Temp1(
contract int)
Insert Into #Temp (contract,ClientName)
select distinct contract, clientname
from table
Insert Into #Temp1 (contract)
select contract
from #Temp
group by contract
having COUNT(contract) > 1
select #Temp.contract, #Temp.ClientName
from #Temp, #Temp1
where #Temp.contract = #Temp1.contract
November 23, 2011 at 2:53 pm
Hi
I am trying to understand the business logic that allows you to have a single contract number with two versions that appear to be assigned to different Clients.....??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 23, 2011 at 3:12 pm
That is exactly why I need to identify these contracts, the business rules were not put in place. This should never have happened and we need to fix these cases.
November 23, 2011 at 3:32 pm
does this help you....
SELECT Contract, Client, COUNT(*) AS ContractCount
FROM tbl_temp
GROUP BY Contract, Client
HAVING (COUNT(*) > 1)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 23, 2011 at 4:04 pm
J Livingston SQL (11/23/2011)
does this help you....
SELECT Contract, Client, COUNT(*) AS ContractCountFROM tbl_temp
GROUP BY Contract, Client
HAVING (COUNT(*) > 1)
Actually, I think this is what is needed:
select Contract, count(distinct Client) as ClientCnt
from dbo.myTable -- what ever your table name goes here)
group by Contract
having count(distinct Client) > 1;
November 23, 2011 at 4:18 pm
Lynn Pettis (11/23/2011)
J Livingston SQL (11/23/2011)
does this help you....
SELECT Contract, Client, COUNT(*) AS ContractCountFROM tbl_temp
GROUP BY Contract, Client
HAVING (COUNT(*) > 1)
Actually, I think this is what is needed:
select Contract, count(distinct Client) as ClientCnt
from dbo.myTable -- what ever your table name goes here)
group by Contract
having count(distinct Client) > 1;
Lynn...you are right...my code was wrong...thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 23, 2011 at 4:30 pm
Thank you so much!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply