Agrupar líneas en función de un valor
Publicado por hercross (26 intervenciones) el 28/04/2021 16:56:33
Hola buenas tardes.. tengo esta tabla:
| FECHA | NUM | APP | EXTEN | CALL | TIME | STATUS | UNIQUEID |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/101 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/102 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/104 | Dial | SIP/100 | 100 | 0 | ANSWERED | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/106 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/107 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/101 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/102 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/104 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/106 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/107 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
Se podría hacer un SELECT para que me devolviera las líneas agrupadas por UNIQUEID, pero que si STATUS='ANSWERED' solo me devuelva la línea que tiene STATUS='ANSWERED', y si STATUS!='ANSWERED' me devuelva todas las líneas..
Es decir, en este ejemplo, si agrupo por UNiQUEID, me devolveria dos líneas:
| FECHA | NUM | APP | EXTEN | CALL | TIME | STATUS | UNIQUEID |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.100 |
Correcto?
Yo quisiera que me devolviera:
| FECHA | NUM | APP | EXTEN | CALL | TIME | STATUS | UNIQUEID |
| 2021-03-15 09:53:32 | SIP/104 | Dial | SIP/100 | 100 | 0 | ANSWERED | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/101 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/102 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/104 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/106 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/107 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
La primera línea sería del primer grupo, pero como en STATUS hay un ANSWERED, que me devuelva solo esa línea.
El resto, que no me las agrupe y que me de todas las líneas ya que ninguna de las del "grupo" tiene STATUS='ANSWERED'.
No se si me he explicado bien..
Sería posible?
Gracias
| FECHA | NUM | APP | EXTEN | CALL | TIME | STATUS | UNIQUEID |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/101 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/102 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/104 | Dial | SIP/100 | 100 | 0 | ANSWERED | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/106 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/107 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/101 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/102 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/104 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/106 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/107 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
Se podría hacer un SELECT para que me devolviera las líneas agrupadas por UNIQUEID, pero que si STATUS='ANSWERED' solo me devuelva la línea que tiene STATUS='ANSWERED', y si STATUS!='ANSWERED' me devuelva todas las líneas..
Es decir, en este ejemplo, si agrupo por UNiQUEID, me devolveria dos líneas:
| FECHA | NUM | APP | EXTEN | CALL | TIME | STATUS | UNIQUEID |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.100 |
Correcto?
Yo quisiera que me devolviera:
| FECHA | NUM | APP | EXTEN | CALL | TIME | STATUS | UNIQUEID |
| 2021-03-15 09:53:32 | SIP/104 | Dial | SIP/100 | 100 | 0 | ANSWERED | 1615798412.723 |
| 2021-03-15 09:53:32 | SIP/100 | Dial | SIP/100 | 100 | 99 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/101 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/102 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/104 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/106 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
| 2021-03-15 09:53:32 | SIP/107 | Dial | SIP/100 | 100 | 0 | NO ANSWER | 1615798412.100 |
La primera línea sería del primer grupo, pero como en STATUS hay un ANSWERED, que me devuelva solo esa línea.
El resto, que no me las agrupe y que me de todas las líneas ya que ninguna de las del "grupo" tiene STATUS='ANSWERED'.
No se si me he explicado bien..
Sería posible?
Gracias
Valora esta pregunta


0