SQLTeam.com | Weblogs | Forums

To find out previous month from salary month table for any given date

I work in SQL Server 2008

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

This is doing exactly what you asked.

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?

1 Like

@jeffw8713
<<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?>>

I already mentioned in the question
When @dt is '2021/02/06' no result, the result should be 202105
i.e give me latest yearmonth from the table.

@jeffw8713

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.

regards

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
1 Like

@jeffw8713
Nice suggestion.

Luckily I do have a column serno in the table .
I would treat your final answer as solved.
But, sometimes I don't seem to find the solution button.

No worries - glad the suggestion works.