How to display in a query only certain records based on a date 1

[gtaborda]

Hi

i have the following records:

ROWID,SUPPLIER,VALIDFROM,VALIDTO,VALUE
1, PEPE JEANS, 01OCT09,31DEC09,$1000
2, CK, 11OCT09,31DEC09,$850
3, PEPE JEANS, 10OCT09,31DEC09,$800
4, CK, 12OCT09,31DEC09,$800
5, CK, 20OCT09,31DEC09,$800
6, PEPE JEANS, 01NOV09,31DEC09,$750

So I need to do a search by a date (example 12OCT09 based on VALIDFROM date) so the query should return:
2 CK, 11OCT09,31DEC09,$850
3 PEPE JEANS, 10OCT09,31DEC09,$800

which are the valid records on that date.

So another example, if I put date 12DEC09, I shoud get back:
5 CK, 20OCT09,31DEC09,$800
6 PEPE JEANS, 01NOV09,31DEC09,$750

I have tried GROUP BY, SORT BY LIMIT, COUNT(*) (a combination of those) and cannot get this to work !

could anyone help me ?

 

[vacunita]

What data type are your date columns? Looks like they are strings and not actual dates. If this is true there's no real way to compare them to say one string falls before or after another string.

You should make any date columns actual date types. then you can set your Where clause to look for dates with in a specified period.

If that is not possible you can use str_to_date to make the change and do the comparison.

SELECT *FROM mytable WHERE str_to_date(validfrom,'%d%M%y') > str_to_date('12DEC09','%d%M%y');








----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[gtaborda]

Hi, thanks for answering

the "valid" fields are datatime fields

I just put them on this message as DDMMMYY so it would not confuse people which use different formats.

 

[vacunita]

In that case a straight comparison should be all that is needed:

SELECT *FROM mytable WHERE VALIDFROM <= mydate AND VALIDTO >= mydate;


Of course your input should also be in a valid datetime format.


----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[gtaborda]

Hi there, thanks for your input.

The option you mentioned doesn't work unfortunately.
The problem is the VALIDTO >=mydate
As per my example, note that the Validto date is always the same for all rows (31.12.09)
Therefore if mydate = 15/10/09, using your query I will get 4 results back:

1, PEPE JEANS, 01OCT09,31DEC09,$1000
2, CK, 11OCT09,31DEC09,$850
3, PEPE JEANS, 10OCT09,31DEC09,$800
4, CK, 12OCT09,31DEC09,$800

When in fact should only be:

3, PEPE JEANS, 10OCT09,31DEC09,$800
4, CK, 12OCT09,31DEC09,$800

The main problem is, as mentioned, the ValidTo date, that it is the same for all.

I've been told by a friend to use GROUP BY together with LIMIT however I have not been able to make it work.

 

[vacunita]

Yes because they are all valid in that date.

Why are 01OCT09 and 11OCT09 not valid for a 15OCT09 date?

They are all with in the range. how exactly are you determining this then? Because I see no reason why it should only return 2 dates when all 4 are valid in that particular date because they don't end until December 31.



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[gtaborda]

the reason is because as soon as a new price is received, the price substitutes the old price

For example, on the 1st oct I receive this rate:
1, PEPE JEANS, 01OCT09,31DEC09,$1000
Then on the 10th October I receive a new price (same supplier, same validto date):
3, PEPE JEANS, 10OCT09,31DEC09,$800

So if I am searching for a price on he 15OCT, the price received last (10oct $800) is the one I want to see.
It is in fact the right price as this price ($800) replaced the previous one ($1000) which is in fact only valid till 09OCT (alothough the validto date is 31.DEC)

Surely I could go and ammend the previous price and change the validto date from 31dec to 09Oct when a new price is being entered, HOWEVER this is rather complex to do so I am looking for a more simple solution.

I hope that clears it.

 

[vacunita]

See now I understand the criteria.
You'll want to get then the closest possible date.

See explaining goes a long way in getting a good answer.

SELECT DISTINCT  supplier, MAX(validfrom) as validfrom, validto, myvalue FROM offers WHERE validfrom <= '2009-10-15' GROUP BY Supplier;

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[gtaborda]

Amazing !!!!!!!!!!

Thank you thank you thank you !!!!

You made my day!

Yes, you are right, I should have tried to be more clearer from the beginning, I am sorry.

Once again, thank you.