How to calculate difference in Minutes/Seconds between two date in the same column

Hi all

I have a table that looks like this:

TicketID              SetStatus                                      SetDate                                       Count
100601BG15      Acknowledged                      2010-06-01 14:02:45.330                   1
100601BG15      Waiting on Business      2010-06-01 14:07:28.820                   0
100601BG15      Pending                              2010-06-01 14:07:40.597                   1
100601BG15      Pending                              2010-06-01 14:34:43.737                   1
100601BG15      Pending                           2010-06-03 18:29:47.120                   1      
100601BG15      Post Dated                      2010-06-04 09:00:52.587                   0
100601BG15      Post Dated                      2010-06-07 12:42:37.960                   0
100601BG15      Fulfilled                              2010-06-07 14:38:29.677                   1


I also have a function  CalcMinutesSLA(Date1,Date2) that will calculate the difference between two dates excluding weekends and holidays.

Basically what i need is to calculate the difference in minutes between each date and the one before it and only the ones that have count set to 1.

So from the example above i would need the difference between Fulfilled and Post Date then from Post Dated to Pending and so on.
LVL 7
urir10Asked:
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.

Rajkumar GsSoftware EngineerCommented:
Please see the attached script with your sample data and final query

Raj
create table #table
(
	TicketID              varchar(20),
	SetStatus             varchar(20), 
	SetDate               datetime,                        
	Count				int
)

insert into #table
select '100601BG15',      'Acknowledged',                      '2010-06-01 14:02:45.330',                   1
union all
select '100601BG15',      'Waiting on Business',      '2010-06-01 14:07:28.820',                   0 
union all
select '100601BG15',      'Pending',                              '2010-06-01 14:07:40.597',                   1
union all
select '100601BG15',      'Pending',                              '2010-06-01 14:34:43.737',                   1
union all
select '100601BG15',      'Pending',                           '2010-06-03 18:29:47.120',                   1      
union all
select '100601BG15',      'Post Dated',                      '2010-06-04 09:00:52.587',                   0
union all
select '100601BG15',      'Post Dated',                      '2010-06-07 12:42:37.960',                   0 
union all
select '100601BG15',      'Fulfilled',                              '2010-06-07 14:38:29.677',                   1




with cte as
(
	select *, row_number() over (order by SetDate) row_no 
	from #table
) 
select *, datediff(mi, a.SetDate, b.SetDate) as DiffMinute from cte a
	inner join cte b
		on a.row_no = b.row_no - 1

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
Rajkumar GsSoftware EngineerCommented:
Oh! In my query, give the alias 'a'. Here is the modified query.

Raj
with cte as
(
	select *, row_number() over (order by SetDate) row_no 
	from #table
) 
select a.*, datediff(mi, a.SetDate, b.SetDate) as DiffMinute from cte a
	inner join cte b
		on a.row_no = b.row_no - 1

Open in new window

0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The "count" condition is not accounted for, and it is far from being clear what you mean. In the above CTE, would the added condition be
   a.count = 1 and b.count = 0 or a.count = 0 and b.count = 1
or
  a.count = 1 or b.count = 1
?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

urir10Author Commented:
The count condition only there to say whether the status assosiated to the row should be counted since when the ticket is set to 'Waiting on business' for example it should not count the time towards the SLA value.
0
LowfatspreadCommented:
;with cte as
  (select ticketid,setstatus,setdate
     .row_number() over (partition by (ticketid) order by setdate asc) as rn
   from yourtable
   where [count]=1
  )
select a.ticketid,dbo.calcminutessla(a.setdate,b.setdate)
  from cte as a
  inner join cte as b
    on a.ticketid=b.ticketid
   and a.rn=b.rn-1
 order by 1
0
Rajkumar GsSoftware EngineerCommented:
Check this new query. I verified and seems to be fine.

Raj
select b.*, datediff(mi, PreviousDate, Setdate) as DiffInMinutes
from
(
	select a.*, (Select max(SetDate) from #table where SetDate < a.SetDate and count = 1) as PreviousDate
	from #table a
) b

Open in new window

0
urir10Author Commented:
Thanks alot guys! i used both of those solution to get what i needed.
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.