Skip to content
Advertisement

Select all rows between dates

I have a #tmp table that looks like the following. It has a total of three lines.

title receipt_date decision_date
“It wasn’t as bad as I thought it would be” 2017-06-12 15:07:10.893 2017-06-23 09:37:31.667
“It wasn’t as bad as I thought it would be” 2017-07-11 10:35:24.337 2018-06-25 05:54:41.133
“It wasn’t as bad as I thought it would be” 2017-09-25 14:06:18.670 2017-11-21 05:13:08.563

The code is supposed to iterate through each of the three lines, and (based on the current iterated line) select all rows from the entire table where receive_date is between receive_date and decision_date on any other line.

NOTE: I specifically used greater-than and less-than (versus greater-than-or-equal) so that the row would not self select.

NOTE: 2017-09-25 14:06:18.670 is later than 2017-07-11 10:35:24.337 but earlier than 2018-06-25 05:54:41.133, so that line should return in the select. But I get nothing from the select.

I’m running the code in SSMS

CODE

SET @Cur2 = CURSOR FOR
  SELECT title,receipt_date,decision_date  
From #tmpTable
GROUP BY title,receipt_date,decision_date 
ORDER BY receipt_date

OPEN @Cur2
FETCH NEXT FROM @Cur2 INTO @TITLE,@RECEIPT_DATE,@DECISION_DATE;
WHILE @@FETCH_STATUS = 0
BEGIN

  select *  
  from #tmpTable
  where receipt_date > @RECEIPT_DATE 
  AND   receipt_date < @DECISION_DATE

  FETCH NEXT FROM @Cur2 INTO @RECEIPT_DATE,@DECISION_DATE;
END;

Advertisement

Answer

It’s not so easy to decipher your question. You should have posted a proper example! But from what I get, it sounds like you could simply use EXISTS and a correlated subquery.

SELECT *
       FROM #tmp t1
       WHERE EXISTS (SELECT *
                            FROM #tmp t2
                            WHERE t2.receipt_date < t1.receipt_date
                                  AND t2.decision_date >= t1.receipt_date);

db<>fiddle

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement