Return values less than 10 from a column with Datediff

Question

I need to return values less than 10 in a column with DATEDIFF and others between 10 and 20 of the same column. Someone can help me?

SELECT CONVERT(VARCHAR(10), (MAX(REMESSA.DT_USO_FIM)),105) AS DATA_DEVOLUÇÃO
        ,DATEDIFF ( DAY, MAX(REMESSA.DT_USO_FIM),GETDATE())AS INDISPONIBILIDADE
        ,E.NM_EQUIPTO AS EQUIPAMENTO
        ,NR_PATRIMONIO AS PATRIMÔNIO
        ,CASE 
            WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO NOVA LIMA' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO NOVA LIMA','MG')   
            WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO RIO DE JANEIRO' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO RIO DE JANEIRO','RJ')
            WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO MANAUS' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO MANAUS','AM')    
        END AS FILIAL
FROM V_REMESSA_PATRIMONIO AS REMESSA
INNER JOIN PATRIMON AS P ON REMESSA.CD_PATRIMONIO = P.CD_PATRIMONIO
INNER JOIN EQUIPTO AS E ON E.CD_EQUIPTO = P.CD_EQUIPTO
INNER JOIN EST_ALMOX AS ALMOX ON ALMOX.CD_ALMOX = P.CD_ALMOX
WHERE P.CD_ALMOX IN(4,37,41)
    AND  CONVERT(DATE,REMESSA.DT_USO_FIM) <> '2099-01-01' 
GROUP BY P.NR_PATRIMONIO, E.NM_EQUIPTO, ALMOX.CD_ALMOX, ALMOX.NM_ALMOX
ORDER BY FILIAL;

Answer 1

Jander, for this totalization are not necessary some of the tables, which were used in the original query only to obtain descriptions.

Here is a way:

-- código #1 v2
with ctePATRIMON as (
SELECT NR_PATRIMONIO, P.CD_EQUIPTO, P.CD_ALMOX,
       datediff (day, REMESSA.DT_USO_FIM, Current_timestamp) as Indisp
  FROM V_REMESSA_PATRIMONIO AS REMESSA
       INNER JOIN PATRIMON AS P ON REMESSA.CD_PATRIMONIO = P.CD_PATRIMONIO
  WHERE P.CD_ALMOX IN (4, 37, 41)
       AND REMESSA.DT_USO_FIM <> '20990101' 
)
SELECT case CD_ALMOX 
            when 4 then 'xx'
            when 37 then 'xx'            
            when 41 then 'xx' end as FILIAL,
       sum (case when Indisp <= 10 then 1 else 0 end) as Ind0010,
       sum (case when Indisp between 11 and 20 then 1 else 0 end) as Ind1120
  from ctePATRIMON
  group by CD_ALMOX
  order by FILIAL;

Only need to replace "xx" by "AM", "MG" or "RJ", depending on the value of CD_ALMOX.

The report will be issued by warehouse. You can modify the code to issue by warehouse/equipment type if necessary.

Answer 2

You can turn your query into a sub-consultation, and from it, get by the column INDISPONIBILIDADE records under 10 and/or between 10 and 20.

SELECT T.DATA_DEVOLUÇÃO, T.INDISPONIBILIDADE, T.EQUIPAMENTO, T.PATRIMÔNIO, T.FILIAL
FROM (
    SELECT CONVERT(VARCHAR(10), (MAX(REMESSA.DT_USO_FIM)),105) AS DATA_DEVOLUÇÃO
            ,DATEDIFF ( DAY, MAX(REMESSA.DT_USO_FIM),GETDATE())AS INDISPONIBILIDADE
            ,E.NM_EQUIPTO AS EQUIPAMENTO
            ,NR_PATRIMONIO AS PATRIMÔNIO
            ,CASE 
                WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO NOVA LIMA' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO NOVA LIMA','MG')   
                WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO RIO DE JANEIRO' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO RIO DE JANEIRO','RJ')
                WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO MANAUS' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO MANAUS','AM')    
            END AS FILIAL
    FROM V_REMESSA_PATRIMONIO AS REMESSA
    INNER JOIN PATRIMON AS P ON REMESSA.CD_PATRIMONIO = P.CD_PATRIMONIO
    INNER JOIN EQUIPTO AS E ON E.CD_EQUIPTO = P.CD_EQUIPTO
    INNER JOIN EST_ALMOX AS ALMOX ON ALMOX.CD_ALMOX = P.CD_ALMOX
    WHERE P.CD_ALMOX IN(4,37,41)
        AND  CONVERT(DATE,REMESSA.DT_USO_FIM) <> '2099-01-01' 
    GROUP BY P.NR_PATRIMONIO, E.NM_EQUIPTO, ALMOX.CD_ALMOX, ALMOX.NM_ALMOX
    ) T
