|
|
|
|
|
|
|
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 |
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 […] |
| |
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com |