StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSql Automatic Result Issue
text
Body
copied!<p>I have a SQL Server table:</p> <pre><code>CREATE TABLE tblExample ( ID int, Name nvarchar(256), Date datetime, IsAnual bit ) </code></pre> <p>This is a simplified example. </p> <p>Now I scan the next 30 days from <code>GETDATE()</code>. If there is result, I insert information into another table:</p> <pre><code>WHERE DATEDIFF(dd, GETDATE(), Date) <= 30 </code></pre> <p>Up to now there is no problem. But </p> <pre><code>WHERE IsAnual = 1 </code></pre> <p>I must take into account their continuations. How can I do this? </p> <p>Suppose that <code>GETDATE()</code> is <code>2013-10-22</code> and the column contains <code>2013-10-30</code>, there is not problem. What if <code>GETDATE()</code> is <code>2014-10-28</code> and column contains <code>2013-10-30</code> AND IsAnual = 1?</p> <p>Updated:</p> <p>I found solution. I used recursive query.</p> <pre><code>CREATE TABLE tblExample ( ID int IDENTITY(1,1) PRIMARY KEY NOT NULL, Name nvarchar(256), Date datetime, IsAnual bit ) </code></pre> <p>And inserted some rows:</p> <pre><code>INSERT INTO tblExample (Name, Date, IsAnual) VALUES ('A', '2012-11-01', 1), ('B', '2013-11-01', 0), ('C', '2013-01-01', 1) </code></pre> <p>And final section is properly working query:</p> <pre><code>WITH TempTable AS ( SELECT e.ID, e.Name, e.Date, e.IsAnual FROM tblExample AS e UNION ALL SELECT e.ID, e.Name, DATEADD(yy, 1, t.Date), e.IsAnual FROM tblExample AS e INNER JOIN TempTable AS t ON e.ID = t.ID WHERE e.IsAnual = 1 AND DATEDIFF(yy, t.Date, DATEADD(yy, 1, GETDATE())) > 0 ) SELECT * FROM TempTable WHERE DATEDIFF(dd, GETDATE(), Date) BETWEEN 0 AND 30 </code></pre> <p>Results here:</p> <pre><code>14 B 01.11.2013 False 13 A 01.11.2013 True </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