WHERE T.INDISPONIBILIDADE < 10 
      OR T.INDISPONIBILIDADE BETWEEN 10 AND 20
ORDER BY T.FILIAL;

Note: Your query has a lot of information that makes it difficult to generate a minimal example. Consider the idea of what can be done.

Response update

According to what you said, using CTE, you can do the sum to pick up the total equipment in each situation.
At the end, I added a way to know if the equipment is in the 10 days or between 11 and 20.

WITH CTE_GERAL (DATA_DEVOLUCAO, INDISPONIBILIDADE, EQUIPAMENTO, PATRIMONIO, FILIAL)
AS (
    SELECT CONVERT(VARCHAR(10), (MAX(REMESSA.DT_USO_FIM)),105) AS DATA_DEVOLUÇÃO
            ,DATEDIFF ( DAY, MAX(REMESSA.DT_USO_FIM),GETDATE())AS INDISPONIBILIDADE
            ,E.NM_EQUIPTO AS EQUIPAMENTO
            ,NR_PATRIMONIO AS PATRIMÔNIO
            ,CASE 
                WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO NOVA LIMA' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO NOVA LIMA','MG')   
                WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO RIO DE JANEIRO' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO RIO DE JANEIRO','RJ')
                WHEN ALMOX.NM_ALMOX = 'IMOBILIZADO MANUTENÇÃO MANAUS' THEN REPLACE (ALMOX.NM_ALMOX,'IMOBILIZADO MANUTENÇÃO MANAUS','AM')    
            END AS FILIAL
    FROM V_REMESSA_PATRIMONIO AS REMESSA
    INNER JOIN PATRIMON AS P ON REMESSA.CD_PATRIMONIO = P.CD_PATRIMONIO
    INNER JOIN EQUIPTO AS E ON E.CD_EQUIPTO = P.CD_EQUIPTO
    INNER JOIN EST_ALMOX AS ALMOX ON ALMOX.CD_ALMOX = P.CD_ALMOX
    WHERE P.CD_ALMOX IN(4,37,41)
        AND  CONVERT(DATE,REMESSA.DT_USO_FIM) <> '2099-01-01' 
    GROUP BY P.NR_PATRIMONIO, E.NM_EQUIPTO, ALMOX.CD_ALMOX, ALMOX.NM_ALMOX
    )
SELECT SUM(CASE WHEN INDISPONIBILIDADE < 10 THEN 1 ELSE 0 END) 'Menor_10',
       SUM(CASE WHEN INDISPONIBILIDADE >= 11 AND INDISPONIBILIDADE <= 20 THEN 1 ELSE 0 END) 'Entre_11_20'
FROM CTE_GERAL

--OU apenas informando 1 para verdadeiro e 0 para false em cada registro que não atenda sua condição

SELECT DATA_DEVOLUCAO, INDISPONIBILIDADE, EQUIPAMENTO, PATRIMONIO, FILIAL,
       CASE WHEN INDISPONIBILIDADE < 10 THEN 1 ELSE 0 END 'Menor_10',
       CASE WHEN INDISPONIBILIDADE >= 11 AND INDISPONIBILIDADE <= 20 THEN 1 ELSE 0 END 'Entre_11_20'
FROM CTE_GERAL
ORDER BY FILIAL;