August 12, 2014 at 5:31 am
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
August 12, 2014 at 5:48 am
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
August 12, 2014 at 6:54 am
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'?
August 12, 2014 at 6:56 am
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
August 12, 2014 at 6:58 am
written by someone else - I'm having to learn the ropes (the developer is no longer here)
August 12, 2014 at 7:12 am
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
August 12, 2014 at 7:21 am
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
August 12, 2014 at 7:26 am
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
August 12, 2014 at 7:29 am
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