Hi,
I need to limit my data within 2 years, now I have no version of scripts, but I'm not sure which one is better, especially for the first one, I don't quite understand if it's necessary to prepare the 2nd temp table. hope you can guide me, thanks.
V1：
with cte_filterdates as
(
select top 1 [month] maxdate, dateadd(year, -2, cast(convert (varchar(max), [month])+ '01' as date)) mindate
from TableA with(nolock) order by [month] desc
),
cte_formatted as
(
select maxdate, cast(convert( varchar(max),datepart(year, mindate)) +convert(varchar(max), datepart(month, mindate)) as int ) mindate
from cte_filterdates
)
select * from TableA a with(nolock)
join cte_formatted fd on a.[month] <= fd.maxdate and a.[month]> fd.mindate
V2：
with cte_filterdates as
(
select top 1 CREATEDATETIME maxdate, dateadd(year, -2, [month]) mindate
from SGAGIPRDDB1.FUNNEL.DBO.OSUSR_517_EMS_MSA MSA with(nolock) ORDER BY [month]DESC
)
select * from TableA a with(nolock)
join cte_filterdates fd on a.[month] <= fd.maxdate and a.[month]> fd.mindate
