CODEWITHSUNDEEP
sql
AndyBSG
AndyBSG

Reputation: 37

Select the FIRST record with a value greater than XXX

OK, another newbie SQL question which i'm sure has a simple solution and i'll kick myself when someone posts the answer!

I have two tables as follows

PRICE_DTA
PRC_DATE    PRC_TIME    PRC_PRICE    PRC_ITEM

2008-01-01  06.00.00    1.05        JUMPER
2008-01-01  09.00.00    1.20        JUMPER
2008-01-25  17.00.00    1.75        JUMPER
2008-01-02  09.00.00    2.25        TROUSERS
2008-10-25  12.00.00    2.95        TROUSERS 


SALE_DTA
TRN_DATE    TRN_TIME    TRN_PRICE_PAID   TRN_ITEM

2008-01-01  08.30.00                     JUMPER
2008-01-03  10.00.00                     JUMPER
2008-01-03  17.00.00                     JUMPER
2008-01-01  13.00.00                     TROUSERS
2008-01-02  09.00.00                     TROUSERS

The way the prices work is that you get the NEXT available price(prices aren't set until after the purchase because we bulk all the orders up and get a cheaper price the more we buy in one go). So, in the example the 08.30.00 purchase on 2008-01-01 will have been for 1.20 because that is the first available price after the purchase date/time

So, I need to populate the prices in the SALE_DTA table using the TRN_DATE/TRN_TIME fields to go an get the next available price off the PRICE_DTA tables. NOTE: The DATE and TIME fields on both tables are CHAR fields not date/timestamp fields

I can concatenate the date and time easily enough but i'm not sure how to find the FIRST record on PRICE_DTA with a date/time stamp greater than that. I know on UNISYS DMS II I can use a 'FIND NEXT GREATER THAN' but can't find a similar command in SQL?

I'm happy to create a temporary table as part of the solution if that makes it simpler.

Upvotes: 1

Views: 2316

Answers (2)

Rahul
Rahul

Reputation: 77876

You can certainly get the data required but DB2 don't support JOIN with UPDATE statement. So you can take a different route like

Create a auxiliary table

create table SALE_DTA_temp(TRN_DATE,TRN_TIME,TRN_PRICE_PAID,TRN_ITEM)

Do a insert into temp table from the query

insert into SALE_DTA_temp
select sd.TRN_DATE,
sd.TRN_TIME,
tab.max_PRC_PRICE as TRN_PRICE_PAID,
sd.TRN_ITEM
from SALE_DTA sd
join 
(
select PRC_DATE, max(PRC_PRICE) as max_PRC_PRICE
from PRICE_DTA
group by PRC_DATE
) tab on sd.TRN_DATE = tab.PRC_DATE

Drop the old table

drop table SALE_DTA

Rename the table

RENAME TABLE SALE_DTA_temp TO SALE_DTA

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

The generic SQL solution for this can be done with a couple of joins:

SELECT
    * --TODO - Pick appropriate columns
FROM
    SALE_DTA s
       INNER JOIN
    PRICE_DTA p
       ON
          p.PRC_ITEM = s.TRN_ITEM and
          (p.PRC_DATE > s.TRN_DATE or
              (p.PRC_DATE = s.TRN_DATE and
              p.PRC_TIME > s.TRN_TIME
          ))
       LEFT JOIN
    PRICE_DTA p2
       ON
          p2.PRC_ITEM = s.TRN_ITEM and
          (p2.PRC_DATE > s.TRN_DATE or
              (p2.PRC_DATE = s.TRN_DATE and
              p2.PRC_TIME > s.TRN_TIME
          )) and
          (p2.PRC_DATE < p.PRC_DATE or
              (p2.PRC_DATE = p.PRC_DATE and
               p2.PRC_TIME < p.PRC_TIME
          ))
 WHERE
     p2.PRC_ITEM IS NULL

Hopefully, you can see the logic here. The INNER JOIN is used to match rows in SALE_DTA with all rows in PRICE_DTA that occur afterwards. We then do a second join (the LEFT JOIN) to this PRICE_DTA again, this time trying to locate a row with this join (p2) such that it still occurs after the s date/time, but occurs before the p date/time.

Finally, in the WHERE clause, we eliminate any rows where this LEFT JOIN actually succeeded. Therefore, by deduction, we know that the row that we matched in p is the earliest row from PRICE_DTA which occurs after the SALE_DTA date/time.

Upvotes: 2

Related Questions