Return value corresponding to date

Asked

Viewed 101 times

0

I have a table with values and dates referring to the cost of a vehicle. However the table is filled in as follows:

data_saida | custo_saida
-------------------------
2019-11-17 | 25
2019-11-18 | 20
null       | 10

Filling is done this way because specific days are less common, and speeds up user information input.

I need that when the date is 17/11 return the cost 25, when it is 18/11 return 20, and any other days return 10 (example: 19, 20 and 21/11).

I have tried several querys and none have succeeded:

SELECT custo_saida 
FROM tabela WHERE data_saida = COALESCE('2019-11-19', null::date)

The above query returns the correct cost_output when the searched date corresponds with the dates in the table, but does not return any value when the out_date is any other date, as in the example 19/11.

SELECT custo_saida 
FROM tabela WHERE (data_saida = '2019-11-19' OR data_saida is null)

Already the previous query returns correct when the search date was not set in the data_output field of the table, but when I look for a date that is set, for example 18/11, it returns me the values 20 and 10.

And I need this query to be a main subquery, so it needs to return only one value.

Is there any way to do it?

sql postgresql

  • By your explanation we can conclude that there may be data_output in your table that are not associated with a cost_output?

  • @anonimo In fact it is the opposite, there is costo_saida without associated data_saida. If a date that does not contain in the worksheet is informed, this outgoing data_will receive the cost_output where the outgoing data_is null. And when a data_output that contains in the worksheet is informed, it will return the value of the corresponding cost_output.

3 answers

1

Try it this way.

SELECT CASE WHEN data_saida is null THEN 10 ELSE custo_saida END AS custo_saida
FROM tabela

1

Try this way. Just replace the date 2019-11-19 by the date you want to search.

SELECT custo_saida
FROM tabela
WHERE (data_saida = '2019-11-19' OR data_saida IS NULL)
ORDER BY data_saida DESC NULLS LAST
LIMIT 1

  • @thiagofred, this command solved your need? In the other answer you said you had to return the field desired date. Just add to SELECT after the field cost

  • Yes, this command is also a way to resolve my need.

1


See if this is it:

SELECT (CASE WHEN EXISTS (SELECT 1 FROM tabela aux1 WHERE aux1.data_saida = data_desejada::date) 
                        THEN (SELECT custo_saida FROM tabela WHERE data_saida = data_desejada::date) 
                        ELSE (SELECT custo_saida FROM tabela WHERE data_saida IS NULL) 
          END) AS custo_saida;

  • It works for what I asked but I need to return also the data_saida (I forgot to edit the question).

  • Do you need to return the output data_or the desired data_out? Note that the output data_out will be NULL if it does not exist in your table.

  • This, I need the desired date, I was able to do by inserting the desired data_after cost_output. I will evaluate which is the best option.

Browser other questions tagged sql postgresql

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