Sql-server – Table Joined To Itself Appears Inefficient

join;sql serversql-server-2012

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 IsMainWaveGreater and 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.

Best Answer

With two rows per table any method will be good.

Since you are OK with hard-coding the value 1 for the main RouteID I'd do it like this:

SELECT
    CASE WHEN Main.WaveValue > Other.WaveValue THEN 1 ELSE 0 END AS IsMainWaveGreater
    ,CASE WHEN Main.RollValue > Other.RollValue THEN 1 ELSE 0 END AS IsMainRollGreater
FROM
    Shipping AS Main
    CROSS APPLY
    (
        SELECT
            Other.WaveValue
            ,Other.RollValue
        FROM Shipping AS Other
        WHERE Other.RouteID <> 1
    ) AS Other
WHERE Main.RouteID = 1
;

It assumes that there are only two rows in the table, if there are more "other" routes, then specify their specific RouteID in the CROSS APPLY instead of <> 1 to get only one row that's needed.

Here is SQL Fiddle.

Related Solutions

Sql-server – Select rows with same id but null and some other value in another column for that id

You should be able to use conditional aggregation to get the username with both a value in col2 as well as null.

I'd suggest using a HAVING clause with the conditions. The query would be similar to:

select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
  and sum(case when col2 is null then 1 else 0 end) = 1

See SQL Fiddle with Demo. This query groups your data by each username and then uses conditional logic to check if col2 meets both conditions you want - where col2 is not null and col2 is null.

You can then use this in a subquery, etc to get the username and col2 values:

select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) = 1
    and sum(case when col2 is null then 1 else 0 end) = 1
) d
  on t.username = d.username

See SQL Fiddle with Demo.

If you have more than one col2 row with both null and another value, then you just need to alter the HAVING clause slightly:

select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) >= 1
    and sum(case when col2 is null then 1 else 0 end) >= 1
) d
  on t.username = d.username;

See SQL Fiddle with Demo

Sql-server – Query to convert the count to million or thousands

This type of formatting is generally best done in your application if possible.

The problem is that the case expression returns a result based on the highest datatype precedence of any branch.

So you would need to cast the final COUNT branch of your CASE to VARCHAR too as int has higher precedence than varchar.

Also you should probably add year into your order by except if you actually want to order Jan 2014 and Jan 2015 together followed by Feb and so on.

Though I might well be minded to take that concatenated string out of the GROUP BY too and rewrite it (Along the way fixing a couple of other issues with integer division and inconsistent boundary conditions) as.

WITH T(mmmyyyy, FORMATTED_COUNT, Yr, Mnth)
     AS (SELECT DATENAME(MONTH, MIN(CREATEDTS)) + ', ' + DATENAME(YEAR, MIN(CREATEDTS)) AS Month,
                CASE
                  WHEN COUNT(*) BETWEEN 1000 AND 999999
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000.0, 1) AS NUMERIC(4, 1)) )) ) + 'k'
                  WHEN COUNT(*) >= 1000000
                    THEN ( CONVERT (VARCHAR(10), ( CAST(ROUND(COUNT(*) / 1000000.0, 2) AS NUMERIC(6, 2)) )) ) + 'm'
                  ELSE CONVERT (VARCHAR(10), COUNT(*))
                END,
                YEAR(CREATEDTS),
                MONTH(CREATEDTS)
         FROM   USAGEDATA
         GROUP  BY YEAR(CREATEDTS),
                   MONTH(CREATEDTS))
SELECT mmmyyyy,
       FORMATTED_COUNT
FROM   T
ORDER  BY Yr,
          Mnth;

Best Answer

Related Solutions

Sql-server – Select rows with same id but null and some other value in another column for that id

Sql-server – Query to convert the count to million or thousands

Related Question