Another Color Wheel

Categories: News, Professional, SSC
Tags: ,
Comments: No Comments
Published on: November 3, 2011

Some time ago, I had the opportunity to work on a problem involving the color wheel.  The requirements for that problem lent themselves to a solution involving bitwise operations (which you can read here).

I recently came across a new problem involving the color wheel.  This time the requirements and solution are different than the last.  So what better opportunity than the present to write about it.

This time we are presented with a variation where we have been introduced to a group.  The group has various colors.  Given a group, I need to find the other groups that contain a color that the given group does not contain.

Frankly, the solution to this problem is far easier than I initially set out to make it.  Memory being as it is, I did the same thing with the Color Wheel the last time.  I had to revisit that one and update that solution.

This time around, I have simplified the solution prior to writing about it.  That said, somebody will probably show me an easier way to solve it. 😉

Setup

Here is the table with the data that we will be using.

Groups Colors Numbers
A RED 1
A GREEN 2
A BLUE 3
B RED 1
B YELLOW 4
C BLUE 3

Solution

I elected to utilize a CTE rather than formalize a table for the extraction of this sample.  The following solution will thus be using that CTE rather than a table.

[codesyntax lang=”tsql”]

[/codesyntax]

Notice the use of a subquery.  This subquery represents a derived table of Color IDs (since the Numbers column is representative of IDs of the colors).  This is the set of data we want to compare against.  If the color is in this derived table, then we do not want that data in our final result set.

With this setup, I can change the group via the @startgroup variable.  I can now meet the criteria thanks to the “NOT IN” and only produce those groups and color combinations that do not exist in the provided group/starting group.

See, it really is not too difficult.  Breaking down what the requirements are, we can simplify the query and produce the desired results.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
November 2011
M T W T F S S
« Oct   Dec »
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp

  • @Ko_Ver: @NickyvV no, no special tricks in the package :) #sqlhelp
  • @NickyvV: @Ko_Ver I've seen that last week that the Job (step) succeeded, but it actually failed with an error #sqlhelp
  • @NickyvV: @Ko_Ver Is FailParentOnFailure set to false on a task/container maybe? #sqlhelp
  • @Ko_Ver: When I start an Agent job, the "job running" pop-up says there's an error. But SSIS catalog and Agent history says jobs succeed. #sqlhelp
  • @DbaMayor: #sqlhelp I'm seeing LCK_M_U and LCK_M_RS_U waits on sec readonly replica with only reporting queries...anyone faced similar issue?..thnks
  • @TrustMeImADBA: Prob not a good question for #sqlhelp but what is the chances time-service changing the time 23 sec forward made my sql cluster failover?
  • @zippy1981: Seem to be that I need to install SSMS vNext Verifying now #sqlhelp
  • @ChrisAdkin8: @YaHoznaDBA you should also compare avg writelog waits to avg tran log write stall,the two are not necessarily foced to be the same #sqlhelp
  • @toddkleinhans: #sqlhelp Will try using lock priority. Totally forgot about that. Thanks!
  • @DBArgenis: @toddkleinhans Compatibility mode is for T-SQL constructs and Query Optimization, nothing else. #sqlhelp

Welcome , today is Tuesday, February 21, 2017