StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODisplay next event date
text
Body
copied!<p><strong>The UI design</strong> for storing event and event meta data is</p> <p><img src="https://i.stack.imgur.com/1vejB.png" alt="enter image description here"></p> <p><strong>SQL TABLE DESIGN</strong> is</p> <pre><code>CREATE TABLE [dbo].[EVENTS] ([ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](255) NOT NULL) </code></pre> <p>and</p> <pre><code>CREATE TABLE [dbo].[EVENTS_META]( [ID] [int] IDENTITY(1,1) NOT NULL, [event_id] [int] NOT NULL, [meta_key] [varchar](255) NOT NULL, [meta_value] [bigint] NOT NULL) </code></pre> <p>The Events data is <img src="https://i.stack.imgur.com/gl8Hh.png" alt="Event"></p> <p>Event Metadata is <img src="https://i.stack.imgur.com/qp3ef.png" alt="Events_Meta table"></p> <p>I Followed <a href="https://stackoverflow.com/questions/10545869/repeating-calendar-events-and-some-final-maths">Repeating calendar events and some final maths</a> and I wrote the below query</p> <p><strong>LIST ALL THE EVENT DATES BEFORE THE GIVEN END DATE</strong></p> <pre><code>SELECT EV.* FROM events AS EV RIGHT JOIN events_meta AS EM1 ON EM1.event_id = EV.id RIGHT JOIN events_meta AS EM2 ON EM2.meta_key = 'repeat_interval_'+ CAST(EM1.id as Varchar(100)) WHERE EM1.meta_key = 'repeat_start' AND ((1391040000 - EM1.meta_value ) % EM2.meta_value) = 0 </code></pre> <p>I am not getting anything. I want to display all dates after repeat_start with the given interval. </p> <p>Example here 1st event starts on (3rd Jan 2014, 10 A.M) unixtimestamp =1388743200 and continues every friday(7 days), we also schedule the first event on starts saturday(Jan04, 2014)1388858400 and continues once in every 7 days(saturday) </p> <p>It can be once in a month/daily/etc. So we have the <code>interval</code> defined as seconds.</p> <p>If i give some input like 30 Jan 2014 , <code>i.e =1391040000</code> (30 Jan 2014 00:00:00)</p> <p><strong>Expected Result</strong></p> <p>Billa Visit, 3 Jan 2014 10 A.M</p> <p>Billa Visit, 4 Jan 2014 10 A.M</p> <p>Billa Visit, 10 Jan 2014 10 A.M</p> <p>Billa Visit, 11 Jan 2014 10 A.M</p> <p>Billa Visit, 17 Jan 2014 10 A.M</p> <p>Billa Visit, 18 Jan 2014 10 A.M</p> <p>Billa Visit, 24 Jan 2014 10 A.M </p> <p>Billa Visit, 25 Jan 2014 10 A.M</p> <p><strong><a href="http://www.sqlfiddle.com/#!3/29ced/1" rel="nofollow noreferrer">SQL FIDDLE LINK</a></strong></p>

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