CODEWITHSUNDEEP
sql-servert-sqldatejoin
mr_primate
mr_primate

Reputation: 27

how to find a record between a range date

i have these tables:

table 1:

   date     |   idcurrency   |   total
2011-10-11  |       1        |    100
2011-10-14  |       1        |    500
2011-10-12  |       2        |    200
2011-11-01  |       2        |    100

table 2

   idcurrency  |   value   |   date
       1       |    200    | 2011-10-09
       1       |    350    | 2011-10-15
       2       |    200    | 2011-10-09
       2       |    250    | 2011-10-20

I need to calculate a new total based on the multiplication of field total from the table 1, with the field value from the table 2. Table 2 keep changes over the currency value and is not date continious, so I can't figure out how to join these tables to get my goal

Change design

I was thinking in change the design of table2, adding a new field to save the final date of the range for the currency value, like this:

idcurrency | value |  start_date  |  end_date
     1        200     2011-10-09     2011-10-14
     1        350     2011-10-15     2011-12-14
     2        200     2011-10-09     2011-10-19
     2        250     2011-10-20     2011-12-14

Then, i could join table1 and table2 without use "outer apply", but now am trying to make a query for add this new field

Thanks!!

Upvotes: 2

Views: 238

Answers (2)

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26737

what about something like the below(I didn't test it):

SELECT *,total*value as Total
FROM Table1 INNER JOIN (SELECT idCurrency,max(date) as value
GROUP BY idCurrency,date)
ON table1.idcurrency = table2.currency

Upvotes: 0

Andomar
Andomar

Reputation: 238296

You could use an outer apply subquery to search for the latest value for a currency:

select  t1.*
,       last_value.Value * t1.total
from    Table1 t1
outer apply
        (
        select  top 1 value
        from    Table2 t2
        where   t2.idcurrency = t1.idcurrency
                and t2.date <= t1.date
        order by
                t2.date desc
        ) last_value

Upvotes: 1

Related Questions