Check License usage for Winscribe Text/DMW/Winscribe DD Mobiles

There are two scripts to run for this - one for Text Author usage and one for Mobile Phone usage. Firstly, this is the generic script for Text Author usage. Modify the dates and the site and run it against the WinscribeText database - for the Northern region we'd usually run it for the last 3 months, 6 months and 1 month for each of the 3 sites - WDHB, CMDHB and NDHB. This will return a list of all author accounts who have performed an action that should require a license during the time period. DECLARE @Authors Table ( UserID varchar(60), ImportID int, authorName varchar(255) ) Declare @Date datetime = '2024-03-01' Declare @EndDate datetime = '2024-04-01' Declare @Site varchar(5) = 'CMDHB' Insert into @Authors SELECT Distinct h.UserLogin, u.ImportId, (u.FirstName + ' ' + u.LastName) FROM [HIPAALog] h with (nolock) inner join [User] u with (nolock) on u.UserLogin = h.UserLogin and u.UserType = 1 and u.LastSite = @Site where h.AccessDateTime > @Date and h.AccessDateTime < @EndDate and h.SiteCode = @Site and h.ActionCode > 0 Insert into @Authors SELECT Distinct a.[User], u.ImportId, (u.FirstName + ' ' + u.LastName) FROM [Activity] a with (nolock) inner join Document d with (nolock) on d.DocumentID = a.DocumentID and d.SiteCode = @Site inner join [User] u with (nolock) on u.UserLogin = a.[User] and u.LastSite = @Site Where ActivityType in (0,2) and Created > @Date and Created < @EndDate --Select distinct [UserID], ImportID, authorName from @Authors DECLARE @DDAuthors Table ( DDID varchar(60), DD_authorID int, authorName varchar(255) ) Insert into @DDAuthors SELECT distinct J_Author, J_Author, ( A.A_FNAME + ' ' + A.A_SURNAME) FROM [Winscribe_CMDHB].[dbo].[job] j with (nolock) inner join [Winscribe_CMDHB].[dbo].[author] a with (nolock) on a.A_ID = J.J_AUTHOR where J_REDATE > @Date and J_REDATE < @EndDate --Select distinct [UserID], ImportID, authorName from @DDAuthors Select distinct * from @DDAuthors dd full outer join @Authors a on a.ImportID = dd.DD_authorID Delete from @Authors Delete from @DDAuthors Set @Site = 'NDHB' Insert into @Authors SELECT Distinct h.UserLogin, u.ImportId, (u.FirstName + ' ' + u.LastName) FROM [HIPAALog] h with (nolock) inner join [User] u with (nolock) on u.UserLogin = h.UserLogin and u.UserType = 1 and u.LastSite = @Site where h.AccessDateTime > @Date and h.AccessDateTime < @EndDate and h.SiteCode = @Site and h.ActionCode > 0 Insert into @Authors SELECT Distinct a.[User], u.ImportId, (u.FirstName + ' ' + u.LastName) FROM [Activity] a with (nolock) inner join Document d with (nolock) on d.DocumentID = a.DocumentID and d.SiteCode = @Site inner join [User] u with (nolock) on u.UserLogin = a.[User] and u.LastSite = @Site Where ActivityType in (0,2) and Created > @Date and Created < @EndDate --Select distinct [UserID], ImportID, authorName from @Authors Insert into @DDAuthors SELECT distinct J_Author, J_Author, ( A.A_FNAME + ' ' + A.A_SURNAME) FROM [Winscribe_NDHB].[dbo].[job] j with (nolock) inner join [Winscribe_NDHB].[dbo].[author] a with (nolock) on a.A_ID = J.J_AUTHOR where J_REDATE > @Date and J_REDATE < @EndDate --Select distinct [UserID], ImportID, authorName from @DDAuthors Select distinct * from @DDAuthors dd full outer join @Authors a on a.ImportID = dd.DD_authorID Delete from @Authors Delete from @DDAuthors Set @Site = 'WDHB' Insert into @Authors SELECT Distinct h.UserLogin, u.ImportId, (u.FirstName + ' ' + u.LastName) FROM [HIPAALog] h with (nolock) inner join [User] u with (nolock) on u.UserLogin = h.UserLogin and u.UserType = 1 and u.LastSite = @Site where h.AccessDateTime > @Date and h.AccessDateTime < @EndDate and h.SiteCode = @Site and h.ActionCode > 0 Insert into @Authors SELECT Distinct a.[User], u.ImportId, (u.FirstName + ' ' + u.LastName) FROM [Activity] a with (nolock) inner join Document d with (nolock) on d.DocumentID = a.DocumentID and d.SiteCode = @Site inner join [User] u with (nolock) on u.UserLogin = a.[User] and u.LastSite = @Site Where ActivityType in (0,2) and Created > @Date and Created < @EndDate --Select distinct [UserID], ImportID, authorName from @Authors Insert into @DDAuthors SELECT distinct J_Author, J_Author, ( A.A_FNAME + ' ' + A.A_SURNAME) FROM [Winscribe].[dbo].[job] j with (nolock) inner join [Winscribe].[dbo].[author] a with (nolock) on a.A_ID = J.J_AUTHOR where J_REDATE > @Date and J_REDATE < @EndDate --Select distinct [UserID], ImportID, authorName from @DDAuthors Select distinct * from @DDAuthors dd full outer join @Authors a on a.ImportID = dd.DD_authorID Secondly, this one will give a list of author accounts that have dictated a job using a mobile phone app in the last 3 months: Declare @Date datetime = '2024-06-01' Declare @EndDate datetime = '2024-07-01' SELECT distinct J_Author, ( A.A_FNAME + ' ' + A.A_SURNAME) as 'Author Name', 'WDHB' ,J_Source as 'Source' ,sum(J_Length) as 'Length' ,count(j_JNUMBER) as 'Count' FROM Winscribe.[dbo].[job] j inner join Winscribe.[dbo].author a on a.A_ID = J.J_AUTHOR where J_REDATE >= @Date and J_REDATE < @EndDate and J_SOURCE in (7,8) group by J_Author,( A.A_FNAME + ' ' + A.A_SURNAME), J_SOURCE order by J_Author SELECT distinct J_Author, ( A.A_FNAME + ' ' + A.A_SURNAME) as 'Author Name', 'CMDHB' ,J_Source as 'Source' ,sum(J_Length) as 'Length' ,count(j_JNUMBER) as 'Count' FROM Winscribe_CMDHB.[dbo].[job] j inner join Winscribe_CMDHB.dbo.author a on a.A_ID = J.J_AUTHOR where J_REDATE >= @Date and J_REDATE < @EndDate and J_SOURCE in (7,8) group by J_Author,( A.A_FNAME + ' ' + A.A_SURNAME), J_SOURCE order by J_Author SELECT distinct J_Author, ( A.A_FNAME + ' ' + A.A_SURNAME) as 'Author Name', 'NDHB' ,J_Source as 'Source' ,sum(J_Length) as 'Length' ,count(j_JNUMBER) as 'Count' FROM Winscribe_NDHB.[dbo].[job] j inner join Winscribe_NDHB.dbo.author a on a.A_ID = J.J_AUTHOR where J_REDATE >= @Date and J_REDATE < @EndDate and J_SOURCE in (7,8) group by J_Author,( A.A_FNAME + ' ' + A.A_SURNAME), J_SOURCE order by J_Author SELECT distinct J_Author, ( A.A_FNAME + ' ' + A.A_SURNAME) as 'Author Name', 'ADHB' ,J_Source as 'Source' ,sum(J_Length) as 'Length' ,count(j_JNUMBER) as 'Count' FROM Winscribe_ADHB.[dbo].[job] j inner join Winscribe_ADHB.dbo.author a on a.A_ID = J.J_AUTHOR where J_REDATE >= DATEADD(month, -3, GETDATE()) and J_SOURCE in (7,8) group by J_Author,( A.A_FNAME + ' ' + A.A_SURNAME), J_SOURCE order by J_Author It needs to be run against the Winscribe DD database in question - so in the northern region, it would be the Winscribe, Winscribe_ADHB, Winscribe_CMDHB, Winscribe_NDHB databases. Compile all the results into a spreadsheet and send to whoever requested it.