SQL Left Join - smaller date greater than current date

Question

SQL Left Join - smaller date greater than current date

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

3 tables: Processo , Evento , Alerta

The goal is to make a query that returns all processes by adding the columns of the event type and date of the most recent event MAX(DataEvento) and the next alert (smaller date greater than the current date) / p>

SELECT p.id
      ,p.scope
      ,p.entrydate
      ,p.applicant
      ,p.status
      ,MAX(ev.eventdatetime) lastevent
      ,ev.type
      ,ev.eventobs
      ,MIN(al.deadlinedate) nextalert
      ,al.alertobs
  FROM process p
  LEFT JOIN event ev
    ON ev.processid = p.id
  LEFT JOIN alert al
    ON al.eventid = ev.eventid
   AND al.expiredate > getdate()
 GROUP BY p.id
         ,p.scope
         ,p.entrydate
         ,p.applicant
         ,p.status
         ,ev.type
         ,ev.eventobs
         ,al.expiredate
         ,al.alertobs

The above code repeats all processes several times, depending on the number of events.

    SELECT p.id
      ,p.scope
      ,p.entrydate
      ,p.applicant
      ,p.status
      ,MAX(ev.eventdatetime) lastevent
      ,ev.type
      ,ev.eventobs
      ,MIN(al.deadlinedate) nextalert
      ,al.alertobs
  FROM process p
 INNER JOIN event ev
    ON ev.processid = p.id
 INNER JOIN alert al
    ON al.eventid = ev.eventid
 WHERE ev.processid = p.id
   AND a.expiredate > getdate()
 GROUP BY p.id
         ,p.scope
         ,p.entrydate
         ,p.applicant
         ,p.status
         ,ev.type
         ,ev.eventobs
         ,al.alertobs

Lists only one process, the one that has the most recent event.

SELECT DISTINCT p.id
               ,p.scope
               ,p.entrydate
               ,p.applicant
               ,p.status
               ,(SELECT TYPE
                      ,MAX(ev.eventdatetime) lastevent
                      ,eventobs
                  FROM event ev
                 WHERE ev.processid = p.id
                 GROUP BY TYPE
                         ,eventobs)
               ,(SELECT MIN(a.expiredate) nextalert
                      ,alertobs
                  FROM event ev
                 INNER JOIN alert a
                    ON ev.eventid = a.eventid
                 WHERE ev.processid = p.id
                   AND a.expire > getdate())
  FROM process p
 ORDER BY p.id ASC

Here's the error:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

What I want is to list all the processes each appears once and the query fields are displayed, adding the date, type and observations of the last event and the date and observations of the next alert, eg:

1|Registo|21/06/2018|João Sousa|Activo|21/06/2018|Entrada do processo|Observações do evento|26/06/2018|Prazo para resposta

2|Pedido|21/06/2018|Pedro Antunes|Activo|21/06/2018|Entrada do processo|Observações do evento|26/06/2018|Prazo para resposta

sql join query date

asked by anonymous 25.05.2018 / 01:50

2 answers

3

Only use SubSelect:

SELECT 
    p.Id, 
    p.Scope, 
    p.EntryDate, 
    p.Applicant, 
    p.Status,
    (SELECT 
         MAX(ev.EventDateTime) 
     FROM event ev 
     WHERE ev.processId = p.id) as evento_recente,
    (SELECT 
         MIN(a.ExpireDate) 
     FROM event ev 
     INNER JOIN Alert a ON ev.eventid = a.eventId 
     WHERE ev.processId = p.id) as proximo_alerta
FROM Process p

25.05.2018 / 04:22

Android - Run function with seconds of delay How to retrieve an object from a HashSet

score 0 · Accepted Answer

Small adjustments may be necessary because I have not yet tested whether the loaded dates are always correct, but it will never be very different from the list I get with the code below:

 SELECT p.id
      ,p.scope
      ,p.entrydate
      ,p.applicant
      ,p.status
      ,ev.type
      ,ev.eventdatetime AS lastevent
      ,ev.eventobs
      ,MIN(al.expiredate) nextalert
      ,al.alertobs
  FROM process p
  LEFT OUTER JOIN event ev
    ON ev.processid = p.id
   AND ev.eventid = (SELECT top(1) eventid
                       FROM event e
                      WHERE e.processid = ev.processid
                      ORDER BY e.eventdatetime DESC)
  LEFT OUTER JOIN alert al
    ON al.processid = p.id
   AND al.alertid =
       (SELECT top(1) alertid
          FROM alert a a.deadlinedate > getdate())
 GROUP BY p.id
         ,p.scope
         ,p.entrydate
         ,p.applicant
         ,p.status
         ,ev.type
         ,ev.eventdatetime
         ,ev.eventobs
         ,al.alertobs

Many thanks @Rovann Linhalis for the precious help.