Calculating current consecutive days from a table

Question

I have what seems to be a common business request but I can't find no clear solution. I have a daily report (amongst many) that gets generated based on failed criteria and gets saved to a table. Each report has a type id tied to it to signify which report it is, and there is an import event id that signifies the day the imports came in (a date column is added for extra clarification). I've added a sqlfiddle to see the basic schema of the table (renamed for privacy issues).

http://www.sqlfiddle.com/#!3/81945/8

All reports currently generated are working fine, so nothing needs to be modified on the table. However, for one report (type 11), not only I need pull the invoices that showed up today, I also need to add one column that totals the amount of consecutive days from date of run for that invoice (including current day). The result should look like the following, based on the schema provided:

INVOICE     MESSAGE     EVENT_DATE      CONSECUTIVE_DAYS_ON_REPORT
12345       Yes         July, 30 2013    6
54355       Yes         July, 30 2013    2
644644      Yes         July, 30 2013    4

I only need the latest consecutive days, not any other set that may show up. I've tried to run self joins to no avail, and my last attempt is also listed as part of the sqlfiddle file, to no avail. Any suggestions or ideas? I'm quite stuck at the moment.

FYI: I am working in SQL Server 2000! I have seen a lot of neat tricks that have come out in 2005 and 2008, but I can't access them.

Your help is greatly appreciated!

Answer 1

Something like this? http://www.sqlfiddle.com/#!3/81945/14

SELECT
  [final].*,
  [last].total_rows
FROM
  tblEventInfo   AS [final]
INNER JOIN
(
  SELECT
    [first_of_last].type_id,
    [first_of_last].invoice,
    MAX([all_of_last].event_date)   AS event_date,
    COUNT(*)                        AS total_rows
  FROM
  (
    SELECT
      [current].type_id,
      [current].invoice,
      MAX([current].event_date)   AS event_date
    FROM
      tblEventInfo   AS [current]
    LEFT JOIN
      tblEventInfo   AS [previous]
        ON  [previous].type_id    = [current].type_id
        AND [previous].invoice    = [current].invoice
        AND [previous].event_date = [current].event_date-1
    WHERE
          [current].type_id = 11
      AND [previous].type_id IS NULL
    GROUP BY
      [current].type_id,
      [current].invoice
  )
    AS [first_of_last]
  INNER JOIN
    tblEventInfo  AS [all_of_last]
      ON  [all_of_last].type_id     = [first_of_last].type_id
      AND [all_of_last].invoice     = [first_of_last].invoice
      AND [all_of_last].event_date >= [first_of_last].event_date
  GROUP BY
    [first_of_last].type_id,
    [first_of_last].invoice
)
  AS [last]
    ON  [last].type_id    = [final].type_id
    AND [last].invoice    = [final].invoice
    AND [last].event_date = [final].event_date

The inner most query looks up the starting record of the last block of consecutive records.

Then that joins on to all the records in that block of consecutive records, giving the final date and the count of rows (consecutive days).

Then that joins on to the row for the last day to get the message, etc.

Make sure that in reality you have an index on (type_id, invoice, event_date).