Try the following query
select Project, TrackingGuid, Min(Method), DateDiff(MILLISECOND,min(dt),max(dt)) from sampletable group by Project, TrackingGuid
see the screenshots from SQL Server
- Table
- Query & output
Hope this helps
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I have table with this columns:
[ID] ,[dt] ,[Method],[Type],[Project],[TrackingGuid]
ID dt Method Type Project TrackingGuid
1 2022-01-14 20:49:57.6666667 OnSaved Start A1D0B5FF-F2BE-EB11-82EE-005056B44F4E 5FAE695E-6275-EC11-82FE-005056B44F4E
2 2022-01-14 20:49:59.7766667 OnSaved Finish A1D0B5FF-F2BE-EB11-82EE-005056B44F4E 5FAE695E-6275-EC11-82FE-005056B44F4E
3 2022-01-14 20:49:59.9333333 OnPublishing Start A1D0B5FF-F2BE-EB11-82EE-005056B44F4E 8EAE695E-6275-EC11-82FE-005056B44F4E
4 2022-01-14 20:50:00.1200000 OnPublishing Finish A1D0B5FF-F2BE-EB11-82EE-005056B44F4E 8EAE695E-6275-EC11-82FE-005056B44F4E
5 2022-01-17 09:29:43.2266667 OnPublishing Start BA265A30-9459-EC11-82F3-005056B4315A 1CA4A9D7-5E77-EC11-82F5-005056B4315A
6 2022-01-17 09:29:45.0533333 OnPublishing Finish BA265A30-9459-EC11-82F3-005056B4315A 1CA4A9D7-5E77-EC11-82F5-005056B4315A
7 2022-01-17 09:30:36.1400000 OnSaved Start BA265A30-9459-EC11-82F3-005056B4315A F21F92F6-5E77-EC11-82F5-005056B4315A
8 2022-01-17 09:30:36.1400000 OnSaved Finish BA265A30-9459-EC11-82F3-005056B4315A F21F92F6-5E77-EC11-82F5-005056B4315A
How can I select finish and start diference in one line?
The output is:
TrackingGuid,Project,Method,Duration
5FAE695E-6275-EC11-82FE-005056B44F4E, A1D0B5FF-F2BE-EB11-82EE-005056B44F4E, OnSaved, 2,111 sec
8EAE695E-6275-EC11-82FE-005056B44F4E, A1D0B5FF-F2BE-EB11-82EE-005056B44F4E, OnPublishing, 0,19 sec
...
Try the following query
select Project, TrackingGuid, Min(Method), DateDiff(MILLISECOND,min(dt),max(dt)) from sampletable group by Project, TrackingGuid
see the screenshots from SQL Server
Hope this helps