Calculate min/max timestamp intervals on a particular record value 1

[larrydavid]

Hello, I think I'm getting close to a possible solution, but I'm a bit stuck. Here is a recordset sample from my current query:

TxNumber  TimeStamp                User        Supv
X1234567  2011-02-10 06:05:23.283  Jim Beam    BW
X1234567  2011-02-10 06:05:29.440  Jim Beam    BW
X1234568  2011-02-10 06:07:29.444  Jim Beam    BW
X1234568  2011-02-10 06:07:34.123  Jim Beam    BW

In some cases it will be the next subsequent tx record, in some cases it may appear further down in the recordset order.

Is it possible to calculate the Min and Max TimeStamp intervals for all TxNumbers for a particular User? So in the example above, I would want to calculate the intervals for Jim Beam and group them up into a Min and Max column. Here is the query:

SELECT   r.TxNumber, 
		 r.TimeStamp, 
		 u.FirstName + ' ' + u.LastName AS User,
		 CASE WHEN s.Name IS NULL THEN 'No Supervisor' ELSE s.Name END AS [Supv Name]
FROM     Test1 o INNER JOIN
		 RealTimeProd r ON o.CLAIMNO + o.CLAIMGEN = r.ClaimNo INNER JOIN
		 [User] u ON r.UserID = u.UserID LEFT OUTER JOIN
		 Supervisor s ON u.fk_Supervisor_Id = s.Supervisor_Id
ORDER BY TimeStamp ASC

Any help greatly appreciated.

Thanks,
Larry

 

[markros]

;with cte as (your query above without order by)
select TxNumber, min(TimeStamp) as MinTime, max(TimeStamp) as MaxTime, [User], [Supv Name]
from cte 
group by TxNumber, [User],[Supv Name]

PluralSight Learning Library

 

[larrydavid]

Wow, so close Mark. Now I just need to calc the interval between the Min and Max TimeStamp and group it up for each user.

The result of this will be to show the Min and Max turnaround time for each user for all transactions grouped.

Thanks so much,
Larry

 

[markros]

;with cte as (your query above without order by)
select TxNumber, datediff(day,min(TimeStamp),max(TimeStamp)) as TimeDifferenceInDays, [User], [Supv Name]
from cte 
group by TxNumber, [User],[Supv Name]

The same query with the minor change. You can use any interval here you need.

PluralSight Learning Library

 

[larrydavid]

Thank you Mark. Next I'm working on calcing the AVGs, but I think I've got that. Thanks so much for your examples. Larry