Retrieving Time Dependent Data Statistics (counts) by flag, split on foreign id, across multiple tables, returned in one dataset

Question

I have a table of items that are split on a siteId and a status.

CREATE TABLE ItemDetail (
    ItemNumber    long,
    SiteId        int,
    Status        int,
    Created       datetime
)

And then I also have a user's table

CREATE TABLE UserDetail (
    UserId        int,
    Suspended     int
)

Status has 7 possible values (0-6) representing different queues. Let's call this variable x. Suspended, should only have 0 (active) or 1 (suspended). (don't ask me why it's an int, I didn't build it). Data is shown per site in a configurable time frame. Default is 5 days back. Let's call this variable a. I want to return, in one call, a dataset like this:

ActiveUserCount        int
SuspendedUserCount     int,
Queue0Count            int,
Queue0TodayCount       int,
Queue1Count            int,
Queue1TodayCount       int,
...

Where QueueXCount is everything in the last a days by status and siteid. QueueXTodayCount is everything with status x that happened today. So far, I have started a sproc like this

CREATE PROCEDURE GetSiteStatistics
    @SiteId       int,
    @Window       int
AS
BEGIN
DECLARE @Today datetime
DECLARE @Tomorrow datetime
DECLARE @CutOff datetime

SET @Today = (CAST(YEAR(getdate()) as varchar) + 
    RIGHT('00' + CAST(MONTH(getdate()) as varchar), 2) + 
    RIGHT('00' + CAST(DAY(getdate()) as varchar), 2))

SET @Tomorrow  = DATEADD(dd, 1, @Today)

SET @CutOff = DATEADD(dd, @Window + 1, @Today) 

DECLARE
    @SuspendedUserCount         int,
    @ActiveUserCount            int,
    @Queue0Count                int,
    @Queue0TodayCount           int,
    ...

SELECT @SuspendedUserCount = count(UserId) FROM UserDetail WHERE Suspended = 1 AND SiteId = @SiteId
SELECT @ActiveUserCount = count(UserId) FROM UserDetail WHERE Suspended = 0 AND SiteId = @SiteId
SELECT @Queue0Count = count(ItemNumber) FROM ItemDetail WHERE Status = 0 AND SiteId = @SiteId   AND Created >= @Today AND Created < @CutOff 
SELECT @Queue0TodayCount = count(ItemNumber) FROM ItemDetail WHERE Status = 0 AND SiteId = @SiteId AND Created >= @Today AND Created < @Tomorrow 
...

SELECT @SuspendedUserCount AS SuspendedUsers, @ActiveUserCount AS ActiveUsers, @Queue0Count AS Queue0, @Queue0TodayCount AS @Queue0Today...
END

As if that isn't complicated enough, I am using Fluent Nhibernate. I'm not against using a sproc if I have to, but I have considered creating a view with the counts by day, pulling that in using nhibernate and a query to pick by date range, and then summing the amounts where appropriate in code.

I just have a feeling I am making this more complicated than I have to. There must be a better way.

Answer 1

I created a view using CTE and a pivot

CREATE VIEW [dbo].[SiteQueueDailyStatistics]
AS

WITH statCTE AS (
    SELECT
        Count(ItemNumber)as ItemCount,
        SiteId,
        Status,
        DATEADD(dd, 0, DATEDIFF(dd, 0, ScanDate)) AS ScanDay
    FROM
        ItemDetail
    group by  SiteId, Status, DATEADD(dd, 0, DATEDIFF(dd, 0, ScanDate))
)

SELECT 
    SiteId, 
    ScanDay, 
    ISNULL([0], 0) AS Queue0,
    ISNULL([1], 0) AS Queue1,
    ISNULL([2], 0) AS Queue2,
    ISNULL([3], 0) AS Queue3,
    ISNULL([4], 0) AS Queue4,
    ISNULL([5], 0) AS Queue5,
    ISNULL([6], 0) AS Queue6
FROM 
    statCTE
PIVOT
(
    SUM(ItemCount)
    FOR [Status] IN ([0], [1], [2], [3], [4], [5], [6])
)
AS p


GO

And then I get all my data and populate a composite object in my model that holds the statistics across multiple tables.