CODEWITHSUNDEEP
sqlsql-server
deadcode
deadcode

Reputation: 11

SQL query: multiplication from 2 tables

Let's say there are 2 tables in a SQL Server database:

table1, where prices of products are stored when the prices changes:

|Date      |Product|Price|
--------------------------
|2014-01-01|     A |   2$|
|2015-01-01|     A |   3$|

table2, where quantities of sold products are stored by date:

|Date       | Product | Quantities|
-----------------------------------
|2014-01-01 |     A   |    200    |
|2014-06-01 |     A   |    300    |
|2015-02-01 |     A   |    100    |

My question: how to calculate sales (Price x Quantities) by date for product by writing a SQL query:

|Date       | Product | Sales   |
---------------------------------
|2014-01-01 | A       |     400 |
|2014-06-01 | A       |     600 |
|2015-02-01 | A       |     300 |

Upvotes: 0

Views: 206

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I assume you want to pick up the most recent price on or before the sale. When designing such data structures, it is usually better to have an effective and end date on each record, rather than just the effective date. Alas, that is not what you have.

You can get the price using a correlated subquery or apply. Here is an example using your column and table names (and assuming that price is really stored as a number not a string):

select t2.*, (t2.quantity * p.price) as sales
from table2 t2 outer apply
     (select top 1 t1.price
      from table1 t1
      where t1.product = t2.product and t1.date <= t2.date
      order by t1.date desc
     ) p

Upvotes: 1

ASh
ASh

Reputation: 35646

select [date], product, price*quantities 
from 
(
    select 
    t2.*, t1.price ,
    ROW_NUMBER() over (partition by t2.[date], t2.product order by t1.[date] desc) as num
    from table1 t1
    join table2 t2 on  t1.[date] <= t2.[date]
) T
where T.num = 1

Upvotes: 0

Related Questions