Using Count Distinct in a CASE statement to choose more than one unique record

  • 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

  • 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

  • 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'?

  • 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

  • written by someone else - I'm having to learn the ropes (the developer is no longer here)

  • 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

  • Contract_IDProductordercodeStartDateEndDateOfferIDRevision

    111327140101/01/201331/07/2014101010102

    215415200701/01/201306/12/2014202020205

    215415400001/01/201306/12/2014202020205

    215415410101/01/201306/12/2014202020205

    215755990601/01/201306/12/2014202020205

    21247353791001/01/201306/12/2014202020205

    21247695220801/01/201306/12/2014202020205

    21247792800301/01/201306/12/2014202020205

    21247971760601/01/201306/12/2014202020205

    21247972790101/01/201306/12/2014202020205

    311826930501/01/201331/12/2014303030307

    312183000301/01/201331/12/2014303030307

    314574440201/01/201331/12/2014303030307

    315680740001/01/201331/12/2014303030307

    315704700301/01/201331/12/2014303030307

  • 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

  • pretend smaple data from the TMS table (sorry for the garbledness of it) - thank you very much for the answer - now it makes sense!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply