Search for values with priorities

Question

Search for values with priorities

Navigation

#1 by (0 votes)

0

I'm trying to search and I can not find the right logic to filter the results I want.

I have a table with all the due dates for certain equipment. My problem is related here:

Equipments (1, 22, 23) are from the same group, so if one of them appears OK , I do not want you to list other equipment in the group. If it appears all expired, I want you to list the 3 equipments as expired.

So far I have searched the values in the database and created a select to find the data I need, but I can not filter the way I reported it. Follow script:

SELECT 
     y.* FROM   
            (SELECT
             e.numcad,
              e.codepi,
              Max(e.datent) AS Entrega,
              Max(e.datent) + p.diaval AS Validade,
              CASE
                WHEN
                  Max(e.datent) + p.diaval <= Getdate() 
                THEN
                  'VENCIDO' 
                ELSE
                  'OK' 
              END
              AS Situacao
            FROM
              r096die e, r096epi p 
            WHERE
              e.numcad = 241 
              AND e.codepi IN (1, 22, 23, 6)
              AND p.codepi = e.codepi 
             GROUP BY
             e.codepi ,  e.numcad , p.diaval

) Y 
GROUP BY  y.numcad, y.codepi, y.entrega, y.validade, y.situacao

Print from the table:

NOTE: I had to post as an image because I could not add a results table

sql sql-server

asked by anonymous 23.05.2017 / 13:47

1 answer

Database created with Core Data in Swift Unknown gulp error

score 0 · Answer 1

I was able to solve the above problem by creating a temporária table and then putting script to procedure .

Follow the script:

DECLARE @total INT;

SELECT
    E.NUMCAD ,
    E.CODEPI,
    MAX(E.DATENT) AS ENTREGA,
    MAX(E.DATENT) + P.DIAVAL AS VALIDADE,
    CASE
    WHEN
      MAX(E.DATENT) + P.DIAVAL <= GETDATE() 
    THEN
      'VENCIDO' 
    ELSE
      'OK' 
    END
    AS SITUACAO
    INTO #VEN  
FROM
    R096DIE E, 
    R096EPI P 
WHERE E.NUMCAD = 241 
  AND E.CODEPI IN (1, 22, 23, 6)
  AND P.CODEPI = E.CODEPI 
GROUP BY
    E.CODEPI ,  
    E.NUMCAD , 
    P.DIAVAL


SELECT @total = COUNT(*) FROM #VEN WHERE CODEPI IN (1,22,23) AND SITUACAO='VENCIDO';

SELECT * FROM #VEN WHERE (CODEPI = 6 AND SITUACAO = 'VENCIDO') OR (@total = 3 AND CODEPI <> 6 )

DROP TABLE #ven