none
Compare two versions of SQL to limit data within 2 years RRS feed

  • Question

  • 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

    Saturday, February 20, 2021 1:58 PM