SQL for DATEDIFF between rows based on a grouping

I am trying to write a query to get the DATEDIFF between consecutive rows, could be 2 or more based on the value in the first column (grouping).

For example, I have two people who recorded times and I need the sum of the duration between the times for each individual.  I can get the following results (see attached).  I am looking to return Bob > 119 minutes and Steve > 105 minutes

example.xlsx
szadrogaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try this -

You can now add the where clause with the minutes column. The question is not very clear, ..Please let us know what more changes you need.

CREATE TABLE Times
(
	 Name VARCHAR(10)
	,Time VARCHAR(10)
)
GO

INSERT INTO Times VALUES
('Bob','2:01'),
('Bob','4:30'),
('Bob','5:00'),
('Steve','1:15'),
('Steve','3:00')
GO

SELECT Name , DATEDIFF(MINUTE,'1900/01/01 00:00:00',DATEADD(day, DATEDIFF(day, 0, '1900/01/01 00:00:00'), Time)) Minutes
FROM Times 

Open in new window


OUTPUT

/*------------------------

SELECT Name , DATEDIFF(MINUTE,'1900/01/01 00:00:00',DATEADD(day, DATEDIFF(day, 0, '1900/01/01 00:00:00'), Time)) Minutes
FROM Times 
------------------------*/
Name       Minutes
---------- -----------
Bob        121
Bob        270
Bob        300
Steve      75
Steve      180

(5 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Please try this solution

SOLUTION

--
SELECT Name, MAX(Minutes) Minutes
FROM 
(
	SELECT Name , Minutes - LAG(Minutes) OVER (PARTITION BY Name order by DATEADD(day, DATEDIFF(day, 0, '1900/01/01 00:00:00'), Time)) Minutes
	FROM 
	(
		SELECT Name , Time, DATEDIFF(MINUTE,'1900/01/01 00:00:00',DATEADD(day, DATEDIFF(day, 0, '1900/01/01 00:00:00'), Time)) Minutes
		FROM Times 
	)k
)m
GROUP BY Name

Open in new window


OUTPUT

--

/*------------------------
OUTPUT
------------------------*/
Name       Minutes
---------- -----------
Bob        149
Steve      105

(2 row(s) affected)


--

Open in new window

0
szadrogaAuthor Commented:
The only trouble I have is the data lives on SQL Server 2008 and I don't think the LAG() function is available?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Pawan KumarDatabase ExpertCommented:
Updated code for SQL Server 2008

SOLUTION

--
;WITH CTE AS
(
	SELECT Name , Time
	, DATEDIFF(MINUTE,'1900/01/01 00:00:00',DATEADD(day, DATEDIFF(day, 0, '1900/01/01 00:00:00'), Time)) Minutes
	,ROW_NUMBER() OVER (PARTITION BY Name ORDER by DATEADD(day, DATEDIFF(day, 0, '1900/01/01 00:00:00'), Time)) rnk
	FROM Times 
)
,CTE1 AS
(
	SELECT Name , (SELECT TOP 1 Minutes FROM CTE c1 WHERE c1.Name = c.Name AND c1.rnk > c.rnk)-Minutes Minutes
	FROM CTE c
)
SELECT Name,MAX(Minutes) Minutes
FROM CTE1 
GROUP BY Name

Open in new window


OUTPUT

--

/*------------------------
OUTPUT
------------------------*/
Name       Minutes
---------- -----------
Bob        149
Steve      105

(2 row(s) affected)


--

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
Hi,
You can use a loop to scan each record and get comparison to different date like

SELECT DATEDIFF(minute, '2016-10-31 23:59:59.9999999', '2017-05-01 00:00:00.0000000');

Open in new window

0
Pawan KumarDatabase ExpertCommented:
>>You can use a loop to scan each record and get comparison to different date like
Loops are bad for performance. We should always use SET BASED approach. Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.