Select a query for duplicate dates with a tally table

Question

I have an event table that indicates how often the event occurs (in days). I would like to select all occurrences of the event within a given date range, including the calculated occurrences (for example, if the first date of the event is January 6, 2011, and this happens every 7 days, you will see January 13 and January 20 in the results).

Here's what my events table looks like:

event_ID INT,
event_title NVARCHAR(50),
first_event_date DATETIME,
occurs_every INT

After reading this article, it seems that the most effective way to deal with this is with a table of tables, but I couldn’t turn my head around how to return the results that I am looking for.

Let's say I have data that looks like this:

event_ID | event_title | first_event_date | occurs_every
1 | Event 1 | 1/6/2011 | 7
2 | Event 2 | 1/8/2011 | 3

The results I'm looking for will be as follows:

event_ID | event_title | event_date |
1 | Event 1 | 1/6/2011 |
2 | Event 2 | 1/8/2011 |
1 | Event 1 | 1/13/2011 |
2 | Event 2 | 12/12/2011 |
2 | Event 2 | 1/16/2011 |
1 | Event 1 | 1/20/2011 |
(etc)

Any suggestions? Edit: I am using SQL Server 2008.

Additional Information:

I have a working query, but it looks pretty dumb, and performance bothers me when I get more data into a table.

First, for reference, this is a Tally table:

SELECT TOP 11000 
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

Now, here is the kludgy select query:

SELECT event_ID,
        event_title,
        first_event_date,
        DATEADD (dd, occurs_every * (tN - 1), [first_event_date]) AS occurrence
FROM dbo.Events
        CROSS JOIN dbo.Tally t
WHERE   DATEADD(dd, occurs_every * ( t.N - 1 ), [first_event_date]) 

, 1000 , . , . , , "ORDER BY".

Answer 1

-, , . , " ", , .

OPs, , , , , . , 2015 2016 , "-", " ", , RBAR While Loop, rCTE ( CTE).

, Im 2005 , - , 2005 , 2008+ .

. .

--====================================================================
--      Presets
--====================================================================
--===== Declare and prepopulate some obviously named variables
DECLARE  @StartDate     DATETIME
        ,@EndDate       DATETIME
        ,@Days          INT
        ,@Events        INT
        ,@MaxEventGap   INT
;
 SELECT  @StartDate     = '2015-01-01' --Inclusive date
        ,@EndDate       = '2017-01-01' --Exclusive date 
        ,@Days          = DATEDIFF(dd,@StartDate,@EndDate)
        ,@Events        = 1000
        ,@MaxEventGap   = 30 --Note that 1 day will be the next day
;
--====================================================================
--      Create the Test Table
--====================================================================
--===== If the test table already exists, drop it to make reruns of 
     -- this demo easier. I also use a Temp Table so that we don't 
     -- accidenttly screw up a real table.
     IF OBJECT_ID('tempdb..#Events','U') IS NOT NULL
        DROP TABLE #Events
;
--===== Build the test table.
     -- I'm following what the OP did so that anyone with a case 
     -- sensitive server won't have a problem.
 CREATE TABLE #Events
        (
        event_ID            INT,
        event_title         NVARCHAR(50),
        first_event_date    DATETIME,
        occurs_every        INT
        )
;
--====================================================================
--      Populate the Test Table
--====================================================================
--===== Build @Events number of events using the previously defined
     -- start date and number of days as limits for the random dates.
     -- To make life a little easier, I'm using a CTE with a 
     -- "pseudo-cursor" to form most of the data and then an
     -- external INSERT so that I can name the event after the
     -- event_ID.
   WITH cteGenData AS
        (
         SELECT TOP (@Events)
                event_ID         = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
               ,first_event_date = DATEADD(dd, ABS(CHECKSUM(NEWID())) % @Days, @StartDate)
               ,occurs_every     = ABS(CHECKSUM(NEWID())) % 30 + 1
           FROM      sys.all_columns ac1 --Has at least 4000 rows in it for most editions
          CROSS JOIN sys.all_columns ac2 --Just in case it doesn't for Express ;-)
        )
 INSERT INTO #Events
        (event_ID, event_title, first_event_date, occurs_every)
 SELECT  event_ID
        ,event_title = 'Event #' + CAST(event_id AS VARCHAR(10))
        ,first_event_date
        ,occurs_every
   FROM cteGenData
;
--===== Let see the first 10 rows
 SELECT TOP 10 *
   FROM #Events
  ORDER BY event_ID
;

10 , first_even_datet incoming_every - , .

