-3

I have a table as below

ID DateTime Summary
1 2022-06-21 19:03:30.783 XSR
1 2022-06-21 19:04:40.763 GKE
1 2022-06-21 19:05:35.483 ERE
2 2022-07-20 11:01:20.783 BMR
2 2022-07-20 12:03:39.142 PER

It should produce an output as below. ie start datetime and end datetime of every ID. Also output the end time's Summary value.

Expected Result:

ID Start DateTime End DateTime Summary
1 2022-06-21 19:03:30.783 2022-06-21 19:05:35.483 ERE
2 2022-07-20 11:01:20.783 2022-07-20 12:03:39.142 PER

Code I tried

select MIN (t1.DateTime) as StartTime, MAX (t1.DateTime) as EndTime, datediff(MINUTE, min(t1.DateTime), max(t1.DateTime)) as 'RunTime (Mins)'
,max (t1.Summary)
from table t1
Manikandan Soubramaniane
  • 19
  • 1
  • 9
  • What is your question here; you don't appear to ask anything. What is the difficulty you are having getting the results you want? – Thom A Jul 08 '22 at 11:05
  • trying to get the start and end date/time along with the summary value of the end time. example of the resultant table (refer the second table). – Manikandan Soubramaniane Jul 08 '22 at 11:08
  • Have a look at [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) and the `MIN` function. – Thom A Jul 08 '22 at 11:13

1 Answers1

1

Try this:

SELECT 
  t1.id, 
  MIN(t1.[datetime]) AS [Starttime],
  MAX(t1.[datetime]) AS [Endtime],
  MAX(t2.summary) AS [Summary]
FROM Table t1, Table t2
WHERE t1.id = t2.id
  AND t2.[datetime] = (SELECT 
                         MAX([datetime]) 
                       FROM Table t3
                       WHERE t2.id = t3.id)
GROUP BY t1.id
;
Frank
  • 26
  • 2
  • Thank you Frank. May be, I did not give my requirement right. All I need is, I have table (refer the first table in my initial post) which has multiple time entries for the same [ID], I need the output as first and last date time for each ID along with last date time's Summary value. result of the query should produce output as second table. – Manikandan Soubramaniane Jul 08 '22 at 15:20
  • Hi Manikandan, I don't understand this comment. When I execute my query using your data, I get the results you want. id Starttime Endtime Summary 1 2022-06-21 19:03:30.783 2022-06-23 19:05:35.483 ERE 2 2022-07-20 11:01:20.783 2022-07-20 12:03:39.143 PER – Frank Jul 11 '22 at 07:26
  • I see you are referencing 2 tables `FROM Table **t1**, Table **t2**` actually, in my case, I have 1 table – Manikandan Soubramaniane Jul 12 '22 at 08:17
  • I'm not referencing 2 tables. I'm referencing 1 table twice. You didn't give a tablename so I named the table 'Table" in my SELECT-statement. I'm referencing "Table" twice so I had to use aliasses (t1 and t2). – Frank Jul 13 '22 at 12:41
  • Thank you Frank. Yes, your query is exactly producing the output which I was looking for. Sorry, initially I was little confused coz of the aliases. Cheers! – Manikandan Soubramaniane Jul 15 '22 at 09:15