SQL: Comparing multiple dates

Question

I got a table with multiple columns that contain DateTime namely gendate1 up to gendate8. In my stored procedure, the user will have to input a date (@date_start). Now I have to compare the inputted date to the 8 dates which will filter all the data. The problem is that I have to get the two closest dates (out of eight dates) to the given date. Sorry I'm just a beginner in sql.

In response to sir Akina

I don't have the actual code yet because it confuses me of what to do first.

Should I make a temporary table, loop the result of the query and filter each of the result (but how to get the 2 closest dates out of eight dates) then insert it to the temporary table?
or should I put all the condition under the where clause?

Example

 gendate1 = '1/6/2019'
,gendate2 = '4/6/2019'
,gendate3 = '2/6/2019'
,gendate4 = '3/6/2019'
,gendate5 = '5/6/2019'
,gendate6 = '7/6/2019'
,gendate7 = '8/6/2019'
,gendate8 = '10/6/2019'

@date_start = '1/30/2019'

How to get the gendate1 and gendate3? (they are the closest to @date_start)

Akina · Accepted Answer · 2019-05-24 06:31:02Z

with 

cte1 as (
    select id, 1 num, gendate1 gendate from test union all
    select id, 2 num, gendate2 gendate from test union all
    select id, 3 num, gendate3 gendate from test union all
    select id, 4 num, gendate4 gendate from test union all
    select id, 5 num, gendate5 gendate from test union all
    select id, 6 num, gendate6 gendate from test union all
    select id, 7 num, gendate7 gendate from test union all
    select id, 8 num, gendate8 gendate from test 
),

cte2 as (
    select id, num, gendate, row_number() over (partition by id order by gendate desc) rn
    from cte1
    where gendate <= '1/30/2019'
union all
    select id, num, gendate, row_number() over (partition by id order by gendate asc) rn
    from cte1
    where gendate >= '1/30/2019'
)

select id, num, gendate
from cte2
where rn = 1
order by id, gendate

fiddle

If there are duplicated dates in gendateX fields within the row, the num returned will be random. To avoid use RANK() instead of ROW_NUMBER() and use

select id, MAX(num) num, gendate -- or MIN()
from cte2
where rnk = 1
group by id, gendate
order by id, gendate

fiddle

Aaron Bertrand · Accepted Answer · 2019-05-24 15:48:26Z

A slightly different and much more efficient approach (single CIX scan instead of SIXTEEN CIX scans). Assuming this table:

CREATE TABLE dbo.tablename
(
  id int PRIMARY KEY,
  gendate1 datetime,  gendate2 datetime,  gendate3 datetime,  gendate4 datetime,
  gendate5 datetime,  gendate6 datetime,  gendate7 datetime,  gendate8 datetime
);

Then we can use this query to unpivot:

DECLARE @date_start datetime = '20190130';

;WITH x AS
(
  SELECT tp.id, tp.d FROM dbo.tablename AS t
  UNPIVOT 
  (
    d FOR ds IN 
    (
      t.gendate1,t.gendate2,t.gendate3,t.gendate4,
      t.gendate5,t.gendate6,t.gendate7,t.gendate8
    )
  ) AS tp
)
SELECT id, 
  next_date = MIN(CASE WHEN d > @date_start THEN d END),
  prev_date = MAX(CASE WHEN d < @date_start THEN d END)
FROM x
GROUP BY id;

If you want a tie when @date_start is a direct hit, pick one to change to >= or <= (just don't do that to both).

Results with a single id given the sample data in the question:

Plan here is much nicer:

Stack Exchange Network

SQL: Comparing multiple dates

2 Answers 2

Your Answer

Hot Network Questions

SQL: Comparing multiple dates

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions