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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">








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

SQLHelp


Welcome , today is Saturday, December 20, 2014