Compare two versions of SQL to limit data within 2 years

• doll916

  SSC Enthusiast

  Points: 112

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  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;

  ---