August 17, 2012 at 4:10 am
I got this table
LicenseIdClassificationCode GroupCodeHasMultiple
10537GA02 GA 0
10537GA03 GA 0
10537GB98 GB 1
10537GF08 GF 0
10537GF09 GF 0
Result set should return me
either (GA02 or GA03) and (GF08 or GF09) so i can go and update the corresponding "HasMultiple"
Note : ignore GB98 since its got some business logic "98"
my sample table should look something like this after the update
LicenseIdClassificationCode GroupCodeHasMultiple
10537GA02 GA 1
10537GA03 GA 0
10537GB98 GB 1
10537GF08 GF 1
10537GF09 GF 0
Can someone help me here with this query 🙂
August 17, 2012 at 4:26 am
Sorry guys for the trouble ... just figured out some crazy method :hehe:
UPDATEtvLRF
SETHasMultiple = 1
FROM@tv_LicenseRenewalFee tvLRF
INNER JOIN (SELECTGroupCode, MIN(SUBSTRING(ClassificationCode,3, LEN(ClassificationCode))) AS Number
FROM@tv_LicenseRenewalFee
WHEREGroupCode <> (
SELECTGroupCode
FROM@tv_LicenseRenewalFee
WHEREHasMultiple = 1
)
GROUP BY GroupCode
HAVING COUNT(GroupCode) > 1) A
ON(A.GroupCode+''+CAST(A.Number AS VARCHAR(2))) = tvLRF.ClassificationCode
August 17, 2012 at 5:28 am
prads.cs (8/17/2012)
Sorry guys for the trouble ... just figured out some crazy method :hehe:UPDATEtvLRF
SETHasMultiple = 1
FROM@tv_LicenseRenewalFee tvLRF
INNER JOIN (SELECTGroupCode, MIN(SUBSTRING(ClassificationCode,3, LEN(ClassificationCode))) AS Number
FROM@tv_LicenseRenewalFee
WHEREGroupCode <> (
SELECTGroupCode
FROM@tv_LicenseRenewalFee
WHEREHasMultiple = 1
)
GROUP BY GroupCode
HAVING COUNT(GroupCode) > 1) A
ON(A.GroupCode+''+CAST(A.Number AS VARCHAR(2))) = tvLRF.ClassificationCode
Gosh, I'm sure it doesn't have to be quite so complicated. Try this - it generates exactly the same results:
;WITH Updater AS (
SELECT LicenseId, ClassificationCode, GroupCode, HasMultiple,
MaybeMultiple = ROW_NUMBER() OVER(PARTITION BY GroupCode ORDER BY ClassificationCode)
FROM @tv_LicenseRenewalFee
) UPDATE Updater SET HasMultiple = 1 WHERE MaybeMultiple = 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 21, 2012 at 1:38 pm
Use ChrisM@Work solution as it is clean and trim...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply