Post reply

Compare two versions of SQL to limit data within 2 years

  • February 20, 2021 at 2:11 pm

    #3847726

    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

  • February 21, 2021 at 11:28 am

    #3848025

    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