使用Excel / SQL查找重复值并与其他值区分

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:

我有一个非常奇怪的问题。我有很多关于日期列，ID列等的数据。我想在两列中考虑2个日期的时间差（其中日期报告为7/16/2017 18:42）。在第一个例子中，看起来容易区分，但问题在于ID列。在ID列中，还有许多ID也是重复的ID。以下是条件：

1. Take difference of date and time where first row of ID. Like A2-B2
2. 区分第一行ID的日期和时间。像A2-B2一样
3. When same ID comes again, take the difference of Date then difference would be like B2-A3. But ID should be from same group.
4. 当相同的ID再次出现时，取日期的差异然后差异将像B2-A3。但ID应来自同一组。

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.

如果这可以在excel / SQL中完成，那将会很有帮助。在此先感谢您的指导。

1 个解决方案

#1

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