Databases
StackOverflow2013
Postsdbo
POSQL query to find single, sorted record
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL query to find single, sorted record
    primarykey
    data
    text
    <p>Here is the problem I am trying to solve. I have a table where I store a StartExclusionDate and EndExclusionDate and set an ApplyDate for my object (called Exclusion). There can be many ExtId entries in the table.</p> <p>I want to get the ApplyDate from the last continuous record where a date passed to the query is included between the StartExclusionDate and EndExclusionDate.</p> <p>So here is the table I created in SQL 2005:</p> <pre><code>Id int PK ExtId int FK StartExclusionDate datetime null EndExclusionDate datetime null ApplyDate not null </code></pre> <p>And here is an example dataset:</p> <pre><code> ID | ExtID | StartDate | EndDate | ApplyDate -------------------------------------------------------- 1 101 11/15/2005 3/15/2010 11/15/2005 2 101 12/30/2005 3 101 1/1/2000 1/1/2000 1/1/2006 4 101 4/1/2008 4/1/2008 5 101 8/1/2010 6/1/2008 6 101 11/1/2006 12/31/2010 11/1/2009 </code></pre> <p>I would be passing 2 parameters, @ExtId and @dtDate. The record I want to retrieve based on the following conditions: <code>@ExtId = 101, @dtDate = '6/15/2008'</code> would be <strong>ID = 4</strong></p> <p>The logic is this:</p> <ul><li>Get all records for ExtId = 101 and where ApplyDate &lt;= '6/15/2008', sort the ApplyDate DESC</li> <li>If the first record's Start and End Date are between '6/15/2008', find the record where the Exclusion was originally set by checking the next records</li></ul> <p>I wanted to find a way to do this without a CURSOR, is it possible to do with a query or CTE?</p> <p><strong>EDIT:</strong> Here it is done with a cursor</p> <pre><code>IF OBJECT_ID('TempDB..#t_edr','U') IS NOT NULL DROP TABLE #t_edr CREATE TABLE #t_edr ( Id INT NOT NULL, ExtId INT NOT NULL, StartDate DATETIME NULL, EndDate DATETIME NULL, ApplyDate DATETIME NOT NULL ) DECLARE @ExtID INT, @dtDate DATETIME, @dtReturn DATETIME SELECT @ExtID = 101, @dtDate = '6/15/2008' SET NOCOUNT ON INSERT INTO #t_edr VALUES (1, 101, '11/15/2005', '3/15/2010', '11/15/2005') INSERT INTO #t_edr VALUES (2, 101, NULL, NULL, '12/30/2005') INSERT INTO #t_edr VALUES (3, 101, '1/1/2000', '1/1/2000', '1/1/2006') INSERT INTO #t_edr VALUES (4, 101, '4/1/2008', NULL, '4/1/2008') INSERT INTO #t_edr VALUES (5, 101, NULL, '8/1/2010', '6/1/2008') INSERT INTO #t_edr VALUES (6, 101, '11/1/2006', '12/31/2010', '6/1/2009') SET NOCOUNT OFF IF EXISTS ( SELECT EDR.ID FROM #t_edr EDR INNER JOIN (SELECT TOP 1 ID FROM #t_edr WHERE ExtId = @ExtID AND ApplyDate &lt;= @dtDate ORDER BY ApplyDate DESC) LatestEDR ON EDR.ID = LatestEDR.ID WHERE ExtId = @ExtID AND ApplyDate &lt;= @dtDate AND (StartDate IS NULL OR StartDate &lt;= @dtDate) AND (EndDate IS NULL OR EndDate &gt;= @dtDate) ) BEGIN DECLARE @ID INT, @StartEDR DATETIME, @EndEDR DATETIME, @ApplyDate DATETIME, @CurrentApplyDate DATETIME, @Continue BIT DECLARE c CURSOR LOCAL FAST_FORWARD FOR SELECT Id, StartDate, EndDate, ApplyDate FROM #t_edr WHERE ExtId = @ExtID AND ApplyDate &lt;= @dtDate ORDER BY ApplyDate DESC OPEN c FETCH NEXT FROM c INTO @id, @startedr, @endedr, @ApplyDate IF @@FETCH_STATUS &lt;&gt; 0 SET @Continue = 0 ELSE SET @Continue = 1 SET @CurrentApplyDate = @ApplyDate WHILE @Continue = 1 BEGIN IF @StartEDR &gt;= @dtDate OR @EndEDR &lt;= @dtDate SET @Continue = 0 IF @Continue = 1 BEGIN SET @CurrentApplyDate = @ApplyDate FETCH NEXT FROM c INTO @id, @startedr, @endedr, @ApplyDate IF @@FETCH_STATUS &lt;&gt; 0 SET @Continue = 0 END END CLOSE c DEALLOCATE c SELECT @CurrentApplyDate END ELSE PRINT 'NOT EXCLUDED' DROP TABLE #t_edr </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSteve Wright
    1. USSteve Wright
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL query to find single, sorted record
      1. UStekBlues
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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