none
Compare two versions of SQL to limit data within 2 years RRS feed

  • 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

    Saturday, February 20, 2021 1:58 PM

All replies

  • 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.

    Tuesday, February 23, 2021 4:28 PM
  • 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

    Friday, February 26, 2021 8:12 AM
  • 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


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

    Friday, February 26, 2021 4:09 PM