Finding duplicate value and taking difference with other values with Excel/SQL

0
votes

I have got a very strange problem. I have lot of data with columns of dates, ID column etc. I want to take the difference of time of 2 dates (where dates are reported as 7/16/2017 18:42) in both columns. At first instance, it looks easy to take simple difference but problem here is ID column. In ID column, there are lot of IDs where IDs are duplicate also. So below are the conditions:

  1. Take difference of date and time where first row of ID. Like A2-B2
  2. When same ID comes again, take the difference of Date then difference would be like B2-A3. But ID should be from same group.

Below are the rows:

ID  Date 1  Date 2
5AB80D3A    7/10/2017 14:16 7/14/2017 11:38
5AB80D3A    7/14/2017 11:38 7/14/2017 12:48
5AB80D3A    7/14/2017 13:00 7/14/2017 19:09
5AB80D3A    7/14/2017 19:09 7/14/2017 21:09
5AB80D      7/14/2017 19:09 7/14/2017 21:09
5AB80D      7/14/2017 19:09 7/14/2017 21:09
5AB80A      7/14/2017 19:09 7/14/2017 21:09

If this can be done in excel/SQL, it will be helpful. Thanks in advance for your guidance.

1
sqlsql-serverexcelsql-server-2012
If you are not using MySQL, remove the tag. Otherwise the question is misleading. – Gordon Linoff
I am fetching data from SQL query. So if I can build this syntex in query then also it works. – Sharmil
what sql server are you using? – Kashif Qureshi
SQL server 2012 – Sharmil
So you want the max in column B minus the min in column A where the IDs are the same? – Scott Craner

1 Answers

0
votes

Take a look at the below query , it might guides you to do what you want.

 ;with
Testdata as
(select Id,Date1 ,Date2 
,ROW_NUMBER() over(partition by Id order by Date1) rowNumber
from Q1
)
select currentData.ID,currentData.Date1,currentData.Date2
,case when currentData.rowNumber=1 then datediff(MINUTE,currentData.Date1,currentData.Date2) else datediff(MINUTE,currentData.Date1,prevData.Date2) End Diffs  
from testData currentData 
left join Testdata prevData on currentData.ID=prevData.ID and currentData.rowNumber=prevData.rowNumber+1

Edit

after reviwing your comments I get the following query , hope it helps you

 ;with
Testdata as
(select Id,Date1 ,Date2 
 ,ROW_NUMBER() over(partition by Id order by Date1) rowNumber
 from Q1
)
select currentData.ID,currentData.Date1,currentData.Date2
,case when currentData.rowNumber=1 then datediff(MINUTE,currentData.Date1,currentData.Date2) else datediff(MINUTE,currentData.Date2,nextData.Date1) End Diffs  
from testData currentData 
left join Testdata nextData on currentData.ID=nextData.ID and currentData.rowNumber=nextData.rowNumber-1

Edit2 (using row_number & order by Date1 Descending to get the last row for every ID)

 ;with
Testdata as
(select Id,Date1 ,Date2 
 ,ROW_NUMBER() over(partition by Id order by Date1) rowNumber
 ,ROW_NUMBER() over(partition by Id order by Date2) rowNumberDesc
 from Q1
)
 select currentData.ID,currentData.Date1,currentData.Date2
 ,case when currentData.rowNumber=1 or currentData.rowNumberDesc=1  then datediff(MINUTE,currentData.Date1,currentData.Date2) else datediff(MINUTE,currentData.Date2,nextData.Date1) End Diffs  
 from testData currentData 
 left join Testdata nextData on currentData.ID=nextData.ID and currentData.rowNumber=nextData.rowNumber-1