Vehicle Number - This column am using in where clause to fetch data.
Hold type - This is in select clause.
start date - define as timestamp.
end date - This is defined timestamp with null allow, I am fetching this column. if null, it should populate as '2099-12-31' in output.
here vehicle number and start date is unique key.
If the vehicle has 2 Hold type of the same Hold type(value), only 1 record will be returned. This will be the Hold with the latest end date, or no specified end date. If both Holds expire at the same time, the Hold applied first will be returned.
Written below sql, ignore syntax if any as I have re-written based on above example for understanding purpose. But I have executed query so logic wise it is right.
this query working based on difference of start date and end date, so working fine when two same record(hold type) and one of them having end date null.
But it gives wrong output( for same hold type) if start date for one record is 2015-01-01 and end date is 2020-05-01
second record having Start date 2017-01-01 and end date 2020-04-01
Here I am getting record having end date 2020-05-01 since difference between start date and date is more here. the expected record to be picked having end date '2020-04-01 (it has latest end date as per req).
select hold type, end date_tem
coalesce(DATE(End_date ),'2099-12-31') as END_DATE_temp ,
coalesce(DATE(End_date),'2099-12-31')-date(start_date) as DAYS_DIFF
-- (to find longest hold)
,row_number() over(PARTITION BY Table_name ORDER BY (coalesce(DATE(end_date),'2099-12-31')-date(start_date)) desc) as RNUM
vehicle = 'abcd'
and (end_date >= current_timestamp or end_date is null)
Coded for you this time. Do it yourself in future.