simple query question, using ROLLUP instead of COMPUTE

  • Glen_A

    SSC Eights!

    Points: 983

    A database was upgraded from 2008 to 2014 sqlserver, a report that was running using  COMPUTE to summarize totals quit working, as this function was deprecated with SS 2012.

    I wrote this query some years ago, adapted from an Oracle database I was pulling similar information from.

    The original query gave nice totals by SystemUser, and was like this:

    use AuditDb
    go

    select
    cast(SystemUser as varchar(25)) as "User",
    LogonTime as "Date",
    cast(HOST_NAME as varchar(25)) as "Workstation",
    SPID as "session",
    cast(APP_NAME as varchar(55)) as "Program"
    from ServerLogonHistory
    where LogonTime between '01-jun-19' and '30-jun-19'
    and app_name not like 'Microsoft SQL Server VSS Writer'
    order by SystemUser, LogonTime
    compute count(SPID) by SystemUser
    go

    I tried to replicate it using ROLLUP with this:

    use AuditDb
    go
    select
    cast(SystemUser as varchar(25)) as "User",
    LogonTime as "Date",
    cast(HOST_NAME as varchar(25)) as "Workstation",
    cast(APP_NAME as varchar(55)) as "Program",
    SPID as "session",
    count(APP_NAME)
    from ServerLogonHistory
    where LogonTime between '01-jun-19' and '30-jun-19'
    and app_name not like 'Microsoft SQL Server VSS Writer'
    --order by SystemUser, LogonTime
    group by SystemUser, SPID, HOST_NAME,APP_NAME,LogonTime
    with rollup
    go

    I am missing something here, as the results look a lot different. The ROLLUP also did not like the order by clause in front of the group by clause.

    What am I doing wrong here?

     

     

  • ScottPletcher

    SSC Guru

    Points: 98286

    Try this:

    select 
    cast(SystemUser as varchar(25)) as "User",
    LogonTime as "Date",
    cast(HOST_NAME as varchar(25)) as "Workstation",
    SPID as "session",
    cast(APP_NAME as varchar(55)) as "Program",
    count(SPID) over () AS SystemUserCount /*add this line*/
    from ServerLogonHistory
    where LogonTime between '01-jun-19' and '30-jun-19'
    and app_name not like 'Microsoft SQL Server VSS Writer'
    order by SystemUser, LogonTime

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Glen_A

    SSC Eights!

    Points: 983

    Thanks for the reply, it gives me total in the added column, but does not sum by SystemUser with subtotals like the query with COMPUTE (SPID) by SystemUser did.

    The query using COMPUTE function gave a result set like this, which is what I want to replicate:

    User Date Workstation session Program
    ------------------------- ----------------------- ------------------------- ----------- -------------------------------------------------------
    USER1 2019-06-25 08:15:19.067 STATIONBPDEV 60 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:15:19.150 STATIONBPDEV 61 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:15:19.160 STATIONBPDEV 60 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:15:19.240 STATIONBPDEV 62 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:15:19.247 STATIONBPDEV 61 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:15:32.300 STATIONBPDEV 61 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:16:54.440 STATIONBPDEV 62 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:16:54.970 STATIONBPDEV 62 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:16:55.017 STATIONBPDEV 62 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:17:13.227 STATIONBPDEV 62 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:23:10.130 STATIONBPDEV 61 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:23:11.520 STATIONBPDEV 94 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:33:56.957 STATIONBPDEV 75 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:34:01.147 STATIONBPDEV 76 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:34:02.640 STATIONBPDEV 79 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:34:37.830 STATIONBPDEV 75 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:34:41.430 STATIONBPDEV 76 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:34:42.910 STATIONBPDEV 79 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:35:19.333 STATIONBPDEV 75 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:35:23.790 STATIONBPDEV 79 Microsoft SQL Server Management Studio
    USER1 2019-06-25 08:35:25.190 STATIONBPDEV 82 Microsoft SQL Server Management Studio
    USER1 2019-06-25 09:20:18.350 STATIONBPDEV 67 Microsoft SQL Server Management Studio
    USER1 2019-06-25 09:20:22.070 STATIONBPDEV 69 Microsoft SQL Server Management Studio
    USER1 2019-06-25 09:20:22.613 STATIONBPDEV 70 Microsoft SQL Server Management Studio
    USER1 2019-06-25 09:40:53.530 STATION-SERVER2SQL 75 Microsoft SQL Server Management Studio
    USER1 2019-06-25 09:40:54.030 STATION-SERVER2SQL 76 Microsoft SQL Server Management Studio
    USER1 2019-06-25 09:41:02.197 STATION-SERVER2SQL 76 Microsoft SQL Server Management Studio
    USER1 2019-06-25 09:41:02.443 STATION-SERVER2SQL 77 Microsoft SQL Server Management Studio
    USER1 2019-06-25 11:17:48.020 STATION-SERVER2SQL 87 Microsoft SQL Server Management Studio
    USER1 2019-06-25 11:17:48.477 STATION-SERVER2SQL 88 Microsoft SQL Server Management Studio
    USER1 2019-06-25 11:17:55.657 STATION-SERVER2SQL 88 Microsoft SQL Server Management Studio
    USER1 2019-06-25 11:18:00.900 STATION-SERVER2SQL 89 Microsoft SQL Server Management Studio
    USER1 2019-06-25 11:18:01.180 STATION-SERVER2SQL 91 Microsoft SQL Server Management Studio

    cnt
    -----------
    33

    User Date Workstation session Program
    ------------------------- ----------------------- ------------------------- ----------- -------------------------------------------------------
    USER2 2019-06-25 16:43:29.827 STATIOND08710 74 Microsoft® Windows® Operating System
    USER2 2019-06-25 16:43:51.627 STATIOND08710 75 Microsoft® Windows® Operating System

    cnt
    -----------
    2

     

    • This reply was modified 6 months, 3 weeks ago by  Glen_A.
  • Jeffrey Williams 3188

    SSC Guru

    Points: 88215

    It looks like COMPUTE is returning multiple result sets - and ROLLUP and GROUPING SETS will not reproduce those same results.  Either will return a single result set with the totals included in that result set.

    If you cannot modify the receiving system to use a single result set - then I think your only solution is to rewrite the procedure to return separate results sets with the expected totals.  This could get quite ugly...as you would probably need a query to return the initial results and a dynamic query to return multiple resultsets depending on what is being computed and the data from the initial results.

    Personally - I wouldn't even try to get this to work...I would modify the receiving system to use either the detail data provided and summarize within that system - or use ROLLUP/GROUPING SETS and modify the receiving system to parse out the totals.

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • Glen_A

    SSC Eights!

    Points: 983

    I wish they hadn't dropped the compute function, I generate a similar report in Oracle SQLPLUS that's worked with the same code from 9i to current version, using a "break on Y skip 2 compute count of X on Y" before I even issue the select statement.

    However I did get it to look somewhat better by using grouping sets as follows:

    select 
    cast(SystemUser as varchar(25)) as "User",
    LogonTime as "Date",
    cast(HOST_NAME as varchar(25)) as "Workstation",
    cast(APP_NAME as varchar(55)) as "Program",
    SPID as "session",
    count(SPID) as "count"
    from ServerLogonHistory
    where LogonTime between '01-jun-19' and '30-jun-19'
    and app_name not like 'Microsoft SQL Server VSS Writer'
    group by grouping sets ((SystemUser, SPID, HOST_NAME,APP_NAME,LogonTime),(SystemUser),());

    But instead of a tidy little count below each group I just get a row of nulls and had to add the extra count(SPID) as "count" column:

    User Date Workstation session Program Count 
    ------------------------- ----------------------- ------------------------- ----------- -------------------------------------------------------
    USER1 2019-06-25 08:15:19.067 STATIONBPDEV 60 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:15:19.150 STATIONBPDEV 61 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:15:19.160 STATIONBPDEV 60 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:15:19.240 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:15:19.247 STATIONBPDEV 61 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:15:32.300 STATIONBPDEV 61 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:16:54.440 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:16:54.970 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:16:55.017 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:17:13.227 STATIONBPDEV 62 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:23:10.130 STATIONBPDEV 61 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:23:11.520 STATIONBPDEV 94 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:33:56.957 STATIONBPDEV 75 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:34:01.147 STATIONBPDEV 76 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:34:02.640 STATIONBPDEV 79 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:34:37.830 STATIONBPDEV 75 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:34:41.430 STATIONBPDEV 76 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:34:42.910 STATIONBPDEV 79 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:35:19.333 STATIONBPDEV 75 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:35:23.790 STATIONBPDEV 79 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 08:35:25.190 STATIONBPDEV 82 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 09:20:18.350 STATIONBPDEV 67 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 09:20:22.070 STATIONBPDEV 69 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 09:20:22.613 STATIONBPDEV 70 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 09:40:53.530 STATION-SERVER2SQL 75 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 09:40:54.030 STATION-SERVER2SQL 76 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 09:41:02.197 STATION-SERVER2SQL 76 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 09:41:02.443 STATION-SERVER2SQL 77 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 11:17:48.020 STATION-SERVER2SQL 87 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 11:17:48.477 STATION-SERVER2SQL 88 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 11:17:55.657 STATION-SERVER2SQL 88 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 11:18:00.900 STATION-SERVER2SQL 89 Microsoft SQL Server Management Studio 1
    USER1 2019-06-25 11:18:01.180 STATION-SERVER2SQL 91 Microsoft SQL Server Management Studio 1
    USER1 NULL NULL NULL NULL 33
    USER2 2019-06-25 16:43:29.827 STATIOND08710 74 Microsoft® Windows® Operating System 1
    USER2 2019-06-25 16:43:51.627 STATIOND08710 75 Microsoft® Windows® Operating System 1
    USER1 NULL NULL NULL NULL 2

     

     

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply