0

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.

  1. 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?
  2. 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)

0
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

2

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:

enter image description here

Plan here is much nicer:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.