CODEWITHSUNDEEP
sqlsql-serversql-server-2014
user3115933
user3115933

Reputation: 4453

Query to fetch values corresponding to the last effective date

I am using SQL Server 2014. I have 3 tables in my database that need to be joined in a query and they are as follows:

ResStayDate.dbo

ResaID    StayDate        Rate   
 50       2015-01-20      235.00
 50       2015-01-21      235.00
 50       2015-01-22      235.00
 50       2015-01-23      235.00
 54       2015-02-10      200.00
 54       2015-02-11      200.00
 65       2015-03-12      155.00
 65       2015-03-13      155.00
 67       2015-04-10      290.00
 67       2015-04-11      290.00
 67       2015-04-12      290.00

ResStay.dbo

 ResaID    Currency
  50        USD
  54        USD
  65        EUR
  67        EUR

ExchangeRate.dbo

Currency     ExRate     EffectiveDate
  USD        28.00       2015-01-20
  EUR        40.00       2015-01-20
  USD        30.00       2015-01-22
  EUR        40.00       2015-01-22
  USD        30.00       2015-03-13
  EUR        40.00       2015-03-13
  USD        30.00       2015-04-11
  EUR        42.00       2015-04-11

The output that I want my query to give is as follows:

 ResaID       StayDate    Rate      ExRate        Revenue 
   50       2015-01-20   235.00     28.00          6580.00
   50       2015-01-21   235.00     28.00          6580.00
   50       2015-01-22   235.00     30.00          7050.00
   50       2015-01-23   235.00     30.00          7050.00
   54       2015-02-10   200.00     30.00          6000.00
   54       2015-02-11   200.00     30.00          6000.00
   65       2015-03-12   155.00     40.00          6200.00
   65       2015-03-13   155.00     40.00          6200.00
   67       2015-04-10   290.00     40.00         11600.00
   67       2015-04-11   290.00     42.00         12180.00
   67       2015-04-12   290.00     42.00         12180.00

Revenue is calculated as follows: ResStayDate.Rate * ExRate (by matching the Currency in ResStay.dbo with the Currency value in ExchangeRate.dbo).

I have progressed this far with my query, but I am stuck with how to match the ResStayDate.StayDate with the ExchangeRate.EffectiveDate so that the query can use the proper ExRate of the Currency. My problem lies in that the ExchangeRate table does not list the values of the currency on a daily basis but only when there is a change in the ExRate of a Currency.

As such, the ExRate of a Currency is effective until the date of the next change and so on. I need to match my StayDate in the ResStayDate table with the corresponding ExRate value of that Currency in the ExchangeRate table.

So, my draft query as at now looks like this:

 SELECT * FROM 

 (
 (SELECT ResaID, 
        StayDate,
        Rate,
        (?????) As [Revenue]
 FROM ResStayDate)a

LEFT JOIN ResStay b ON b.ResaID = a.ResaID
LEFT JOIN ExRate  c ON c.Currency = b.Currency

)xy

I can't figure out how to plug in the ExRate in the query so that I can use the equation to get the Revenue column.

Upvotes: 1

Views: 159

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32693

I would use CROSS APPLY here. Here is SQL Fiddle.

Add index to ExchangeRate on (Currency, EffectiveDate) and it would be efficient.

Make sure that you have a value in ExchangeRate for each currency with EffectiveDate before the first date in ResStayDate, otherwise these dates would not get any matching rows in CROSS APPLY and they will not appear in the final result. If your data may have historical values that don't have exchange rates, use OUTER APPLY instead of CROSS APPLY. In this case you'll get NULLs and you'll at least see that something is wrong, rather than silently not having rows in the result.

SELECT
  ResStayDate.ResaID
  ,ResStayDate.StayDate
  ,ResStayDate.Rate
  ,CA.ExRate
  ,ResStayDate.Rate * CA.ExRate AS Revenue
FROM
  ResStayDate
  INNER JOIN ResStay ON ResStay.ResaID = ResStayDate.ResaID
  CROSS APPLY
  (
      SELECT TOP(1) ExchangeRate.ExRate
      FROM ExchangeRate
      WHERE
        ExchangeRate.Currency = ResStay.Currency
        AND ExchangeRate.EffectiveDate <= ResStayDate.StayDate
      ORDER BY ExchangeRate.EffectiveDate DESC
  ) AS CA
ORDER BY ResaID, StayDate;

Upvotes: 2

Rahul
Rahul

Reputation: 77896

Why can't you do like below, also use ISNULL() function

SELECT a.ResaID, 
        a.StayDate,
        a.Rate,
        a.Rate * ISNULL(c.ExRate,0) As [Revenue]
 FROM ResStayDate a
LEFT JOIN ResStay b ON b.ResaID = a.ResaID
LEFT JOIN ExchangeRate  c ON c.Currency = b.Currency;

Upvotes: 0

Related Questions