Transforming temp table that uses counts into query

Question

Today I came by one query that I deem to be 'suboptimal'

it looks following:

CREATE TABLE #temp ( 
                    Sessionid    UNIQUEIDENTIFIER, 
                    PolicyNumber VARCHAR(30), 
                    StartDate    DATETIME, 
                    RequestUrl   VARCHAR(255), 
                    ClientSource VARCHAR(50),
                    Comment VARCHAR(250),
                    ExceptionCount INT 
                ) 

                INSERT INTO #temp 
                    SELECT sessionid, policynumber, startdate  "Timestamp", requesturl "Instance", clientsource , '' Comment 
                    , (SELECT COUNT(*) from Logging.Exceptions where [Key] = sessionId) as exceptioncount 
                    FROM   [Transaction].[sessions] tr WITH  (nolock)
                     WHERE sessionId = '7B851237-3790-4A6D-9688-2E77843E0BB1' 

                SELECT (SELECT COUNT(*) FROM #temp WHERE exceptioncount > -1) as TotalRecords,
                       (SELECT COUNT(*) FROM #temp WHERE exceptioncount > -1) as TotalDisplayRecords,* from
                    (SELECT * FROM
                        (SELECT ROW_NUMBER() OVER (ORDER BY StartDate  desc) AS rownumber,
                            sessionid, policynumber, startdate  "Timestamp", requesturl "Instance", 
                            clientsource, exceptioncount,Comment            
                         FROM #temp
                         WHERE exceptioncount > -1) as cte
                     WHERE cte.rownumber BETWEEN 1 AND 10) as "Results"


                drop table #temp

which I have now rewritten in a following form

SELECT   
--count(*) TotalRecords,
 --   count(*) TotalDisplayRecords,
                       * from (

SELECT ROW_NUMBER() OVER (ORDER BY "Timestamp"  desc) AS rownumber, * from (
                        SELECT sessionid, policynumber, startdate  "Timestamp", requesturl "Instance", clientsource , '' Comment 
                    , (SELECT COUNT(1) from Logging.Exceptions where [Key] = sessionId) as exceptioncount
                    FROM   [Transaction].[sessions] tr WITH  (nolock)
                     WHERE sessionId = '7B851237-3790-4A6D-9688-2E77843E0BB1' ) withrownumber 
                     WHERE exceptioncount > -1 ) as cte

the only thing that is missing is adding counts, Is there a way to do this without having to repeat bulk of main select?

Answer 1

Try this way..

Here I have replaced Co-related Sub query with Outer Apply. Up to my knowledge Corelated Sub query will compare every record of Outer Set with every record of Inner set. Where as Outer Apply will execute per row once.

;WITH CTE AS (
    SELECT ROW_NUMBER() OVER (ORDER BY StartDate DESC) AS rownumber
    , sessionid
    , policynumber
    , startdate  AS [Timestamp]
    , requesturl AS [Instance]
    , clientsource
    , '' Comment
    , EXC.exceptioncount 
    FROM [Transaction].[sessions] tr WITH (NOLOCK)
    OUTER APPLY
    (
        SELECT COUNT(1) AS exceptioncount 
            FROM Logging.Exceptions
            WHERE [Key] = tr.sessionId
    )AS EXC
    WHERE sessionId = '7B851237-3790-4A6D-9688-2E77843E0BB1'
)

SELECT 
(SELECT COUNT(1) FROM CTE WHERE exceptioncount > - 1 ) AS TotalRecords
,(SELECT COUNT(1) FROM CTE WHERE exceptioncount > - 1 ) AS TotalDisplayRecords
, sessionid
, policynumber
, [Timestamp]
, [Instance]
, clientsource
, Comment
, exceptioncount 
FROM CTE 
WHERE rownumber BETWEEN 1 AND 10

Answer 2

This is what I ended up with, I don't like it very much and would appreciate if someone would come up with more elegant solution

    select * from 
            (SELECT   
                count(1) TotalRecords,
                count(1) TotalDisplayRecords,
                * from (
                    SELECT ROW_NUMBER() OVER (ORDER BY "Timestamp"  desc) AS rownumber, * from (
                        SELECT sessionid, policynumber, startdate  "Timestamp", requesturl "Instance", clientsource, 
                            (SELECT COUNT(1) from Logging.Exceptions  WITH  (nolock) where [Key] = sessionId) as exceptioncount,
                        '' Comment 
                        FROM [Transaction].[sessions] tr WITH (nolock)
                        WHERE sessionId = '7B851237-3790-4A6D-9688-2E77843E0BB1') withrownumber 
                WHERE exceptioncount > -1 ) as cte
                group by rownumber, sessionid, policynumber,  "Timestamp",  "Instance", clientsource ,  Comment, exceptioncount) as r
        WHERE r.rownumber BETWEEN 1 AND 10