StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The following will generate events based on StartEvent and MEta description with a CTE.</p> <p>Change the values for MaxDate and MaxEvents according to parameters values.</p> <pre><code>declare @MaxDate datetime = convert(datetime,'12/2/2014', 101); declare @MaxEvents integer= 200; ; With -- number generator by power of 2 n2(n) as ( select 1 as n union all select 1), n4(n) as ( select 1 from n2 t1 cross join n2 t2 ), n16(n) as ( select 1 from n4 t1 cross join n4 t2 ), n256(n) as ( select 1 from n16 t1 cross join n16 t2 ), n65k(n) as ( select 1 from n256 t1 cross join n256 t2 ), Numbers (n) as (select row_number() over( order by n) from n65k ), -- Start of events StartEvents as ( SELECT 1 as EventNo, EV.Name, EM.ID, EM.Event_Id, EM.Meta_key, dateAdd(second,EM.meta_value,convert(datetime,'01/01/1970', 101)) as EventDate FROM events AS EV INNER JOIN events_meta EM ON EM.event_id = EV.id AND EM.meta_key = 'repeat_start'), -- Repeating events N times NextEvents AS ( SELECT Numbers.N+1 asEventNo, StartEvents.Name, EM.ID, EM.Event_Id, EM.Meta_key, dateAdd(second,EM.meta_value*Numbers.n,StartEvents.EventDate) as EventDate FROM StartEvents INNER JOIN events_meta EM ON EM.event_id = StartEvents.event_id AND EM.meta_key = 'repeat_interval_'+ ltrim(rtrim(str(StartEvents.ID ))) AND ((1391040000- EM.meta_value ) % EM.meta_value) = 0 cross join Numbers -- not to overflow (dateadd parameter is int type) Where Numbers.N < 3000 ) -- startEvents union nextEvents select EventNo, Name, Meta_key, EventDate FROM ( Select * from StartEvents union all select * from NextEvents ) AllEvents where EventDate < @MaxDate and EventNo < @MaxEvents order by ID ; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload