pootzko
5/9/2017 - 12:04 PM

Calculate QuickDBD user stats

Calculate QuickDBD user stats

SELECT [Id]
      ,[Email]
      ,DATEDIFF(SECOND,{d '1970-01-01'}, [DateCreated]) as DateCreated
      ,DATEDIFF(SECOND,{d '1970-01-01'}, [LastActive]) as LastActive
      ,(SELECT COUNT(*) FROM SchemaSource WHERE CreatedById=AspNetUsers.Id AND IsDeleted=0) as Diagrams
      ,(SELECT COUNT(*) FROM SchemaSource WHERE CreatedById=AspNetUsers.Id AND IsDeleted=1) as DiagramsDeleted
      ,(SELECT ISNULL(MAX(TableCount), 0) FROM SchemaSource WHERE CreatedById=AspNetUsers.Id AND IsDeleted=0) as MaxTables
      ,(SELECT ISNULL(AVG(TableCount), 0) FROM SchemaSource WHERE CreatedById=AspNetUsers.Id AND IsDeleted=0) as AvgTables
  FROM [dbo].[AspNetUsers]