How to join two lines in SELECT?

Question

I’m having a problem joining 2 lines. I did a lot of research, but I couldn’t find anything that could solve my case.

In the query below, there are two SELECT, which return two lines for each extension, as shown below. In the code, I need to join these two lines to display on screen, but this junction in the code is causing several problems.

Does anyone have any idea how I can return this result in just one line? For example:

|2016-04-15|2200|Bruna Farias da Silva Neves|1|454|18|470|

Note: I need to group by date and extension.

   ( SELECT     date(cdr.calldate) as data, 
            cdr.dst as ramal, 
            users.name as nome, 
            count(date(cdr.calldate)) as qtde_entrante, 
            sum(cdr.duration) as tempo_entrante, 
            NULL as qtde_sainte, 
            NULL as tempo_sainte 
    FROM (`asteriskcdrdb`.`cdr`) 
        JOIN `asterisk`.`users` ON `users`.`extension` = `cdr`.`dst` 
    WHERE cdr.calldate >= '2016-04-15 00:00:00' 
    and cdr.calldate <='2016-04-15 23:59:59' 
    AND cdr.dst LIKE '220%' 
    GROUP BY date(cdr.calldate), cdr.dst) 
 UNION ALL 
 ( SELECT   date(cdr.calldate) as data, 
            cdr.src as ramal, 
            users.name as nome, 
            NULL as qtde_entrante, 
            NULL as tempo_entrante, 
            count(date(cdr.calldate)) as qtde_sainte, 
            sum(cdr.duration) as tempo_sainte 
    FROM (`asteriskcdrdb`.`cdr`) 
        JOIN `asterisk`.`users` ON `users`.`extension` = `cdr`.`src` 
    WHERE cdr.calldate >= '2016-04-15 00:00:00' 
    and cdr.calldate <='2016-04-15 23:59:59' 
    AND cdr.src LIKE '220%' 
    AND cdr.dst not in ('s') 
    AND cdr.dst not like '*2%' 
    AND (cdr.dst < 900 OR cdr.dst > 999) 
    GROUP BY date(cdr.calldate), cdr.src) 
    ORDER BY date(data) asc, ramal asc)

Answer 1

It is possible to do this with SQL, but will end up facing problem of slowness.

In code, the solution without performance problem is to collect the input and output data separately, raise all extensions of people who had connections in those days and do JOIN manually.

SQL below tries to replicate these steps.

SELECT A.data
     , B.extension
     , B.name
     , C.qtde_entrante
     , C.tempo_entrante
     , D.qtde_sainte
     , D.tempo_sainte
FROM   ( SELECT DISTINCT DATE(calldate) AS data
         FROM   cdr
         WHERE  calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59'
       ) AS A
CROSS JOIN
       ( SELECT extension , name
         FROM users
         WHERE extension IN ( SELECT src FROM cdr where calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59' )
          OR   extension IN ( SELECT dst FROM cdr where calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59' )
       ) AS B
LEFT OUTER JOIN
       ( SELECT date(calldate) as data
              , dst as ramal
              , count(date(calldate)) as qtde_entrante
              , sum(duration) as tempo_entrante
         FROM   cdr
         WHERE  calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59'
          AND   dst LIKE '220%'
         GROUP BY 1,2
       ) AS C ON ( A.data = C.data AND B.extension = C.ramal )
LEFT OUTER JOIN
       ( SELECT date(calldate) as data
              , src as ramal
              , count(date(calldate)) as qtde_sainte
              , sum(duration) as tempo_sainte
         FROM   cdr
         WHERE  calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59'
          AND   src LIKE '220%'
          AND   dst not in ('s')
          AND   dst not like '*2%'
          AND   (dst < 900 OR dst > 999)
         GROUP BY 1,2
       ) AS D ON ( A.data = D.data AND B.extension = D.ramal )
WHERE ( NOT qtde_entrante IS NULL ) AND ( NOT qtde_sainte IS NULL )

Answer 2

Separate into two sub-queries

Your question is incomplete to do more testing but I believe the way is for you to create two sub-queries and then join with users. A SELECT representing incoming calls (where the branch is connected to the field dst) and one representing outgoing calls (where the extension is connected to the field src).

By understanding their exit, these queries should be joined by the date and extension, that is, where both the date and the extension number have records of incoming and outgoing calls. After joining these two "views" you would merge with the user table using the field extension.

I understand that there may be incoming calls on dates that have no outgoing calls and vice versa, so the JOIN could be a FULL OUTER JOIN, however it is necessary to analyze if you want to take these situations in your result. In the case of a Outer Join you will also need to link to the user table twice, once to the dst and another to the src.

As an illustration (because I have no way to test) the query below may help you understand what I am proposing and make necessary adaptations to the final result.

As a hint about performance issues, it’s likely that this has to do with LIKE. Try to reshape the base so that it allows these filters to use simple expressions (=, >, <)

SELECT 
    date(entrada.calldate) as data, 
    entrada.ramal,
    users.name as nome,  
    entrada.qtde_e, 
    entrada.duracao_e, 
    saida.qtde_s, 
    saida.duracao_s
FROM
    (
        SELECT  cdr.src as ramal,
                cdr.calldate,
                COUNT(cdr.calldate) qtde_s,
                SUM(cdr.duration) as duracao_s
        FROM asteriskcdrdb.cdr AS cdr
        WHERE cdr.calldate >= '2016-04-15 00:00:00' 
        and cdr.calldate <= '2016-04-15 23:59:59' 
        AND cdr.src LIKE '220%' 
        AND cdr.dst not in ('s') 
        AND cdr.dst not like '*2%' 
        AND (cdr.dst < 900 OR cdr.dst > 999) 
        GROUP BY cdr.calldate, cdr.src
    ) AS saida
JOIN
    (
        select  cdr.dst as ramal,
                cdr.calldate,
                count(cdr.calldate) as qtde_e, 
                sum(cdr.duration) as tempo_e
        FROM asteriskcdrdb.cdr as cdr
        WHERE cdr.calldate >= '2016-04-15 00:00:00' 
        and cdr.calldate <= '2016-04-15 23:59:59' 
        AND cdr.dst LIKE '220%' 
        GROUP BY cdr.calldate, cdr.dst
    ) AS entrada
ON entrada.ramal = saida.ramal 
AND entrada.calldate = saida.calldate
JOIN asterisk.users as users ON users.extension = entrada.ramal

Answer 3

Use SELECT DISTINCT. The function already filters the field that will perform select, I believe that by reassembling your want using it can facilitate. https://technet.microsoft.com/pt-br/library/ms187831(v=sql.105). aspx;

Answer 4

Hello, maybe you can leave UNION and try selects that resolve Counts for you within the initial query, for example:

SELECT     date(cdr.calldate) as data, 
            cdr.dst as ramal, 
            users.name as nome, 
            count(date(cdr.calldate)) as qtde_entrante, 
            sum(cdr.duration) as tempo_entrante, 
            (SELECT COUNT(date(cdr.calldate)) FROM asteriskcdrdb.cdr WHERE [..outros filtros..]) as qtde_sainte, 
            (SELECT COUNT(cdr.duration) FROM asteriskcdrdb.cdr WHERE [.. outros filtros..]) as tempo_sainte 
    FROM (asteriskcdrdb.cdr) 
        JOIN asterisk.users ON users.extension = cdr.dst 
    WHERE cdr.calldate >= '2016-04-15 00:00:00' 
    and cdr.calldate <='2016-04-15 23:59:59' 
    AND cdr.dst LIKE '220%' 
    GROUP BY date(cdr.calldate), cdr.dst)