How to have totals row to be exlcuded from order by and show on bottom of table ssms18-CodePudding

This is the code I have in place right now. There are 4 rows in the output table and I'm wondering how to have the totals rows show on the bottom of the table. Its currently showing on the top due to the order by DESC.

USE [MyData]

SELECT * INTO tableOne FROM [DataLib].[dbo].[Lease] -- carry over Lease table from Data Library to Reporting Database

--populate new table with the 3 commerical sectors and their uniqu
CREATE TABLE commercial_expirations (
    UniqueID varchar(255),
    PropertyType varchar(50))

INSERT INTO commercial_expirations
VALUES  ('Asset / Most Recent Quarter / All Assets / Sector | Industrial','Industrial'),
        ('Asset / Most Recent Quarter / All Assets / Sector | Office','Office'),
        ('Asset / Most Recent Quarter / All Assets / Sector | Retail','Retail'),
        ('Asset / Most Recent Quarter / All Assets / SectorType | Commercial','Total')

SELECT  c.PropertyType AS 'Property Type',
        e.SpaceArea_End AS 'RentableSF',
        FORMAT(e.LsDuration_WAvg, 'N1') AS 'WA Duration (Yrs)',
        FORMAT(e.[LsArea%_End], 'P1') AS '%Leased',
        FORMAT(e.[Year0ExpireArea%], 'P1') AS '2021',
        FORMAT(e.[Year1ExpireArea%], 'P1') AS '2022',
        FORMAT(e.[Year2ExpireArea%], 'P1') AS '2023',
        FORMAT(e.[Year3ExpireArea%], 'P1') AS '2024',
        FORMAT(e.[Year4ExpireArea%], 'P1') AS '2025',
        FORMAT(iif(e.[Year5ExpireArea%] IS NULL, 0, e.[Year5ExpireArea%])   iif(e.[Year6ExpireArea%] IS NULL, 0, e.[Year6ExpireArea%])   iif(e.[Year7ExpireArea%] IS NULL, 0, e.[Year7ExpireArea%])   iif(e.[Year8ExpireArea%] IS NULL, 0, e.[Year8ExpireArea%])   iif(e.[Year9ExpireArea%] IS NULL, 0, e.[Year9ExpireArea%])    iif(e.[Year10ExpireArea%] IS NULL, 0, e.[Year10ExpireArea%])   iif(e.[Year11PlusExpireArea%] IS NULL, 0, e.[Year11PlusExpireArea%]), 'P1')  AS 'Thereafter'

INTO ##Schedule

FROM commercial_expirations AS c
LEFT JOIN tableOne AS e
ON c.UniqueID = e.RecordID;

SELECT  [Property Type],
        FORMAT(RentableSF, 'N0') AS 'Rentable SF',
        [WA Duration (Yrs)],
        [2021],
        [2022],
        [2023],
        [2024],
        [2025],
        Thereafter

FROM ##Schedule
ORDER BY [RentableSF] DESC;



/*
DROP TABLE commercial_expirations
DROP TABLE expSummary
DROP TABLE ##Schedule
*/

CodePudding user response：

Here is my best attempt at converting all this code into a single query. As I have no database or schema or script to validate the query, i leave debugging to you.

with commercial_expirations as (
   select * from (
   VALUES  ('Asset / Most Recent Quarter / All Assets / Sector | Industrial',    'Industrial'),
           ('Asset / Most Recent Quarter / All Assets / Sector | Office',        'Office'),
           ('Asset / Most Recent Quarter / All Assets / Sector | Retail',        'Retail'),
           ('Asset / Most Recent Quarter / All Assets / SectorType | Commercial','Total')
   ) as x(UniqueID, PropertyType)
)
select ... 
from commercial_expirations as cexp
left join [DataLib].[dbo].[Lease] as lease -- why use a three-part name here? THINK!! 
  ON cexp.UniqueID = lease.RecordID
order by 
    case cexp.PropertyType when 'Total' then 1 else 0 end, 
    lease.SpaceArea_End DESC
;

Be wary of using three-part names. It makes your code fragile since it requires changes when using different environments. Generally speaking, your connection should determine the database to use for your object (table) references. I suggest you really thing about using FORMAT in sql queries as it is an expensive operation. Formatting is function best implemented in the presentation layer.

Last suggestion. Stop using this lazy pattern of <select ... into x, do something with x, select * from x> coding approach. Breaking complex queries into achievable parts is a good idea but that can all be done as a process of starting with basic queries against the tables of interest and then adding joins and functionality needed to achieve the desired result. Sometimes there is a reason to materialize the underlying queries - do that when there is a proven benefit. Otherwise let the database engine optimizer do what it is designed to do.

It is quite common to see people using the pattern to simply push these scripts into production and move on to the next task - never making any attempt to clean it up or optimize it. That just reinforces bad habits. Start learning GOOD habits.

CodePudding user response：

To force the total row to the top, you can either add

  CASE WHEN c.PropertyType = 'Total' THEN 1 ELSE 2 END

to the beginning of the ORDER BY clause.

You can also simplify and improve this query in a number of ways:

Change commercial_expirations to a virtual table in a CTE
Combine the temp tables together into one query
Use ISNULL instead of IIF(... IS NULL
Use [] to quote column names
- Ideally you shouldn't need to quote column names at all, choose your names with care
Order by an actual value, rather than the formatted representation

WITH commercial_expirations AS (
    SELECT *
    FROM (VALUES  
        ('Asset / Most Recent Quarter / All Assets / Sector | Industrial','Industrial'),
        ('Asset / Most Recent Quarter / All Assets / Sector | Office','Office'),
        ('Asset / Most Recent Quarter / All Assets / Sector | Retail','Retail'),
        ('Asset / Most Recent Quarter / All Assets / SectorType | Commercial','Total')
    ) v(UniqueID, PropertyType)
)
SELECT  c.PropertyType AS [Property Type],
        FORMAT(e.SpaceArea_End, 'N0') AS RentableSF,
        FORMAT(e.LsDuration_WAvg, 'N1') AS [WA Duration (Yrs)],
        FORMAT(e.[LsArea%_End], 'P1') AS [%Leased],
        FORMAT(e.[Year0ExpireArea%], 'P1') AS [2021],
        FORMAT(e.[Year1ExpireArea%], 'P1') AS [2022],
        FORMAT(e.[Year2ExpireArea%], 'P1') AS [2023],
        FORMAT(e.[Year3ExpireArea%], 'P1') AS [2024],
        FORMAT(e.[Year4ExpireArea%], 'P1') AS [2025],
        FORMAT(
            ISNULL(e.[Year5ExpireArea%], 0)  
            ISNULL(e.[Year5ExpireArea%], 0)  
            ISNULL(e.[Year6ExpireArea%], 0)  
            ISNULL(e.[Year7ExpireArea%], 0)  
            ISNULL(e.[Year8ExpireArea%], 0)  
            ISNULL(e.[Year9ExpireArea%], 0)   
            ISNULL(e.[Year10ExpireArea%], 0)   
            ISNULL(e.[Year11PlusExpireArea%], 0), 'P1')  AS [Thereafter]
FROM commercial_expirations AS c
LEFT JOIN tableOne AS e
  ON c.UniqueID = e.RecordID;
ORDER BY
  CASE WHEN c.PropertyType = 'Total' THEN 1 ELSE 2 END,
  e.SpaceArea_End DESC;