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: 2 days ago @ 9:40 AM
Points: 6,778, Visits: 13,971
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 Saturday, August 18, 2012 3:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:21 PM
Points: 1,945, Visits: 3,059
>> I got this table <<

What is its name? Where is the DDL? There is no such thing as a “classification_code”; your can have a <something>_classification_” (Dewey Decimal Classification) or <something>_code” (postal code), but never a vague generic hybrid. The word “has_multiple” is a verbal phrase, not a noun. It is a computed column and not an attribute of a single row; even worse, it seems to be an assembly language BIT flag! . Here is my guess at your DDL; see the key, the constraints?

CREATE TABLE Licenses
(license_nbr CHAR(5) NOT NULL
CHECK (license_nbr LIKE '[0-9][0-9][0-9][0-9][0-9]'),
license_class CHAR(4) NOT NULL,
PRIMARY KEY (license_nbr, license_class),
license_grp_code CHAR(2) NOT NULL);

INSERT INTO Licenses
VALUES
('10537', 'GA02', 'GA'),
('10537', 'GA03', 'GA'),
('10537', 'GB98', 'GB'),
('10537', 'GF08', 'GF'),
('10537', 'GF09', 'GF);

CREATE VIEW Multiple_Class_License (license_nbr, license_class_cnt)
AS
SELECT license_nbr, COUNT(license_class)
FROM Licenses
GROUP BY license_nbr, license_grp_code;

>> Note : ignore GB98 since its got some business logic "98" <<

No, let's put that business rule into the VIEW. This is one of ways that set-oriented programming is not like punch cards and sequestrate files. The set is declared with predicates as a whole, not in partial processing steps. We do not destroy information (like the count) with flags. Right now, the VIEw will show that GB98 has one license.




Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1346888
Posted Tuesday, August 21, 2012 1:38 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:30 PM
Points: 1,512, Visits: 482
Use ChrisM@Work solution as it is clean and trim...
Post #1348064
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse