count amount of records in recent months

Asked

Viewed 115 times

0

I have 3 aircraft tables, occurrence and aeronave_occurrence, I need to know if the same aircraft was present in any event in the last 2 months of the data_utc back, and how many aircraft were present.

Information in the tables: aircraft (id_aircraft, registration); occurrence (id_occurrence, data_utc); aeronave_occurrence(id_aeronave_occurrence, id_aircraft, id_occurrence)

I’m lost in this query, I don’t know where to go HELP:

SELECT
  a.matricula,
  count(a.matricula)
FROM
  aeronave a
  JOIN aeronave_ocorrencia ao ON a.id_aeronave = ao.id_aeronave
  JOIN ocorrencia o ON ao.id_ocorrencia = o.id_ocorrencia
WHERE
  o.data_utc > ADD_MONTHS(o.data_utc , -2 )
GROUP BY 
  a.matricula
sql query oracle

  • Put as a condition o.data_utc > ADD_MONTHS(SYSDATE , -2 ).

  • or.data_utc >= TRUNC(ADD_MONTHS(SYSDATE , -2 ),'MM') taking into account the beginning of the month , which means "last 2 months"

  • "last 2 months" means the last 2 months taking as reference the data_utc of occurrence.

  • my doubt would be , date today 14/8 , would be 14/7 to 14/8 , from 1/7 to 14/8 ,... I understand as 1/7 to 14/8

  • If data_utc = 14/08, then it would be 14/06 to 14/08 and so on.... See that data_utc are several distinct dates, it can be 12/08, 03/03...

1 answer

-1

Try it like this:

SELECT
  a.matricula
FROM
  aeronave a
JOIN 
  aeronave_ocorrencia ao ON a.id_aeronave = ao.id_aeronave
JOIN
  ocorrencia o ON ao.id_ocorrencia = o.id_ocorrencia
WHERE
  o.data_utc > ADD_MONTHS(CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE), -2)
GROUP BY 
  a.matricula

See the fiddle here.

Browser other questions tagged sql query oracle

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