Compare two versions of SQL to limit data within 2 years

  • 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

  • It helps people if you format your code as a code sample. Here is your code again:

    --V1:
    WITH cte_filterdates
    AS
    (SELECT TOP 1
    maxdate = month
    , mindate = DATEADD(YEAR, -2, CAST(CONVERT(VARCHAR(MAX), month) + '01' AS DATE))
    FROM TableA WITH (NOLOCK)
    ORDER BY month DESC)
    , cte_formatted
    AS
    (SELECT
    maxdate
    , mindate = CAST(CONVERT(VARCHAR(MAX), DATEPART(YEAR, cte_filterdates.mindate))
    + CONVERT(VARCHAR(MAX), DATEPART(MONTH, cte_filterdates.mindate)) AS INT)
    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
    maxdate = CREATEDATETIME
    , mindate = DATEADD(YEAR, -2, month)
    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;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply