Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using Count Distinct in a CASE statement to choose more than one unique record Expand / Collapse
Author
Message
Posted Tuesday, August 12, 2014 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 4:53 AM
Points: 6, Visits: 9
Hopefully this is a quick question....
I’m scratching my head with a bit of script. Why would the below choose a unique set of records from one table to put into another table? I can’t get over the =’1’ part. In my thinking it would only enter one record (rather than a set). How would it count the next unique record as ‘1’ (adding more than 1 contractid to a contract table)?

SELECT
CASE
WHEN COUNT(DISTINCT(Contract_ID)) = '1' THEN MAX(Contract_ID)
ELSE '-1'
END AS Contract_ID,
Cheers
Chris
Post #1602228
Posted Tuesday, August 12, 2014 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 5,437, Visits: 10,129
Chris

Please will you post the whole query, along with some sample data? What results do you get, and what results are you expecting?

Thanks
John
Post #1602232
Posted Tuesday, August 12, 2014 6:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 322, Visits: 1,010
monkeyflavours (8/12/2014)
Hopefully this is a quick question....
I’m scratching my head with a bit of script. Why would the below choose a unique set of records from one table to put into another table? I can’t get over the =’1’ part. In my thinking it would only enter one record (rather than a set). How would it count the next unique record as ‘1’ (adding more than 1 contractid to a contract table)?

SELECT
CASE
WHEN COUNT(DISTINCT(Contract_ID)) = '1' THEN MAX(Contract_ID)
ELSE '-1'
END AS Contract_ID,
Cheers
Chris

Not the only problem but are you really comparing an integer (Count) to a character '1'?
Post #1602256
Posted Tuesday, August 12, 2014 6:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 4:53 AM
Points: 6, Visits: 9
Hi,
This is just a small excerpt from a much larger Stored Procedure and is linked to temp tables as well as system tables. I'm not sure how I can show this without getting into trouble at work....I'll have a look.

Thanks
chris
Post #1602261
Posted Tuesday, August 12, 2014 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 4:53 AM
Points: 6, Visits: 9
written by someone else - I'm having to learn the ropes (the developer is no longer here)
Post #1602263
Posted Tuesday, August 12, 2014 7:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 4:53 AM
Points: 6, Visits: 9
INSERT INTO DBO.Contract
(
ContractID,
3rdPartyFK,
CustomerFK,
OfferID,
Revision,
SoldDate,
StartDate,
EndDate,
SalesPerson,
SalesManager

)
SELECT
CASE
WHEN COUNT(DISTINCT(tms.Contract_ID)) = '1' THEN MAX(tms.Contract_ID)
ELSE '-1'
END AS Contract_ID,
MIN(pb.ParticipantPK) AS ParticipantPK,
CASE
WHEN COUNT(DISTINCT(tms.CustomerID)) = '1' THEN MAX(pc.ParticipantPK)
ELSE '-1'
END AS CustomerID,
tms.OfferID,
tms.Revision,
MAX(tms.DateSold) AS DateSold,
MAX(tms.StartDate) AS StartDate,
MAX(tms.EndDate) AS EndDate,
MAX(tms.Sold_By) AS Sold_By,
MAX(tms.salesmanager) AS Salesmanager,


FROM #TmsContract TMS
LEFT JOIN dbo.Participant PB
ON tms.3rdPartyID = pb.TmsFK
AND pb.ParticipantType = '3rdParty'
LEFT JOIN dbo.Participant PC
ON tms.PeCustomerID = pc.TmsFK
AND pc.ParticipantType = 'Customer'
LEFT JOIN dbo.Contract C
ON tms.OfferID = C.OfferID
AND tms.Revision = C.Revision
WHERE
c.OfferID IS NULL
Group by

tms.OfferID,
tms.Revision
ORDER BY
tms.OfferID
Post #1602274
Posted Tuesday, August 12, 2014 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 4:53 AM
Points: 6, Visits: 9
Contract_ID Product ordercode StartDate EndDate OfferID Revision
1 1 13271401 01/01/2013 31/07/2014 10101010 2
2 1 54152007 01/01/2013 06/12/2014 20202020 5
2 1 54154000 01/01/2013 06/12/2014 20202020 5
2 1 54154101 01/01/2013 06/12/2014 20202020 5
2 1 57559906 01/01/2013 06/12/2014 20202020 5
2 1 2473537910 01/01/2013 06/12/2014 20202020 5
2 1 2476952208 01/01/2013 06/12/2014 20202020 5
2 1 2477928003 01/01/2013 06/12/2014 20202020 5
2 1 2479717606 01/01/2013 06/12/2014 20202020 5
2 1 2479727901 01/01/2013 06/12/2014 20202020 5
3 1 18269305 01/01/2013 31/12/2014 30303030 7
3 1 21830003 01/01/2013 31/12/2014 30303030 7
3 1 45744402 01/01/2013 31/12/2014 30303030 7
3 1 56807400 01/01/2013 31/12/2014 30303030 7
3 1 57047003 01/01/2013 31/12/2014 30303030 7
Post #1602277
Posted Tuesday, August 12, 2014 7:26 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 5,437, Visits: 10,129
What was that - sample data, expected results, or actual results?

Anway, if I understand correctly, your question is why the SELECT statement returns more than one row? The answer is in the GROUP BY clause. You'll get one row for each combination of tms.OfferID and tms.Revision, and your query will count the distinct values of ContractID in each of those rows.

John
Post #1602281
Posted Tuesday, August 12, 2014 7:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 4:53 AM
Points: 6, Visits: 9
pretend smaple data from the TMS table (sorry for the garbledness of it) - thank you very much for the answer - now it makes sense!
Post #1602283
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse