Split a query

Question

I need some help from you guys, i have a query on my system, which has 4 cases

SELECT TOP 3 * FROM (
    SELECT Title, SUM(Count) AS Count
        FROM (
            SELECT
                CASE WHEN EstadoId = EstadoIdAutor AND EstadoId = Meddoc1 AND EstadoId = Meddoc2 AND EstadoId = Meddoc3 THEN
                    'Sem deslocamento'
                ELSE
                    CASE WHEN EstadoId = EstadoIdAutor AND (EstadoId = Meddoc1 OR EstadoId = Meddoc2 OR EstadoId = Meddoc3) THEN
                        'Fora de domicílio do autor'
                    ELSE
                        CASE WHEN EstadoId <> EstadoIdAutor OR (EstadoId = EstadoIdAutor AND (EstadoId <> Meddoc1 AND EstadoId <> Meddoc2 AND EstadoId <> Meddoc3)) THEN
                            'Mais de um deslocamento'
                        ELSE 'Outros'
                        END
                    END
                END AS Title,
            COUNT(*) AS Count
            FROM jud_Processos
            WHERE Status = 1 AND Trancado = 1 {dados}
            GROUP BY EstadoId, EstadoIdAutor, Meddoc1, Meddoc2, Meddoc3
        ) Sub
    GROUP BY Title
) TB_AUX

I wanted to know if it is possible to divide this query that in the case would be in 4 parts

No commuting - Out of place of the author - More than one commuting - Other

It is possible?

To be more specific, each case in a select.

Answer 1

It is possible, through UNION. But it is not recommended - the performance will be worse.

The recommendation is to simplify your CASE. You open a CASE complete as "otherwise" of each case. You can achieve the same goal as follows:

SELECT TOP 3 * FROM (
    SELECT Title, SUM(Count) AS Count
        FROM (
            SELECT
                CASE
                    WHEN EstadoId = EstadoIdAutor AND EstadoId = Meddoc1 AND EstadoId = Meddoc2 AND EstadoId = Meddoc3 THEN
                        'Sem deslocamento'
                    WHEN EstadoId = EstadoIdAutor AND (EstadoId = Meddoc1 OR EstadoId = Meddoc2 OR EstadoId = Meddoc3) THEN
                        'Fora de domicílio do autor'
                    WHEN EstadoId <> EstadoIdAutor OR (EstadoId = EstadoIdAutor AND (EstadoId <> Meddoc1 AND EstadoId <> Meddoc2 AND EstadoId <> Meddoc3)) THEN
                        'Mais de um deslocamento'
                    ELSE
                        'Outros'
                END AS Title,
                COUNT(*) AS Count
            FROM jud_Processos
            WHERE Status = 1 AND Trancado = 1
            GROUP BY EstadoId, EstadoIdAutor, Meddoc1, Meddoc2, Meddoc3
        ) Sub
    GROUP BY Title
) TB_AUX

It’s even more readable.

I recommend taking a look in this Microsoft article on syntax CASE. Suddenly you even optimize other queries of your system ;)

Answer 2

If the intention is to separate the first 3 CASE, you can do it this way:

-- Sem deslocamento
SELECT 'Sem deslocamento' AS Title,
       COUNT(1) AS Count
  FROM jud_processos jp
 WHERE jp.status = 1
   AND jp.trancado = 1
   AND jp.estadoid = jp.estadoidautor
   AND jp.estadoid = jp.meddoc1
   AND jp.estadoid = jp.meddoc2
   AND jp.estadoid = jp.meddoc3

-- Fora do domicílio do autor
SELECT 'Fora de domicílio do autor' AS Title,
       COUNT(1) AS Count
  FROM jud_processos jp
 WHERE jp.status = 1
   AND jp.trancado = 1
   AND jp.estadoid = jp.estadoidautor
   AND (jp.estadoid = jp.meddoc1
    OR  jp.estadoid = jp.meddoc2
    OR  jp.estadoid = jp.meddoc3)
-- Pelo menos 1 diferente
   AND (jp.estadoid <> jp.meddoc1
    OR  jp.estadoid <> jp.meddoc2
    OR  jp.estadoid <> jp.meddoc3)

-- Mais de um deslocamento
SELECT 'Mais de um deslocamento' AS Title,
       COUNT(1) AS Count
  FROM jud_processos jp
 WHERE jp.status = 1
   AND jp.trancado = 1
   AND jp.estadoid <> jp.estadoidautor
    OR (jp.estadoid <> jp.meddoc1
   AND  jp.estadoid <> jp.meddoc2
   AND  jp.estadoid <> jp.meddoc3)

And if you want to bring in just one result:

-- Sem deslocamento
SELECT 'Sem deslocamento' AS Title,
       COUNT(1) AS Count
  FROM jud_processos jp
 WHERE jp.status = 1
   AND jp.trancado = 1
   AND jp.estadoid = jp.estadoidautor
   AND jp.estadoid = jp.meddoc1
   AND jp.estadoid = jp.meddoc2
   AND jp.estadoid = jp.meddoc3
UNION
-- Fora do domicílio do autor
SELECT 'Fora de domicílio do autor' AS Title,
       COUNT(1) AS Count
  FROM jud_processos jp
 WHERE jp.status = 1
   AND jp.trancado = 1
   AND jp.estadoid = jp.estadoidautor
   AND (jp.estadoid = jp.meddoc1
    OR  jp.estadoid = jp.meddoc2
    OR  jp.estadoid = jp.meddoc3)
-- Pelo menos 1 diferente
   AND (jp.estadoid <> jp.meddoc1
    OR  jp.estadoid <> jp.meddoc2
    OR  jp.estadoid <> jp.meddoc3)
UNION
-- Mais de um deslocamento
SELECT 'Mais de um deslocamento' AS Title,
       COUNT(1) AS Count
  FROM jud_processos jp
 WHERE jp.status = 1
   AND jp.trancado = 1
   AND jp.estadoid <> jp.estadoidautor
    OR (jp.estadoid <> jp.meddoc1
   AND  jp.estadoid <> jp.meddoc2
   AND  jp.estadoid <> jp.meddoc3)