V_QUERY_ACTIVEUSERMONITORINGLOG

/*
Generated by Blackbaud AppFx Platform
Date:  9/1/2024 11:51:14 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3800.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_ACTIVEUSERMONITORINGLOG AS



                with [DATA] as
                (
                    select 
                        ACTIVEUSERMONITORINGLOG.ID,
                        ACTIVEUSERMONITORINGLOG.APPUSERID,
                        coalesce(ACTIVEUSERMONITORINGLOG.SESSIONSTART, getdate()) SESSIONSTART,
                        coalesce(ACTIVEUSERMONITORINGLOG.SESSIONEND, getdate()) SESSIONEND
                    from ACTIVEUSERMONITORINGLOG
                    where ACTIVEUSERMONITORINGLOG.APPUSERID is not null
                ),
                [AGGREGATED] as
                (
                    --This recursive CTE will return at least one row for each day that a logged session spans

                    --(I believe it will return exactly one row, but I'm not certain. Group bys in the final result should help with that at any rate.)

                    --This technique was adapted from http://stackoverflow.com/a/8183886/5968

                    select
                        [DATA].ID,
                        [DATA].APPUSERID,
                        [DATA].SESSIONSTART,
                        [DATA].SESSIONEND,
                        cast([DATA].SESSIONSTART as date) [STARTDATE],
                        cast([DATA].SESSIONEND as date) [ENDDATE],
                        cast([DATA].SESSIONSTART as date) [ROWDATE]
                    from [DATA]
                    union all 
                    select
                        [AGGREGATED].ID,
                        [AGGREGATED].APPUSERID,
                        [AGGREGATED].SESSIONSTART,
                        [AGGREGATED].SESSIONEND,
                        cast([AGGREGATED].SESSIONSTART as date) [STARTDATE],
                        cast([AGGREGATED].SESSIONEND as date) [ENDDATE],
                        dateadd(day,1,[ROWDATE])
                    from [AGGREGATED]
                    where [ROWDATE] < [AGGREGATED].ENDDATE
                )
                --JamesWill 2013-10-16 PBI 258290 This query view aggregates the active users and returns at most one row per user per day. 

                --For sessions which spanned multiple days, one row is returned for each of those sessions (even though there is only one session

                --in the underlying log). For users with multiple sessions a day, return the earliest and latest activity times for that day 

                --(even if that's in a different day, as would be the case for day-spanning sessions)

                select 
                    AGGREGATED.APPUSERID,
                    min(AGGREGATED.SESSIONSTART) [EARLIESTACTIVITY],
                    max(AGGREGATED.SESSIONEND) [LATESTACTIVITY],
                    AGGREGATED.ROWDATE as ACTIVEDATE
                from AGGREGATED
                group by AGGREGATED.ROWDATE, AGGREGATED.APPUSERID