What is the SQL query that executes when running the License Usage report in HEAT Reporting?

Version 2

    Details

    This article provides the SQL stored procedure that executes when running the License Usage report in HEAT Reporting.


    Resolution

    1. The stored procedure that executes when running the License Usage report is 'sp_readLicenseUsage'




    2. The SQL script within the sp_readLicenseUsage is provided below and attached to KB  -

    USE [HEATSM]
    GO
    /****** Object:  StoredProcedure [dbo].[sp_readLicenseUsage]    Script Date: 5/5/2017 12:21:35 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[sp_readLicenseUsage] @Frequency as varchar(10), @FromDate as datetime, @ToDate as datetime
    AS 
    BEGIN
        -- read license usage ranging from time1 to time 2
        -- time 1: on the start of the day, or start of the week, or start of the month, by @FromDate
        -- time 2: on the end of the day, or end of the week, or end of the month, by @ToDate
        -- NULL @FromDate or @ToDate is the current date
        -- @from = time 1, @to = start of next day of time 2.
        DECLARE @from as datetime;
        DECLARE @to as datetime;
        DECLARE @utcNow as datetime;
        DECLARE @utcStart as datetime;
        DECLARE @tempDate as datetime;
        set @utcNow = DATEADD(dd, -1, getutcdate());
        set @utcStart = convert(datetime, cast(YEAR(@utcNow) as varchar(4)) + '/' + cast(MONTH(@utcNow) as varchar(2)) + '/' + cast(DAY(@utcNow) as varchar(2)), 101);
        -- calculate the usage to the latest time
        exec dbo.sp_getLicenseUsage;
        SET NOCOUNT ON;
        set @from = @fromDate;
        if (@fromDate is null) 
           set @from = @utcStart;
        set @to = @toDate;
        if (@toDate is null) 
           set @to = @utcStart;
    if (@to < @from)
    begin
      set @tempDate = @from;
      set @from = @to;
      set @to = @tempDate;
    end;
    if (@Frequency = 'daily' or @Frequency = 'hourly')
    begin
           set @from = convert(datetime, cast(YEAR(@from) as varchar(4)) + '/' + cast(MONTH(@from) as varchar(2)) + '/' + cast(DAY(@from) as varchar(2)), 101);
           set @to = DATEADD(dd, 1, convert(datetime, cast(YEAR(@to) as varchar(4)) + '/' + cast(MONTH(@to) as varchar(2)) + '/' + cast(DAY(@to) as varchar(2)), 101));
    end
    else if (@Frequency = 'monthly')
    begin
           set @from = convert(datetime, cast(YEAR(@from) as varchar(4)) + '/' + cast(MONTH(@from) as varchar(2)) + '/01', 101);
      set @tempDate = DATEADD(mm, 1, @to);
      set @to = convert(datetime, cast(YEAR(@tempDate) as varchar(4)) + '/' + cast(MONTH(@tempDate) as varchar(2))  + '/01', 101);
    end
    else
    begin
      -- weekly
           set @tempDate = DATEADD(dd, 1-DATEPART(dw, @from), @from);
      set @from = convert(datetime, cast(YEAR(@tempDate) as varchar(4)) + '/' + cast(MONTH(@tempDate) as varchar(2)) + '/' + cast(DAY(@tempDate) as varchar(2)), 101);
           set @tempDate = DATEADD(dd, 7-DATEPART(dw, @to), @to);
      set @to = DATEADD(dd, 1,  convert(datetime, cast(YEAR(@tempDate) as varchar(4)) + '/' + cast(MONTH(@tempDate) as varchar(2)) + '/' + cast(DAY(@tempDate) as varchar(2)), 101));
        end;
        if (@Frequency = 'hourly')
           select YEAR(LicenseDatetime)as Y, MONTH(LicenseDatetime) as M, DAY(LicenseDatetime) as D, DATEPART(hh, LicenseDatetime)as H,
           namedSeatsUsed, concurrentSeatsUsed as concurrentSeatsUsed, selfServiceSeatsUsed, NamedSeats, concurrentSeats
           from dbo.LicenseUsage 
           where LicenseDatetime >= @from and LicenseDatetime < @to
           order by YEAR(LicenseDatetime), MONTH(LicenseDatetime), DAY(LicenseDatetime), DATEPART(hh, LicenseDatetime);
        else if (@Frequency = 'daily')
           select YEAR(LicenseDatetime)as Y, MONTH(LicenseDatetime) as M, DAY(LicenseDatetime) as D, 0 as H,
           MAX(namedSeatsUsed) as namedSeatsUsed, MAX(concurrentSeatsUsed) as concurrentSeatsUsed, 
           MAX(selfServiceSeatsUsed) as selfServiceSeatsUsed, MAX(NamedSeats) as NamedSeats, 
           MAX(concurrentSeats) as concurrentSeats
           from dbo.LicenseUsage 
           where LicenseDatetime >= @from and LicenseDatetime < @to
           group by YEAR(LicenseDatetime), MONTH(LicenseDatetime), DAY(LicenseDatetime)
           order by YEAR(LicenseDatetime), MONTH(LicenseDatetime), DAY(LicenseDatetime);
        else if (@Frequency = 'monthly')
           select YEAR(LicenseDatetime)as Y, MONTH(LicenseDatetime) as M, 0 as D, 0 as H,
           MAX(namedSeatsUsed) as namedSeatsUsed, MAX(concurrentSeatsUsed) as concurrentSeatsUsed, 
           MAX(selfServiceSeatsUsed) as selfServiceSeatsUsed, MAX(NamedSeats) as NamedSeats, 
           MAX(concurrentSeats) as concurrentSeats
           from dbo.LicenseUsage 
           where LicenseDatetime >= @from and LicenseDatetime < @to
           group by YEAR(LicenseDatetime), MONTH(LicenseDatetime)
           order by YEAR(LicenseDatetime), MONTH(LicenseDatetime);
        else if (@Frequency = 'weekly')
           select YEAR(LicenseDatetime)as Y, 1 as M, DATEPART(wk, LicenseDatetime) as D, 0 as H,
           MAX(namedSeatsUsed) as namedSeatsUsed, MAX(concurrentSeatsUsed) as concurrentSeatsUsed, 
           MAX(selfServiceSeatsUsed) as selfServiceSeatsUsed, MAX(NamedSeats) as NamedSeats, 
           MAX(concurrentSeats) as concurrentSeats
           from dbo.LicenseUsage 
           where LicenseDatetime >= @from and LicenseDatetime < @to
           group by YEAR(LicenseDatetime), DATEPART(wk, LicenseDatetime)
           order by YEAR(LicenseDatetime), DATEPART(wk, LicenseDatetime);
    END