SQL Audit Log Running Totals

Question

I have a table with an audit log:

BugId       Timestamp               Status
1           2010-06-24 10:00:00     open
2           2010-06-24 11:00:00     open
1           2010-06-25 12:00:00     closed
2           2010-06-26 13:00:00     closed

I want a running total of open and closed bugs like:

Timestamp            #       Status
2010-06-25 00:00:00  2       open
2010-06-26 00:00:00  1       open
2010-06-26 00:00:00  1       closed
2010-06-27 00:00:00  2       closed

How may I do this query (or similar) in Microsoft SQL Server 2000?

The output is intended to be used to feed a time series chart so I do not care if there are rows with 0 output since I will probably only select a timespan like the last month.

Answer 1

use tempdb
go
create table audit_log
(
BugID integer not null
, dt_entered_utc datetime not null  default ( getutcdate () )
, [status] varchar(10) not null
);


INSERT INTO audit_log ( BugID, dt_entered_utc, [status] ) VALUES ( 1, '2010-06-24 10:00', 'open' );
INSERT INTO audit_log ( BugID, dt_entered_utc, [status] ) VALUES ( 2, '2010-06-24 11:00', 'open' );
INSERT INTO audit_log ( BugID, dt_entered_utc, [status] ) VALUES ( 1, '2010-06-25 12:00', 'closed' );
INSERT INTO audit_log ( BugID, dt_entered_utc, [status] ) VALUES ( 2, '2010-06-26 13:00', 'closed' );

SELECT
    [Date] = CAST ( CONVERT ( varchar, a.dt_entered_utc, 101 ) as datetime )
    , [#] = COUNT ( 1 )
    , [Status] =  a.status
FROM audit_log a
GROUP BY CAST ( CONVERT ( varchar, a.dt_entered_utc, 101 ) as datetime ), a.status
ORDER by [Date] ASC

Date                           #       Status
2010-06-24 00:00:00.000     2       open
2010-06-25 00:00:00.000     1       closed
2010-06-26 00:00:00.000     1       closed

Answer 2

I think the output actually matches the sample data: on the 25th (12am), there are two open bugs. On the 26th, there is one open bug and one closed. And by the 27th, all bugs are closed.

It isn't clear how the main dates should be created. For my example, I pre-loaded the dates that I knew to be right but this could be accomplished in a variety of ways depending on the requirements of the user.

Anyway, the code is below. This should work for instances where a bug is opened and closed multiple times on the same day. It operates under the assumption that a bug cannot be opened and closed at the same time.

/** Setup the tables **/
IF OBJECT_ID('tempdb..#bugs') IS NOT NULL DROP TABLE #bugs

CREATE TABLE #bugs (
  BugID INT,
  [Timestamp] DATETIME,
  [Status] VARCHAR(10)
) 

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

CREATE TABLE #dates ( 
  [Date] DATETIME
) 

/** Load the sample data. **/
INSERT #bugs 
SELECT 1, '2010-06-24 10:00:00', 'open'   UNION ALL
SELECT 2, '2010-06-24 11:00:00', 'open'   UNION ALL
SELECT 1, '2010-06-25 12:00:00', 'closed' UNION ALL
SELECT 2, '2010-06-26 13:00:00', 'closed' 

/** Build an arbitrary date table **/
INSERT #dates 
SELECT '2010-06-24' UNION ALL  
SELECT '2010-06-25' UNION ALL  
SELECT '2010-06-26' UNION ALL  
SELECT '2010-06-27' 


/** 
Subquery x:
For each date in the #date table,
get the BugID and it's last status.
This is for BugIDs that have been
opened and closed on the same day.

Subquery y:
Drawing from subquery x, get the 
date, BugID, and Status of its
last status for that day

Main query:
For each date, get the count
of the most recent statuses for 
that date. This will give the
running totals of open and 
closed bugs for each date
**/
SELECT
  [Date],
  COUNT(*) AS [#],
  [Status]
FROM (
  SELECT 
    Date,
    x.BugID,
    b.[Status]
  FROM ( 
    SELECT
      [Date],
      BugID,
      MAX([Timestamp]) AS LastStatus
    FROM #dates d 
    INNER JOIN #bugs b 
    ON d.[Date] > b.[Timestamp]
    GROUP BY
      [Date],
      BugID
    ) x 
  INNER JOIN #bugs b
  ON x.BugID = b.BugID
  AND x.LastStatus = b.[Timestamp]
) y 
GROUP BY [Date], [Status]
ORDER BY [Date], CASE WHEN [Status] = 'Open' THEN 1 ELSE 2 END

Results:

Date                    #           Status
----------------------- ----------- ----------
2010-06-25 00:00:00.000 2           open
2010-06-26 00:00:00.000 1           open
2010-06-26 00:00:00.000 1           closed
2010-06-27 00:00:00.000 2           closed