Calculate Moving Averages using T-SQL in SQL Server (2020-02-04)

 SQLServerCentral Newsletter for February 4, 2020 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Calculate Moving Averages using T-SQL in SQL Server]( - [Static data in SQL Change Automation SSMS plugin]( - [Building SQL Server Indexes in Ascending vs Descending Order]( - [From the SQL Server Central Blogs - Continuous integration and delivery (CI/CD) in Azure Data Factory using DevOps and GitHub - Part 2]( - [From the SQL Server Central Blogs - ANSI_WARNINGS = OFF can break update statements with some SQL features]( Question of the Day - [Query Optimization Level]( [Redgate SQL Source Control]( The Voice of the DBA  How to Get Started with Version Control for Databases Today we have a guest editorial from Kendra Little as Steve is away on his sabbatical. In the [2020 State of Database DevOps report]( we see an interesting combination of trends: - Frequent database deployments are increasing: 49% of respondents now report they deploy database changes to production weekly or more frequently - Use of version control for the database has increased only modestly, with 56% of respondents overall reporting that database code is in a version control system Digging into the data, we see that not all frequent deployers are using version control. This means that either code changes are being frequently made directly to production databases without the changes being scripted out, or that scripted changes are being stored in file shares or other mechanisms which are less reliable and flexible than a true version control system. Combining this data with responses related to code quality, we also see that frequent deployers who report they use version control for database code reported lower percentages of defect rates as compared to frequent deployers who do not use version control for database code. This finding is not surprising to me personally, as in my experience the use of version control provides not only a mechanism to reliably share and review code, but it also provides a strong foundation for testing and validating the quality of changes. So why aren’t more teams using version control for database code, and how can they get started? We see in the survey data that the two biggest perceived obstacles to implementing DevOps are concerns about the disruption to existing workflows/business and a lack of appropriate skills in the team. Perception is critical here, and I believe that concerns in these two areas are a big reason why people are slow to adopt version control for databases: they worry that people will be slow to learn how to do it, and that this will cause a significant stall for the team and disrupt business workflows and deliverables while things get sorted out. The easiest way to overcome these obstacles is to start small and work in incremental changes. This may mean doing things imperfectly to begin with: perhaps the first move your team makes to implement version control for databases is to capture schema nightly and store that schema in version control, while still using your established process for deploying changes. While this doesn’t give you all the benefits of using version control as part of the development process, it gives you the ability to see when schema changes day by day, and it gives your team a way to begin learning how version control works and a way that they can look at trends over time. The important thing isn’t to implement the perfect solution right away: the important thing is to get started and to begin to incrementally improve.  Kendra Little [Join the debate, and respond to today's editorial on the forums]( [Redgate SQL Change Automation](   Featured Contents [Calculate Moving Averages using T-SQL in SQL Server]( Archana from SQLServerCentral Introduction In Financial Data, analyzing the Moving Average (MA) is a very common practice. The direction of the moving average conveys important information about prices, whether that average is simple or exponential. A rising moving average shows that prices are generally increasing. A falling moving average indicates that prices, on average, are falling. This article […] [Static data in SQL Change Automation SSMS plugin]( Additional Articles from Redgate Kendra Little sat down with the SQL Change Automation Dev Team to get some insight into how they build the product with the customers in mind. SQL Change Automation 4.1 included adding support for the tracking of static data tables to the SSMS extension. This was a direct result of the communication and work the team have carried out with their customer base. Read Kendra’s blog to find out more about the release and the work that goes into delivering these updates. [Building SQL Server Indexes in Ascending vs Descending Order]( Additional Articles from MSSQLTips.com In this tip we look at the impact of building SQL Server indexes in ascending versus descending order and the impact. From the SQL Server Central Blogs - [Continuous integration and delivery (CI/CD) in Azure Data Factory using DevOps and GitHub - Part 2]( Rayis Imayev from Data Adventures (2020-Jan-28) This blog post is a followup to my previous post about DevOps (CI/CD) for Azure Data Factory - Continuous integration and delivery (CI/CD) in Azure Data Factory using DevOps and... From the SQL Server Central Blogs - [ANSI_WARNINGS = OFF can break update statements with some SQL features]( Will Assaf from SQL Tact Working on a SQL 2017 database where an external, non-Microsoft application has direct access to read/write data via the ODBC Driver 17 for SQL Server. They suddenly began to experience...   Question of the Day Today's question (by Grant Fritchey):  Query Optimization Level Where within the execution plan can you find what level of optimization was performed on the query in question? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Kathi Kellenberger) Calculate the Days Between First and Last Orders Your SQL Server 2017 database has a Sales table that contains CustomerID, OrderID, and OrderDate columns. You would like to return a list of CustomerIDs along with the difference in days between the very first order the customer placed and the most recent order. Which query will give you that answer? Query #1 SELECT CustomerID, DATEDIFF(DAY,LAG(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID), OrderDate) AS OrderSpanDays FROM Sales; Query #2 SELECT CustomerID, DATEDIFF(DAY,OrderDate, LEAD(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS OrderSpanDays FROM Sales; Query #3 SELECT DISTINCT CustomerID, DATEDIFF(DAY,FIRST_VALUE(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), LAST_VALUE(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) AS OrderSpanDays FROM Sales; Query #4 SELECT DISTINCT CustomerID, DATEDIFF(DAY,FIRST_VALUE(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID), LAST_VALUE(OrderDate) OVER(PARTITION BY CustomerID ORDER BY OrderID)) AS OrderSpanDays FROM Sales; Answer: Query #3 Explanation: The window function LAST_VALUE is used to return an expression from the last row of a partition, and FIRST_VALUE is used to return an expression from the first row. These functions can be used to solve the problem, however, the partition must be correctly defined using framing. These functions were added to SQL Server in version 2012. Some window functions, including LAST_VALUE and FIRST_VALUE, use framing to define the partition more granularly than the PARTITION BY and ORDER BY options. The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so the "last" row in the partition is the same as the current row. LAST_VALUE will return the current row when the frame is left out, so Query #4 is not correct. The frame which returns the last row you expect is ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. Query #3 uses the correct frames and returns the right answer. It is also using DISTINCT here, because one row per order placed will be returned otherwise. LAG (Query #1) returns the row before the current row and LEAD (Query #2) returns the following row. These are useful functions, but they are not the right ones for this scenario. Ref: [Introduction to T-SQL Window Functions]( [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2017 - Administration [Backup One or Multiple Databases At Once (Script)]( - Hi Everyone, I wrote the below to assist in multiple database backups. Thought you may find it helpful. /* - Script to backup one or multiple databases in MS SQL Server, at once. - Written by: Gray Meiring - Updated: 2020-02-20 - Backup file name format: DatabaseName_yyyymmdd_hhmm.bak Instructions: 1. Add database names (without []) with […] SQL Server 2017 - Development [eMail - Analytical Platform System (Parallel Data Warehouse)]( - hi Does Analytical Platform System (Parallel Data Warehouse) support eMail feature, like in traditional SQL Server? thanks [Conversion Error When Using CASE Statement]( - Hello All - I feel like a dope here, but I need some help. I have a case statement which is throwing a conversion error and i'm trying to figure this out. All fields involved in the CASE are all numeric (int), I check them with the ISNUMERIC() function. However, when run the following statement […] SQL Server 2016 - Administration [SQL SERVER Installation]( - hi there, I have a SQL SERVER 2016 installed on windows 10. I am trying to uninstall it or installing SQL SERVER 2017, In either ways it is giving me an error saying computer need to be restarted. Once I restart, it comes again in the next try. Can anyone help please.  Thanks, [Uninstall SQL Server 2014]( - Hi, i have recently migrated my server to SQL Server 2014 to SQL Server 2016 by doing inplace upgrade.now my question is uninstall the SQL Server 2014 .Can i directly proceed with uninstall of SQL Server 2014 on Control Panel by Microsoft Sql Server (64 Bit).is any problem happened by doing this.  SQL Server 2016 - Development and T-SQL [T-SQL multiple COUNT help]( - Hello, New to the forum and an SQL novice. I have a table similar to the below and need to count, firstly the number of each occurrence of 'acc' and in the same result set, count the number of 'token' per 'acc'. Create the test table with the following: DROP TABLE IF EXISTS dbo.Test1; CREATE […] Administration - SQL Server 2014 [Sysadmin at DB Level]( - Hello. I have been reading that SYSADMIN cannot be granted at DBLEVEL. However, when I do a Select * from SYS.SYSLOGINS where SYSADMIN = 1 it returns a Database Name in the DBNAME, either a User DB or Master. So what is this telling me - if anything ? Regards Steve O. SQL Server 2019 - Administration [Index]( - I have 1000 Indexes on Primary I have database MDF data file in 2 drives on E Drive and F drive. I want to know which Indexes in E drive and F drive. How do I get that info? SQL Server 2019 - Development [stumped by a LEFT JOIN outcome]( - I want only records in Table A that have disabled= 0 LEFT JOINed to Table B. Below query returns a record from table A that I don't even want considered when joining to table B. It returns a row even though it's Disabled Flag = 1.  insert into #SOURCETABLE (ID , DisableFlag) values (3,1) […] [Feeling My Way Into SQL]( - I am very new to SQL. I have completed a Udemy introduction course and have some exposure via MS Access to SQL but not a lot. I am really interested in any directions to a good learning resource as I think that is missing in my understanding somewhere. Anyway, the issue I am stuck with […] [script]( - I need some help  I have 1000 Indexes on Primary I have database MDF data file in 2 drives on E Drive and F drive. I want to know which Indexes in E drive and F drive. How do I get that info? SQL Server 2008 Performance Tuning [How to improve query with select top n rows ?]( - I notice some slowness on a select statement whenever it's selecting top n rows. Is there anyway to improve it? Query seems to execute faster if I just issue select * instead of select top n rows. SSDT [SSDT 2017 / SQL 2019 SSRS weird screen refresh/flicker]( - @@Version => Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)  Oct 28 2019 19:56:59  Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 (Build 18363: ) I've been tinkering with SSRS for several years and lots of versions (2005-2019), and I've never seen this before. I create a connection […] Integration Services [BIML and sensitive data.]( - Hi All, Does any of you faced with the case when one of the data source or destination do not have windows authentication access? If yes, is there any way to keep sensitive connection information like username and password OUTSIDE of the BIML scripts or metadata tables? Anything that is NOT about SQL! [WS_ftp concern]( - Ipswitch just stopped supporting WS_Ftp pro except through their user community.  Never mind we paid for technical support and product upgrades.   And yes, I emailed them a nastygram about this. Here is my question.  We want to schedule an ipswitch ftp script to periodically check a folder for files or subfolders.  When they are present […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -