Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Using Count Distinct in a CASE statement to choose more than one unique record Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, August 12, 2014 5:31 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 30, 2014 3:11 AM Points: 6, Visits: 12
 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,CheersChris
Post #1602228
 Posted Tuesday, August 12, 2014 5:48 AM
 SSCertifiable Group: General Forum Members Last Login: Friday, October 21, 2016 9:57 AM Points: 6,909, Visits: 14,415
 ChrisPlease will you post the whole query, along with some sample data? What results do you get, and what results are you expecting?ThanksJohn
Post #1602232
 Posted Tuesday, August 12, 2014 6:54 AM
 SSCommitted Group: General Forum Members Last Login: Yesterday @ 1:10 PM Points: 1,616, Visits: 3,058
 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,CheersChris 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 Group: General Forum Members Last Login: Tuesday, December 30, 2014 3:11 AM Points: 6, Visits: 12
 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.Thankschris
Post #1602261
 Posted Tuesday, August 12, 2014 6:58 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 30, 2014 3:11 AM Points: 6, Visits: 12
 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 Group: General Forum Members Last Login: Tuesday, December 30, 2014 3:11 AM Points: 6, Visits: 12
 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 TMSLEFT 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.RevisionWHERE c.OfferID IS NULLGroup by tms.OfferID, tms.RevisionORDER BY tms.OfferID
Post #1602274
 Posted Tuesday, August 12, 2014 7:21 AM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, December 30, 2014 3:11 AM Points: 6, Visits: 12
 Contract_ID Product ordercode StartDate EndDate OfferID Revision1 1 13271401 01/01/2013 31/07/2014 10101010 22 1 54152007 01/01/2013 06/12/2014 20202020 52 1 54154000 01/01/2013 06/12/2014 20202020 52 1 54154101 01/01/2013 06/12/2014 20202020 52 1 57559906 01/01/2013 06/12/2014 20202020 52 1 2473537910 01/01/2013 06/12/2014 20202020 52 1 2476952208 01/01/2013 06/12/2014 20202020 52 1 2477928003 01/01/2013 06/12/2014 20202020 52 1 2479717606 01/01/2013 06/12/2014 20202020 52 1 2479727901 01/01/2013 06/12/2014 20202020 53 1 18269305 01/01/2013 31/12/2014 30303030 73 1 21830003 01/01/2013 31/12/2014 30303030 73 1 45744402 01/01/2013 31/12/2014 30303030 73 1 56807400 01/01/2013 31/12/2014 30303030 73 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
 SSCertifiable Group: General Forum Members Last Login: Friday, October 21, 2016 9:57 AM Points: 6,909, Visits: 14,415
 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 Group: General Forum Members Last Login: Tuesday, December 30, 2014 3:11 AM Points: 6, Visits: 12
 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

 Permissions