Divide a query

Navigation
2

I need your help, I have a query in 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 would like to know if it is possible to split this query which in this case would be in 4 parts

No displacement - Outside the author's home - More than one displacement - Other

Is it possible?

To be more specific, each case in a select .

    
asked by anonymous 03.10.2017 / 14:07

2 answers

0

If you want to separate the first 3 CASE , you can do the following: 

-- 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)
    
05.10.2017 / 00:54
2

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

The recommendation is to simplify your CASE . You open%% 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 is even more readable.

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

    
03.10.2017 / 14:22
Compare Latitude and Longitude in Python How to do a SUM in different table fields in the Entity Framework?