event_ID event_title first_event_date        occurs_every
-------- ----------- ----------------------- ------------
1        Event #1    2016-10-12 00:00:00.000 10
2        Event #2    2015-04-25 00:00:00.000 28
3        Event #3    2015-11-08 00:00:00.000 4
4        Event #4    2016-02-16 00:00:00.000 25
5        Event #5    2016-06-11 00:00:00.000 15
6        Event #6    2016-04-29 00:00:00.000 14
7        Event #7    2016-04-16 00:00:00.000 9
8        Event #8    2015-03-29 00:00:00.000 2
9        Event #9    2016-02-14 00:00:00.000 29
10       Event #10   2016-01-23 00:00:00.000 8

, Tally, OPs. . , , ( ) . "-" , "syscolumns".

--===== Create a Tally Table with enough sequential numbers
     -- for more than 30 years worth of dates.
SELECT TOP 11000 
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM      sys.all_columns sc1
  CROSS JOIN sys.all_columns sc2
;
--===== Add the quintessential Unique Clustered Index as the PK.
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;

. OP , , . , , " " , ( , ). UDF, , , .

OP- .

SET STATISTICS TIME,IO ON
;
 SELECT event_id,
        event_title,
        first_event_date,
        DATEADD(dd, occurs_every * ( t.N - 1 ), [first_event_date]) AS Occurrence
   FROM #Events 
  CROSS JOIN dbo.Tally t
  WHERE t.N <= DATEDIFF(dd,first_event_date,'2017-03-01') / occurs_every + 1
  ORDER BY Occurrence
;
SET STATISTICS TIME,IO OFF
;

OP. , .

(61766 row(s) affected)
Table 'Worktable'. Scan count 4, logical reads 118440, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 4, logical reads 80, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Events_____________________________________________________________________________________________________________00000000001F'. Scan count 5, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4196 ms,  elapsed time = 1751 ms.

, , While Loop rCTE. ?

, , 11 - -SARGable (SARG = "ARGument " -SARGable , ), CROSS JOIN Tally Table 1000 #Events. ACTUAL , ESTIMATED , .

, "N" Tally , Tally #Events. , , Tally .

, ? , t.N , , , t.N, , . , , , WHERE, t.N SARGable ( , ).

SET STATISTICS TIME,IO ON
;
 SELECT event_id,
        event_title,
        first_event_date,
        DATEADD(dd, occurs_every * ( t.N - 1 ), [first_event_date]) AS Occurrence
   FROM #Events 
  CROSS JOIN dbo.Tally t
  WHERE t.N <= DATEDIFF(dd,first_event_date,'2017-03-01') / occurs_every + 1
  ORDER BY Occurrence
;
SET STATISTICS TIME,IO OFF
;

. 61,766 ( ) 11 .

.

(61766 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Events_____________________________________________________________________________________________________________00000000001F'. Scan count 5, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Tally'. Scan count 1000, logical reads 3011, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 78 ms,  elapsed time = 528 ms.

• 52,79 5,279%.
• 2,32 232%.
• 38,27 3,827%.

... 1 WHERE.

7, CTE Itzik Ben-Gan ( rCTE).

, , Tally , , . "Best Practices", , SARGable WHERE, , .

, OP, , . , - . , rCTE , , . , rCTE , SQLServerCentral.com, . , .

RBAR: CTE

Answer 2

SQL Server 2008 CTE.

DECLARE @StartDate DATE, @EndDate DATE
SET @StartDate = '20110106'
SET @EndDate = '20110228';


WITH DateTable AS
(
    SELECT Event_id, event_title, event_date, occurs_every
    FROM tally_table
    UNION ALL
    SELECT event_ID, event_title, DATEADD(DAY,occurs_every,event_date), occurs_every
    FROM DateTable
    WHERE DATEADD(DAY,occurs_every,event_date) BETWEEN @StartDate AND @EndDate
)
SELECT Event_id, event_title, event_date
FROM DateTable
WHERE event_date BETWEEN @StartDate AND @EndDate
ORDER BY event_date

, . MAXRECURSION, ( 100)

Answer 3

Here is one method using Oracle (you can switch it to other engines by changing the subquery that generates sequential numbers, see below). The idea behind this query is to create a sequential list of factors (e.g. 0, 1, 2, 3 ..., n) up to the window size (days between dates). This is what the subquery returns. We use this to cross-connect to the event table, and then limit the results to the requested date range.

SELECT t.event_id, t.event_title, t.event_date + t.occurs_every*x.r event_date
FROM tally_table t CROSS JOIN (
 SELECT rownum-1 r FROM DUAL 
        connect by level <= (date '2011-1-20' - date '2011-1-6') + 1
 ) x
WHERE t.event_date + t.occurs_every*x.r <= date '2011-1-20'
ORDER BY t.event_date + t.occurs_every*x.r, t.event_id;

The tally_table in the query is the table specified in your question.