Return values smaller than 10 of a column with DateDiff

1

I need to return values less than 10 in a column with DATEDIFF and others between 10 and 20 in the same column. Can anyone 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;
    
asked by anonymous 10.04.2017 / 20:48

2 answers

1

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

Here's 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;

You only need to replace "xx" with "AM", "MG" or "RJ" depending on the value of CD_ALMOX.

The report will be issued by storeroom. You can change the code to issue by storeroom / type of equipment, if necessary.

    
11.04.2017 / 22:19
1

You can transform your query into a subquery, and from there, get the column INDISPONIBILIDADE the records smaller than 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 sum to get the total equipment in each situation.
In 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;
    
10.04.2017 / 21:26