declare @salarydates table (yearmonth int, dt1 date, dt2 date)
insert @salarydates select 202103 , '2021/03/01','2021/03/31'
insert @salarydates select 202104 , '2021/04/01','2021/04/30'
insert @salarydates select 202105 , '2021/05/01','2021/05/31'
declare @dt date set @dt = '2021/05/06'
select top 1
sd.yearmonth
from @salarydates sd
where sd.dt1 <
( select dt1
from @salarydates
where @dt between dt1 and dt2
)
order by yearmonth desc
When @dt is '2021/05/06' the result is 202104 --correct result
When @dt is '2021/05/31' the result is 202104 --correct result
When @dt is '2021/04/02' the result is 202103 --correct result
When @dt is '2021/06/01' no result, the result should be 202105
When @dt is '2021/12/12' no result, the result should be 202105
When @dt is '2021/02/06' no result, the result should be 202105
Select dt1
From @salarydates
Where @dt between dt1 and dt2
When @dt is greater than dt2 - no rows will be returned. Since no rows are returned - sd.dt1 cannot be less than a null so no rows will match.
This can be resolved using coalesce:
select top 1
sd.yearmonth
from @salarydates sd
where sd.dt1 < coalesce(
( select dt1
from @salarydates
where @dt between dt1 and dt2
), '9999-01-01')
order by yearmonth desc
But - this will also fail for @dt = '2021/03/05' because there isn't a row prior to that date. What should happen if that is the case?
in my application a form has two date text boxes defauling to current date.
When the user click minus radio button first time the text boxes are filled with the
latest row from the salarydate table, you press again one month further back in the table, and so on untill it reaches the first row of the table. you press one more time no rows exist ,
in that case (first i thought latest month) it would be best to give me current date.
You are not getting the latest row - you are getting the row prior to the latest row. Since there are no rows prior to the first row - how could you get something?
Based on your requirement - I would just assign a row number to each row. When the user clicks the button add/subtract from the row number. If you capture the row number for the current row - you now have your max row number and if the user decrements the value to 0 - use that row number to reset back to the latest row.
Select sd.yearmonth
, row_num = row_number() over(Order By sd.dt1 desc)
From @salarydates sd