My ultimate goal is to compare two rows (only two will exist) and for a specific type and determine if its rows values are greater than the other rows type values. The result I came up with seems to be unwieldy which leaves me to wonder if it can be done in a faster or more elegant way.
I have created sql fiddle here which demonstrates the issue.
Say we are shipping cargo and our main route of type 1 needs to be compared to the other route type 2. Each route has a Wave value and a Roll value which need to be compared independently. Note that other route types (except main, it is always uses) can be compared to
Main of type value 1, but only two at a time.
CREATE TABLE Shipping ( [RouteID] [INT] NOT NULL, [WaveValue] [INT] NOT NULL, [RollValue] [INT] NOT NULL ); INSERT Shipping(RouteID, WaveValue, RollValue) VALUES (1, 20, 2), -- This is the main route (2, 10, 30); -- The other secondary route, (only 2 items ever in this table)
With the values currently in the table we expect that the MainsWave will be greater than the other's wave and the reverse for the Roll.
Here is the working sql, note that the true end result is only concerned with
IsMainRollGreater but all columns are shown for debug:
SELECT Max1.RouteID , Max1.WaveValue , Max1.RollValue , MaxOther.RouteID , MaxOther.WaveValue , MaxOther.RollValue , IIF(Max1.RouteID = 1, IIF(Max1.WaveValue > MaxOther.WaveValue, 1, 0), IIF(Max1.WaveValue > MaxOther.WaveValue, 0, 1)) AS IsMainWaveGreater , IIF(Max1.RouteID = 1, IIF(Max1.RollValue > MaxOther.RollValue, 1, 0), IIF(Max1.RollValue > MaxOther.RollValue, 0, 1)) AS IsMainRollGreater FROM Shipping AS Max1 INNER JOIN Shipping AS MaxOther ON Max1.RouteID > MaxOther.RouteID;
Since the row that could be read first and joined to the next may not be the Main route (remember always value 1 in
RouteID for main), the sql has to have an
IIF which checks on what type Max1 is and then swap the check/results accordingly.
Can this be optimized or even rewritten?
This is just one operation in a CTE process so other operations can be entertained.