General discussion

Locked

Help with SQL Query (date)

By griseldas ·
Hi there

I need to query my database in the following manner.
I get prices from suppliers which are valid for several months, so I am given a validity like this:
SUPPLIER, VALID_FROM, VALID_TO, VALUE, ITEM
1-PEPE SA, 01/oct/09, 31/dec/09, $1000, XXXX
2-PEPE SA, 01/nov/09, 31/dec/09, $500, XXXX
3-PEPE SA, 01/dec/09, 31/dec/09, $600, XXXX

As you can see, on the first line, $1000 is the cost between 01/oct/09 and 31/dec/09
But on the second line, the supplier has given us a new price, $500, valid from 01/11/09 to 31/dec/09, making redundant the first price (but still has a valid date of 31.dec.09

Again on the 01/dec/09 I get another new rate.

All VALID_TO dates are the same.

So the query I need is, for example, if today is 10/nov/09, I want the query to retrieve only row two

If today happens to be the 10/dec/09, the query should retrieve the 3rd line only.

Hope you can help me

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

From the data you've posted

by Tony Hopkinson In reply to Help with SQL Query (date ...

the only way to do that is by 2-PEPE being more 'recent' than the others. Aside from being vaguely crap with an alpha sort you'd get
1_pepe
11-pepe
12-pepe
2-pepe

as soon as you went over 9 pepe's
You could deal with that as well, but it's going to get messy quick.

a date column in the table when the row was added would be good, or just a straight int identity field.

Depends on whther you get retrospective changes e.g. receive a $475 for 1/11/09 - 31/11/09 after you got 3-pepe...

Another idea might be to split 1-pepe into 1 and pepe....

after you've got some idea of order prices should be considerd then something like

select top 1 * From suppliers where getdate() between valid_deom and ValidTo
and ...
order by somefieldthatindicatesanorder desc

is a quick way.

HtHs

Personally on receiving 2-pepe I'd overwrite 1-pepe at least in this table, it's a waste of space.

Collapse -

sorry for misleading

by griseldas In reply to From the data you've post ...

hi and thanks for taking the time to help

Sorry for misleading, I added the row number in front of each row....the supplier field doesn't include the 1, 2 etc

Sorry again for the misleading.

Collapse -

The only real key then is the time or order

by Tony Hopkinson In reply to sorry for misleading

it was added to the table.
So that would be a date column set at the time the record is inserted or an identity column.
Once you have a key you can find and order things, without it you are stuffed.

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Software Forums