Select max date and the date before

Question

Hi

I have a data table which is updated every day except Saturday and Sunday. The problem lies in so that when I retrieve data with max (date) and max (date) -1

But it fails when I try to retrieve data for today (Monday) and yesterday (Sunday) when max (date) -1 does not exist.

the data can be updated on Saturday and Sunday, but since it's exchange rate I update. Will it give the same exchange rates Friday, Saturday and Sunday.

This is one way to solve the problem this way, but there is a better

string weekend = DateTime.Now.DayOfWeek.ToString();
if (weekend == "Monday")
{
select ***** where max(date)-3 from *****
}

Answer 1

You can select the newest record that is at least one day old. Then the weekend isn't a special case.

Answer 2

You are effectively trying to find the second highest date in a dataset.

Find the highest date in the data. Find values less than this, limit the results to 1 and you have the previous day in the dataset.

Any other method will fail on other days when rates are not updated, e.g. christmas.

Answer 3

Here's some other options which work with an arbitrary target_date as well.

If you only have one record for each date,

SELECT * FROM table WHERE date<=target_date ORDER BY date DESC LIMIT 2

If you have many records per date,

SELECT * FROM table WHERE date IN (SELECT DISTINCT date FROM table WHERE date<=target_date ORDER BY date DESC LIMIT 2)

You could also use the date/time functions of your database to check for Monday, of course.

Answer 4

hmm you might need som more details on the string

The sql string is

SELECT m.Navn, m.Beskrivelse, p_idag.Points AS Points_idag, p_igaar.Points AS Points_igaar FROM medlem m LEFT JOIN (SELECT * FROM point WHERE Datoen = '06-09-2010') AS p_idag ON m.Navn = p_idag.Navn LEFT JOIN (SELECT * FROM point WHERE Datoen = '06-08-2010') AS p_igaar ON m.Navn = p_igaar.Navn ORDER BY m.Navn;

The first date is selected by the user, the next date that date -1 day or -3