[sql-server] Create a view with ORDER BY clause

As one of the comments in this posting suggests using stored procedures to return the data... I think that is the best answer. In my case what I did is wrote a View to encapsulate the query logic and joins, then I wrote a Stored Proc to return the data sorted and the proc also includes other enhancement features such as parameters for filtering the data.

Now you have to option to query the view, which allows you to manipulate the data further. Or you have the option to execute the stored proc, which is quicker and more precise output.

STORED PROC Execution to query data

VIEW Definition

USE [DBA]
GO

/****** Object:  View [olap].[vwUsageStatsLogSessionsRollup]    Script Date: 2/19/2019 10:10:06 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--USE DBA
-- select * from olap.UsageStatsLog_GCOP039 where CubeCommand='[ORDER_HISTORY]'
;

ALTER VIEW [olap].[vwUsageStatsLogSessionsRollup] as
(
    SELECT --*
        t1.UsageStatsLogDate
        , COALESCE(CAST(t1.UsageStatsLogDate AS nvarchar(100)), 'TOTAL- DATES:') AS UsageStatsLogDate_Totals
        , t1.ADUserNameDisplayNEW
        , COALESCE(t1.ADUserNameDisplayNEW, 'TOTAL- USERS:') AS ADUserNameDisplay_Totals
        , t1.CubeCommandNEW
        , COALESCE(t1.CubeCommandNEW, 'TOTAL- CUBES:') AS CubeCommand_Totals
        , t1.SessionsCount
        , t1.UsersCount
        , t1.CubesCount
    FROM
    (
        select 
            CAST(olapUSL.UsageStatsLogTime as date) as UsageStatsLogDate
            , olapUSL.ADUserNameDisplayNEW
            , olapUSL.CubeCommandNEW
            , count(*) SessionsCount
            , count(distinct olapUSL.ADUserNameDisplayNEW) UsersCount
            , count(distinct olapUSL.CubeCommandNEW) CubesCount
        from 
            olap.vwUsageStatsLog olapUSL
        where CubeCommandNEW != '[]'
        GROUP BY CUBE(CAST(olapUSL.UsageStatsLogTime as date), olapUSL.ADUserNameDisplayNEW, olapUSL.CubeCommandNEW )
            ----GROUP BY 
            ------GROUP BY GROUPING SETS
            --------GROUP BY ROLLUP
    ) t1

    --ORDER BY
    --  t1.UsageStatsLogDate DESC
    --  , t1.ADUserNameDisplayNEW
    --  , t1.CubeCommandNEW
)
;


GO

STORED PROC Definition

USE [DBA]
GO

/****** Object:  StoredProcedure [olap].[uspUsageStatsLogSessionsRollup]    Script Date: 2/19/2019 9:39:31 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      BRIAN LOFTON
-- Create date: 2/19/2019
-- Description: This proceedured returns data from a view with sorted results and an optional date range filter.
-- =============================================
ALTER PROCEDURE [olap].[uspUsageStatsLogSessionsRollup]
    -- Add the parameters for the stored procedure here
    @paramStartDate date = NULL,
    @paramEndDate date = NULL,
    @paramDateTotalExcluded as int = 0,
    @paramUserTotalExcluded as int = 0,
    @paramCubeTotalExcluded as int = 0
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @varStartDate as date 
        = CASE  
            WHEN @paramStartDate IS NULL THEN '1900-01-01' 
            ELSE @paramStartDate 
        END
    DECLARE @varEndDate as date 
        = CASE  
            WHEN @paramEndDate IS NULL THEN '2100-01-01' 
            ELSE @paramStartDate 
        END

    -- Return Data from this statement
    SELECT 
        t1.UsageStatsLogDate_Totals
        , t1.ADUserNameDisplay_Totals
        , t1.CubeCommand_Totals
        , t1.SessionsCount
        , t1.UsersCount
        , t1.CubesCount
        -- Fields with NULL in the totals
            --  , t1.CubeCommandNEW
            --  , t1.ADUserNameDisplayNEW
            --  , t1.UsageStatsLogDate
    FROM 
        olap.vwUsageStatsLogSessionsRollup t1
    WHERE

        (
            --t1.UsageStatsLogDate BETWEEN @varStartDate AND @varEndDate
            t1.UsageStatsLogDate BETWEEN '1900-01-01' AND '2100-01-01'
            OR t1.UsageStatsLogDate IS NULL
        )
        AND
        (
            @paramDateTotalExcluded=0
            OR (@paramDateTotalExcluded=1 AND UsageStatsLogDate_Totals NOT LIKE '%TOTAL-%')
        )
        AND
        (
            @paramDateTotalExcluded=0
            OR (@paramUserTotalExcluded=1 AND ADUserNameDisplay_Totals NOT LIKE '%TOTAL-%')
        )
        AND
        (
            @paramCubeTotalExcluded=0
            OR (@paramCubeTotalExcluded=1 AND CubeCommand_Totals NOT LIKE '%TOTAL-%')
        )
    ORDER BY
            t1.UsageStatsLogDate DESC
            , t1.ADUserNameDisplayNEW
            , t1.CubeCommandNEW

END


GO