Search for values with priorities

Asked

Viewed 72 times

0

I’m looking for a search and I’m not finding the right logic to filter the results I want.

I have a table with all the expiration registrations of certain equipment. My problem is related here:

  • The Equipments (1, 22 and 23) are from the same group, so in case one of them shows up OK, do not want to list the other equipment of the group. If it appears all defeated, I want to list the 3 equipment as expired.
  • Already the code equipment equal to 6 if you’re beat I want you to list, if you’re OK you don’t need to list.

So far I searched the values in the database and created a select to find the data I need, but I can’t filter the way I reported. Follows 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

Table print: inserir a descrição da imagem aqui

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

sql sql-server

  • I’d think of a fuction

  • There are many unrelated criteria... I think you should do subqueries for each criterion and bring it all together at the end.

1 answer

0

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

Follows 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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.