0

I have table 1 below with data for calls coming to a center 

  Date    | StartTime | EndTime  | Calling party | Reason  | Notes 
---------------------------------------------------------------------------
2017-02-2 | 09:09:17  | 09:10:50 | 1234567890    | General | Activity
2017-02-2 | 09:15:50  | 09:16:45 | 1234567890    | General | Check balance 
2017-02-2 | 09:21:04  | 09:21:37 | 1234567890    | General | Close 
2017-02-2 | 09:22:52  | 09:23:18 | 1234567890    | General | Transfer
2017-02-2 | 10:16:42  | 10:18:51 | 4455667788    | Helpcntr| NULL
2017-02-2 | 10:23:07  | 10:24:48 | 4455667788    | Helpcntr| Check accnt
2017-02-2 | 10:30:22  | 10:31:32 | 4455667788    | Helpcntr| Apply
   ...    |    ...    |   ...    |    ...        |   ...   |  ...

What I am trying to do is merge all the calls that have the same phone number with having the earliest StartTime and the latest EndTime as shown below 

  Date    | StartTime | EndTime  | Calling party | Reason  | Notes 
---------------------------------------------------------------------------
2017-02-2 | 09:09:17  | 09:23:18 | 1234567890    | General | Activity
2017-02-2 | 10:16:42  | 10:31:32 | 4455667788    | Helpcntr| NULL
   ...    |    ...    |   ...    |     ...       |    ...  |  ...

Note that Notes and Reason column are not as important as the rest of the columns but it would be great to be able to put the strings from all the rows in one row such as 

  Date    | StartTime | EndTime  | Calling party | Reason  | Notes 
--------------------------------------------------------------------------------------------
2017-02-2 | 09:09:17  | 09:23:18 | 1234567890    | General | Activity,Check balance, Close, Transfer
2017-02-2 | 10:16:42  | 10:31:32 | 4455667788    | Helpcntr| Check accnt, Apply 
   ...    |    ...    |   ...    |     ...       |    ...  |  ...

All the answers I found did not involve time since it repeats everyday. I was not able to get it right since the data is huge and is for the whole year

Ash A
  • 147
  • 1
  • 1
  • 12
  • This has been asked and answered thousands of times....please do a little digging. – Sean Lange Jul 06 '18 at 16:02
  • @SeanLange the answers I found did not involve time, since time is not unique and will repeat every day. Also, how would you join Notes coloumn together. Thank you – Ash A Jul 06 '18 at 16:10
  • Did you see the answer at the duplicate? All you have here is a MIN(StartTime) and MAX(EndTime) with grouping by CallingParty and Reason (and possibly date). The rest is EXACTLY the same as the duplicate. – Sean Lange Jul 06 '18 at 16:13
  • @SeanLange I saw it but if I used Min and max to merge the rows together, I would lose the Notes data. Is there a way to do them both in one query? – Ash A Jul 06 '18 at 16:22
  • You wouldn't lose them. Notice there is a subquery to generate the delimited list. you just need a where clause there to get all the rows where StartTime >= Max(StartTime) and EndTime <= Max(EndTime) and CallingParty is the same. Honestly the leap from the duplicate to yours is very very small. – Sean Lange Jul 06 '18 at 16:25
  • But do you really have NULL or is that a string "Null"? – Sean Lange Jul 06 '18 at 16:27
  • @SeanLange it is actually NULL – Ash A Jul 06 '18 at 16:28
  • OK can you demonstrate some attempt at solving this? I have given you a good example and told you the rest of what needs to happen. – Sean Lange Jul 06 '18 at 16:38

0 Answers0