Compare two versions of SQL to limit data within 2 years

Question

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

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

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

Phil Parkin SSC Guru Points: 246974 More actions · Answer 1

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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel