Select TOP month and year numbers from temp table based on Integer variable

I have a temp table with month and year column in it and both are Integer columns, I am storing all the month and year numbers in that table from when the user joined the company to date… The reason for storing is a different case as I need it for my requirement.

Let’s say the user joined the company on 04/2019, the table contains data from 04/2019 till 12/2020.

The data will be as below in case if the user joined in 04/2019

 @TempTable
    Month Year
      04  2019
      05  2019
      06  2019
      -     -
      -     -
      12  2020

I calculated the number of months when the user joined using the below statement and stored it in a variable.

SET @Age = ROUND(DATEDIFF(DAY, @DOJ, GETDATE())/ 30.436875E, 0)

The problem is if a user joined on 24-07-2020 the age is coming as 5 and when I select Top 5 it is giving me the result from 08/2020 whereas I want the results from the date of joining

In short, my req is, if the user joined 3 months back I need to display 3 months data including the month he joined if the user joined 2 years back than recent 12 months I need if the user joined in the current month then display the only current month

Answer

As I understand your question, you want a maximum of 12 rows from the temp table, starting with the most recent row. If so, you can just do:

select top (12) *
from @temptable
where 100 * year + month <= 100 * year(@doj) + month(@doj)
order by year desc, month desc

Advertisement

Answer