Compare two versions of SQL to limit data within 2 years

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

Answer 1

I'd try asking for help over here.

SQL Server on Q&A | Microsoft Docs

 

 

---

Regards, Dave Patrick ....
Microsoft Certified Professional
Microsoft MVP [Windows Server] Datacenter Management

Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

Answer 2

Hi @Doll,

first of all you need to do proper format your code as a code sample. Here is your code again:

It helps people to understand your concern..

--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;

Here I would like to share brief summary regarding SQL verison

Over the years, many versions and service packs have been released of Microsoft SQL Server Express. Below is a list of the updates and revisions.

Majer Verision    Service packs                                                                                                

2019 None
2017 None
2016 SP1 and SP2
2014 SP1 and SP2
2012 SP1, SP2, and SP3
2008 R2 RTM, R2 SP1, and R2 SP2
2005 SP1, SP2, SP3 and SP4

Choosing the Right Version of SQL Server Express

SQL Server is typically upgraded less often than operating systems, so an older version may be used for a very long time, especially if the application doesn't use the new features.

If starting from scratch, choose the newest version that supports all the operating systems you're targeting.
If you are developing and need to eventually deploy to an actual version of SQL Server, you should use a SQL Server Express version that matches that version or an older version to avoid introducing features that don't exist on your SQL Server.

You can get the complete answer from the official SQL Service Center. Instead typing the complete answer here. 

Thanks,

Alana

Answer 3

Good day Doll916,

1. The name of this forum is: "Forums Issues (not product support)"

It explicitly tell you that this forum is NOT for production support!

This forum is for issues in the forums' system.

You should ask your question in the right forum. In your case, the question is related to Transact SQL. The direct link to the right forum is this:

https://docs.microsoft.com/en-us/answers/topics/sql-server-transact-sql.html

In any case, you should provide more information when you ask your question there. You should remember (or know if this is new) that a query which is best for one database can be the worse option for a second database. SQL Server does not execute the query that you pass to the server but instead it has an internal processing procedure which include parsing the text of the query and buiding multiple Execution Plans. The server Engine select one of these plans as better using multiple parameters including the database structure (for example the existing indexes that you have, the columns types) and including the data that your specific table has! The server uses the statistics he collected about your data in order to select the better Execution Plan. For example it might select to use different Execution Plan for a table with 2 rows than the one he will select to use for a table with 100 millions rows.

Therefore, if you want to discuss what better then you MUST provide

1. queries to create your table(s) including the indexes you have

2. queries to insert some sample data

3. What version  of SQL Server you use

4. The real Execution Plan which you get for each option (an XML file and NOT the image)

This document will help you to find the execution plans:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan

Please ask the question in the right forum and provide the missing information.

Welcome to the forums and have a great day

...

Good day Alana Alison

Welcome to the MSDN forums and thanks for helping people and posting information about SQL Server history.

With that being said I do not find any relation to the question asked. 

In addition...

> You can get the complete answer from the official SQL Service Center. Instead typing the complete answer here.

I have no idea what you speak about! Microsoft do not commit to provide any "complete answer" and the support is provided mainly by the community - people that contribute their time to help others.

I am wondering What do you consider as official "SQL Service Center"?!?

Microsoft team supports in must of the common communities including external private forums. I familiar with many external forums where people from the team that develop the SQL Server, are helping there.

One of the official forums that Microsoft have for SQL Server are these that Dave provided the link for.

> Choosing the Right Version of SQL Server Express

As I understand the question was about better query for specific existing version and not related to selecting the version of the server, even so this information can be useful for such question :-)

> first of all you need to do proper format your code as a code sample.

This is true, but you did not do better. If you want to publish code then you should use the option "insert code block" at the top of the editor and select the right language. This way the code will stay in the format that you post it and will get nice colors as well.

--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;

Again, welcome to the MSDN forums and have a great day

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 4

How to compare software versions using SQL Server?

Add a 0 after the decimal in 5.8 so that it compares 5.08 to 5.12, however it seems like this would require a bit of code.
Simply compare values after the decimal (ie. 12 > 8), however this fails when the version rolls to 6.0.
Use reverse logic and assume that if 5.12 is less than 5.8 to return 'Y'.

Hope this works.

Regards,
Bruce

Answer 5

Hi Hope you are doing well

I think v2 is the best

Regards https://thewing.pk/

Answer 6

How do we automate data comparison between large amounts of data?

Answer 7

How do we automate data comparison between large amounts of data?

Answer 8

This article shows different ways to compare SQL Server data, datatypes and tables using TSQL and some development tools.