Execute multiple aggregates at once (SQL, LINQ).. or with better performance?

Question

I have a table with records which include a datetime column "CreationDate".

I need to get the following information for every of the last 90 days:

• How many records were there in total in existence
• How many records were added on that day

I could do this through a loop of counting of course, but this would hit the database 90 times... is there a better way of doing this aggregate without having to riddle the DB with requests?

I'm using C#, LINQ, SQL Server 2008.

Answer 1

Pull the records (or just the ids and creation dates, if that is all you need), and then perform the logic in code. One SELECT against the DB.

edit

In response to comment:

You can get the number of items for each day with a query like this:

SELECT CreationDate, COUNT(CreationDate) FROM MyTable GROUP BY CreationDate

Note that this assumes no times in CreationDate. If you have different times, the grouping won't work -- you'll have to flatten those out.

You can also add a WHERE clause to only look at the items from the last 90 days.

Answer 2

Are you looking for something like this?

WITH CTE AS
    (SELECT COUNT(*) OVER () AS TotalCount,
        CAST(CONVERT(VARCHAR, CreationDate, 101) as DATETIME) as DateValue, *
    FROM MyTable
    WHERE CreationDate >= DATEADD(DD, -90, GETDATE())
    )

SELECT DateValue, TotalCount, COUNT(*) as RowCount
FROM CTE
group by DateValue, TotalCount
order by DateValue
;

Answer 3

Bringing back the daily totals for the 90 days then aggregating in your application would probably be the best idea. There is currently no particularly satisfactory way of calculating running totals in SQL Server. An example of how you could do it though is below (using sys.objects as the demo table)

    IF OBJECT_ID('tempdb..#totals') IS NOT NULL
       DROP TABLE #totals

DECLARE @EndDate DATE = CURRENT_TIMESTAMP;
DECLARE @StartDate DATE = DATEADD(DAY,-89,@EndDate);

    WITH DateRange AS
    (
        SELECT
            @StartDate [DATE]
        UNION ALL
        SELECT
            DATEADD(DAY, 1, DATE) [DATE]
        FROM
            DateRange
        WHERE
            DATE < @EndDate
    )

    SELECT DATE,COUNT(t.modify_date) AS DailyTotal
    INTO #totals
    FROM DateRange LEFT JOIN sys.objects t
    ON  modify_date BETWEEN @StartDate AND @EndDate 
           AND CAST(t.modify_date AS DATE) = DateRange.Date
    GROUP BY DATE 
    ORDER BY DATE


DECLARE @BaseNumber INT = (SELECT COUNT(*) FROM sys.objects WHERE 
         modify_date < @StartDate);


    SELECT t1.Date,
           t1.DailyTotal, 
           @BaseNumber + SUM(t2.DailyTotal) AS RunningTotal
    FROM #totals t1 
     JOIN #totals t2 ON t2.date <= t1.date 
              /*Triangular join will yield 91x45 rows that are then grouped*/
    GROUP BY  t1.Date,t1.DailyTotal
    ORDER BY  t1.Date