SQL higher date of a record before the given date

Asked

Viewed 30,209 times

1

Next is the following case:

Tb_importacao [cod_titulo,data,imp_situacao]

cod_titulo   data_imp    situacao
1           2015-04-10      1
1           2015-04-11      2
1           2015-04-11      1
1           2015-04-13      2
1           2015-04-14      3
1           2015-04-14      4

I need the biggest date that has situation 1 where data_imp < 2015-04-13. My real table allowed more than one cod_titles...

Vlw!

sql date

  • And what is the doubt?

  • SELECT MAX(data_imp) FROM tb_importacao WHERE data_imp < '2015-04-13' AND situacao = 1 ?

      • so ratio 1 = active, 2 = deactivative The guy keeps going back and forth on my base I need the longest date he was active and the longest of inactive, to know if on my target date he was active or not.

  • Raphael, just edit sql, can test again?

  • Rafael: so I only bring the longest date I need it by cod_title

  • It is that you have not provided all the information in the question... so we will go on the kick, can edit it and provide a detailed explanation of what you want and maybe an example of the expected end result?

  • data_target = 2015-04-13 situaca_target = 1 Expected result cod_title data_imp situacao 1 2015-04-11 1

  • Raphael, you can edit your question at the link (edit) below it, or here: http://answall.com/posts/58826/edit

  • Is there anyone who can help with this question of dates? http://answall.com/questions/120596/comor-fazer-um-sql-que-consulte-apenas-dia-de-umdata

Show 4 more comments

3 answers

4

Going by parts:

  • Select the largest data_imp => MAX(data_Imp)
  • When the data_imp is less than 2015-04-13
    and the situation is 1 =>WHERE data_imp < '2015-04-13' AND situacao = 1
  • For every cod_title => GROUP BY cod_titulo

Putting it all together:

SELECT cod_titulo, MAX(data_imp) FROM tb_importacao 
WHERE data_imp < '2015-04-13' AND situacao = 1
GROUP BY cod_titulo

  • example the date of situation 1 came out 2015-04-10 so that this cod_title had a situation 2 on the day 2015-04-11 then on the 13th that is the target in case this guy was not active and that’s what I need

  • I don’t understand. A query of my answer brings or does not bring the result you expect? If not what is expected?

  • Devolution 09/04/2015 16:19 Reingresso 09/04/2015 16:19 Import 09/04/2015 16:19 on 9 this guy was not active because the last record returns the guy

1

Could you test this SQL? I was going to leave in the comments but it’s too big. Maybe you need some adaptation yet to get the exact result you need. Just let her know we’re modeling her. (I can’t test)

SELECT imp1.cod_titulo, MAX(imp2.data_imp) as data_ativa, MAX(imp3.data_imp) as data_inativa FROM tb_importacao as imp1
JOIN tb_importacao as imp2 ON imp2.situacao = 1
JOIN tb_importacao as imp3 ON imp3.situacao = 2
WHERE imp1.data_imp < '2015-04-13'
GROUP BY cod_titulo

In case it will return two dates. The longest active date (active date) and longest inactive date (inactive data_date), so you compare it to your target date in the schedule.

  • Face the problem is that the data_imp is in one table and the situation in another D=

  • Ah... there would not be able to guess it... The structure you provided in the question is not your table then? What is this situation table?

  • Is that the treco ta complex man The system here is a shit I have the IMPORT which is where has the entry date of the record I have the IMPORTACAO_HISTORICO which has the situation and the cod_title Your query was going the right way so I didn’t give you the same data thank you

  • kkkk, I tested here a table with the structure you provided in the question and apparently this SQL is working. We just need to adapt your situation, can’t you provide more details? Either you take sql up there and edit to merge with your situation table, or that situation is a Foreign key in the tb_import table?

-1

Try using the GREATEST function.

select greatest(col1, col2, col3, col4) from tab1;

Browser other questions tagged sql date

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