Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    18

    Unanswered: Most Current Date

    I am not sure if this is possible or not, I consider myself pretty decent with sql and I am stumped on this any help would be greatly appreciated.

    Here is the scenario I have a table with a bunch of column and my last two columns are EFFI(Effect In Date) EFFO(Effect Out Date) our users recently loaded a bunch of data with an EFFI date of 1-15-2005 and an EFFO 12-31-9999, next year this time the will copy the same data but may change a column here or there but will make the EFFI date 1-15-2006 and EFFO 12-31-9999, we don't want to have to have our users effect out the previous years dates we want our queries to pick up the most recent price list record.

    When the application runs a user will enter a price date and we will give them a price based on the date the entered so for example if the entered a price date of 2/15/2005 we will pickup the record 1-15-2005 to 12-31-9999 but next year is going to be an issue because if I entere a price date of 2-15-2006 I will pickup both price records because I am using a BETWEEN in my where clause:ex.
    WHERE '2006-02-15' BETWEEN EFFI AND EFFO
    Is anyone anywhere of a way to pick up the most current record the 1-15-2006 record, you can't use MAX in a where clause, I have tried numerous things such as a setting EFFI = subselect where this grabs the max date but this grabs the max date of all records in table instead of max date based on criteria.

    Again any help would be greatly appreciated, I am stumped.

  2. #2
    Join Date
    Feb 2004
    Posts
    24
    I undetstood your problem, but I may help if you send your sql and see if you are joining with any other table which has dt_eop.
    Thanks
    Srinivas chityala

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tmacksam
    such as a setting EFFI = subselect where this grabs the max date but this grabs the max date of all records in table instead of max date based on criteria.
    Then may be you should use those criteria in the subselect?

    Alternatively,
    Code:
    WHERE '2006-02-15' BETWEEN EFFI AND EFFO
    AND YEAR('2006-02-15'') = YEAR(EFFI)

  4. #4
    Join Date
    Nov 2003
    Posts
    18
    Thanks for the replies:

    The suggestion of WHERE '2006-02-15' BETWEEN EFFI AND EFFO
    AND YEAR('2006-02-15'') = YEAR(EFFI) would work perfect except if someone changes our new effective price in the same year. Let me give you a couple of examples and my sql

    Database record
    ID Price EffI Effo
    1 234.00 2005-01-01 9999-12-31
    2 250.00 2005-03-01 9999-12-31
    3 275.00 2005-06-01 9999-12-31

    I understand some of the effect in dates are in the future but our application allows you to type in date and base on that date it will return you the price.

    SELECT PRICE from TABLE WHERE ? BETWEEN EFFI AND EFFO;

    So if the parameter passed in was 1/15/2005 I want 234 returned
    if paramater 3/15/2005 is passed in I want 250 returned and anything passed
    in with a date after 6/1/2005 should return 275. I hope this explains my dilemma a little more, we don't want to have our users change EFFO on previous records prior to the next EFFI.

    Thanks for the suggestions already!

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    select price from table where ? between effi and effo order by effi desc fetch first 1 row only
    This I guess will do the trick ... But, the appropriate way will be to change the effi each time a new record is inserted or an exisiting row deleted or updated ... You may do this using a trigger ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    sorry , i meant effo
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tmacksam

    Database record
    ID Price EffI Effo
    1 234.00 2005-01-01 9999-12-31
    2 250.00 2005-03-01 9999-12-31
    3 275.00 2005-06-01 9999-12-31
    I think your data model is inefficient in that case. The first price in the above example isn't effective from 2005-01-01 to infinity; it is only in effect until 2005-03-01. So, if you end-date the first record to have EFFO = '2005-03-01' then your query would work (well, with little modification: "...BETWEEN EFFI AND (EFFO - 1 DAY)...").

    If for some reason you decide to keep you current model then your query should be different. First of all, since your upper limit is equivalent to "inifinity" whatever date you enter as a parameter it will always be less than '9999-12-31', that is the comparison with EFFO is redundant. You only need to compare the parameter with EFFI, like this:

    Code:
    SELECT T.PRICE 
    from TABLE T 
    WHERE T.PRODUCT_ID = ? 
    AND T.EFFI = (
      SELECT MAX (EFFI) 
      FROM TABLE 
      WHERE T.PRODUCT_ID = PRODUCT_ID 
      AND EFFI <= ?
    )

  8. #8
    Join Date
    Jan 2005
    Posts
    191
    To expand a little on n_i's response: Greg Hannan (for those of you who know him) once suggested that

    1) store effo as the date on which the next price become effective. This makes it easy/efficient to check that there isn't a hole in the date range
    select ... from ... a where not exists (select 1 from .... b where a.effi=b.effo)
    - no date arithmetic means indexability

    So when you insert a new price/effi, use a trigger to change the effo of the previous "current price" row.

    2) use effo in descending sequence in the index. Think about how
    where effi <= ? and effo > ?
    will be processed. You'll might have many effi dates in the past, but only a few effo dates in the future.

    James Campbell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •