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

Need help with this query Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 4:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:48 AM
Points: 98, Visits: 142
I got this table

LicenseId ClassificationCode GroupCode HasMultiple
10537 GA02 GA 0
10537 GA03 GA 0
10537 GB98 GB 1
10537 GF08 GF 0
10537 GF09 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

LicenseId ClassificationCode GroupCode HasMultiple
10537 GA02 GA 1
10537 GA03 GA 0
10537 GB98 GB 1
10537 GF08 GF 1
10537 GF09 GF 0

Can someone help me here with this query
Post #1346448
Posted Friday, August 17, 2012 4:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 5:48 AM
Points: 98, Visits: 142
Sorry guys for the trouble ... just figured out some crazy method

UPDATE tvLRF
SET HasMultiple = 1
FROM @tv_LicenseRenewalFee tvLRF
INNER JOIN ( SELECT GroupCode, MIN(SUBSTRING(ClassificationCode,3, LEN(ClassificationCode))) AS Number
FROM @tv_LicenseRenewalFee
WHERE GroupCode <> (
SELECT GroupCode
FROM @tv_LicenseRenewalFee
WHERE HasMultiple = 1
)
GROUP BY GroupCode
HAVING COUNT(GroupCode) > 1 ) A
ON (A.GroupCode+''+CAST(A.Number AS VARCHAR(2))) = tvLRF.ClassificationCode
Post #1346458
Posted Friday, August 17, 2012 5:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:01 AM
Points: 7,151, Visits: 14,998
prads.cs (8/17/2012)
Sorry guys for the trouble ... just figured out some crazy method

UPDATE tvLRF
SET HasMultiple = 1
FROM @tv_LicenseRenewalFee tvLRF
INNER JOIN ( SELECT GroupCode, MIN(SUBSTRING(ClassificationCode,3, LEN(ClassificationCode))) AS Number
FROM @tv_LicenseRenewalFee
WHERE GroupCode <> (
SELECT GroupCode
FROM @tv_LicenseRenewalFee
WHERE HasMultiple = 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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1346480
Posted Tuesday, August 21, 2012 1:38 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 11, 2015 8:28 AM
Points: 1,731, Visits: 499
Use ChrisM@Work solution as it is clean and trim...
Post #1348064
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse