Asked by:
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
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.
- Proposed as answer by KHURRAM RAHIM Monday, March 1, 2021 11:54 AM
- Edited by Qinghui MengMicrosoft contingent staff, Administrator Monday, June 6, 2022 8:01 AM remove whitespace link
-
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
- Proposed as answer by Carpet Cleaner Vacuum Wednesday, February 23, 2022 1:20 AM
- Edited by Qinghui MengMicrosoft contingent staff, Administrator Tuesday, June 7, 2022 1:37 AM update for accessibility
-
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-planPlease 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]- Proposed as answer by AllWeatherKC Monday, February 14, 2022 8:03 PM
-
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- Proposed as answer by 90S Outfits Wednesday, June 1, 2022 1:39 AM
-
-
-
-