There has to be a better way to write this query?

This query works, but seems terribly inefficient. There has to be a better way?

What I am trying to do is select 4 different columns from a MarketRates table based on which territory a company is in. There are only 4 Territories, stored as an integer 1-4 in the Company table. So for instance, if the Territory is "1", then I want to select the 4 Southern California columns (column names are SCA*), but if the Territory is "2", then I want to select the 4 Norhtern California columns (column names are NCA*), etc.

I know the tables should be constructed differently, but this is what I have to deal with.

The MarketRates table contains these columns (SCA = Southern California, NCA = Northern California, SNV = Southern Nevada, NAZ = Northern Arizona:

• EndingDate - date
• SCA_MRK - decimal (8,2)
• SCA_RATE - decimal (8,2)
• SCA_COMP - decimal (8,2)
• SCA_NEG - decimal (8,2)
• NCA_MRK - decimal (8,2)
• NCA_RATE - decimal (8,2)
• NCA_COMP - decimal (8,2)
• NCA_NEG - decimal (8,2)
• SNV_MRK - decimal (8,2)
• SNV_RATE - decimal (8,2)
• SNV_COMP - decimal (8,2)
• SNV_NEG - decimal (8,2)
• NAZ_MRK - decimal (8,2)
• NAZ_RATE - decimal (8,2)
• NAZ_COMP - decimal (8,2)
• NAZ_NEG - decimal (8,2)

This is the current query that I am using:

Select CompanyName
  , case TerritoryNumber 
    when 1 then (Select top 1 coalesce(SCA_MRK,0) From MarketRates Order by EndingDate desc) 
    when 2 then (Select top 1 coalesce(NCA_MRK,0) From MarketRates Order by EndingDate desc) 
    when 3 then (Select top 1 coalesce(SNV_MRK,0) From MarketRates Order by EndingDate desc) 
    when 4 then (Select top 1 coalesce(NAZ_MRK,0) From MarketRates Order by EndingDate desc) 
  end AS MRK
  , case TerritoryNumber 
    when 1 then (Select top 1 coalesce(SCA_RATE,0) From MarketRates Order by EndingDate desc) 
    when 2 then (Select top 1 coalesce(NCA_RATE,0) From MarketRates Order by EndingDate desc) 
    when 3 then (Select top 1 coalesce(SNV_RATE,0) From MarketRates Order by EndingDate desc) 
    when 4 then (Select top 1 coalesce(NAZ_RATE,0) From MarketRates Order by EndingDate desc) 
  end AS RATE
  , case TerritoryNumber 
    when 1 then (Select top 1 coalesce(SCA_COMP,0) From MarketRates Order by EndingDate desc) 
    when 2 then (Select top 1 coalesce(NCA_COMP,0) From MarketRates Order by EndingDate desc) 
    when 3 then (Select top 1 coalesce(SNV_COMP,0) From MarketRates Order by EndingDate desc) 
    when 4 then (Select top 1 coalesce(NAZ_COMP,0) From MarketRates Order by EndingDate desc) 
  end AS COMP
  , case TerritoryNumber 
    wh开发者_JS百科en 1 then (Select top 1 coalesce(SCA_NEG,0) From MarketRates Order by EndingDate desc) 
    when 2 then (Select top 1 coalesce(NCA_NEG,0) From MarketRates Order by EndingDate desc) 
    when 3 then (Select top 1 coalesce(SNV_NEG,0) From MarketRates Order by EndingDate desc) 
    when 4 then (Select top 1 coalesce(NAZ_NEG,0) From MarketRates Order by EndingDate desc) 
  end AS NEG
from Company 
where CompanyID = 'THISID'

You should only have to select your 1 row from MarketRates once since you're referencing the same row each time. Select it in a sub-query and join to it and you can reference that throughout the query. I re-wrote what MRK would look like, similar syntax/logic for the other columns as well.

Select CompanyName
  , case TerritoryNumber 
    when 1 then coalesce(SCA_MRK,0)
    when 2 then coalesce(NCA_MRK,0)
    when 3 then coalesce(SNV_MRK,0)
    when 4 then coalesce(NAZ_MRK,0)
  end AS MRK
  , ...etc
from Company 
cross join (select top 1 * from MarketRates order by EndingDate desc) MarketRates
where CompanyID = 'THISID'

DECLARE 
    @TerritoryID INT,
    @CompanyName VARCHAR(32);

SELECT 
    @CompanyName = CompanyName,
    @TerritoryID = TerritoryNumber 
FROM Company 
WHERE CompanyID = 'THISID';

SELECT TOP 1
    CompanyName = @CompanyName,
    MRK = CASE @TerritoryID
        WHEN 1 THEN SCA_MRK
        WHEN 2 THEN NCA_MRK
        WHEN 3 THEN SNV_MRK
        WHEN 4 THEN NAZ_MRK END,
    RATE = CASE @TerritoryID
        WHEN 1 THEN SCA_RATE
        WHEN 2 THEN NCA_RATE
        WHEN 3 THEN SNV_RATE
        WHEN 4 THEN NAZ_RATE END,
    COMP = CASE @TerritoryID
        WHEN 1 THEN SCA_COMP
        WHEN 2 THEN NCA_COMP
        WHEN 3 THEN SNV_COMP
        WHEN 4 THEN NAZ_COMP END,
    NEG = CASE @TerritoryID
        WHEN 1 THEN SCA_NEG
        WHEN 2 THEN NCA_NEG
        WHEN 3 THEN SNV_NEG
        WHEN 4 THEN NAZ_NEG END
FROM MarketRates
ORDER BY EndingDate DESC;

You could keep it clean and faster with one case block. (You can replace the variable types to match your column datatype.) Check the dynamic SQL created below.

DECLARE @CompanyName varchar(50), @TerritoryNum int, @ColumnType varchar(10), @SQL VARCHAR(1000);
SELECT @CompanyName = CompanyName ,@TerritoryNum = TerritoryNumber 
    FROM Company WHERE CompanyID = 'THISID'
SET @ColumnType = CASE @TerritoryNum WHEN 1 THEN 'SCA_'
                                    WHEN 2 THEN 'NCA_'
                                    WHEN 3 THEN 'SNV_'
                                    WHEN 4 THEN 'NAZ_'
                                    END

SET @SQL = '
SELECT 
    ''' + @CompanyName + ''' AS CompanyName,
    COALESCE(' + @ColumnType + 'MRK,0) AS MRK,
    COALESCE(' + @ColumnType + 'RATE,0) AS RATE,
    COALESCE(' + @ColumnType + 'COMP,0) AS COMP,
    COALESCE(' + @ColumnType + 'NEG,0) AS NEG
FROM MarketRates
'
PRINT @SQL
EXEC(@SQL)

The dynamic query (can be seen from the Print) would be as simple as below where the Company table has 'Comp' in CompanyName column and 2 in TerritoryNumber column. Try changing this to other numbers in the Company table and the appropriate MarketRates columns should figure in the dynamic query.

SELECT 
    'Comp' AS CompanyName,
    COALESCE(NCA_MRK,0) AS MRK,
    COALESCE(NCA_RATE,0) AS RATE,
    COALESCE(NCA_COMP,0) AS COMP,
    COALESCE(NCA_NEG,0) AS NEG
FROM MarketRates