Discussions

Help with SQL Query (date)

+
0 Votes
Locked

Help with SQL Query (date)

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
  • +
    0 Votes
    Tony Hopkinson

    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.

    +
    0 Votes
    griseldas

    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.

    +
    0 Votes
    Tony Hopkinson

    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.

  • +
    0 Votes
    Tony Hopkinson

    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.

    +
    0 Votes
    griseldas

    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.

    +
    0 Votes
    Tony Hopkinson

    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.