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