How to find the price based on the given range
I don't get the same output but I think there is an error in your sample with the last row (=1500) and/or a price for QTY>30 is missing.

You can look at this SQL Fiddle and SQL Server query:

With list as (
    Select n = ROW_NUMBER() over(order by (select 1)) From (
        Select n = 1 From (values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
    ) as x
)
Select pr.PROD, pr.[date], QTY = count(l.n), Price = sum(pi.price) From list as l
Inner Join Price pi on l.n &gt;= pi.srange and l.n &lt;= pi.erange
Inner Join (
    Select p1.PROD, p1.date, previous = coalesce(SUM(p2.QTY), 0), p1.QTY
    From Product as p1
    Left Join Product  as p2 on p1.PROD = p2.PROD and p2.date &lt; p1.date
    Group By p1.PROD, p1.date, p1.QTY
) as pr on pr.PROD = pi.PROD and l.n &gt; pr.previous and l.n &lt;= (pr.previous+pr.QTY)
Group By pr.PROD, pr.date


Output:

PROD | date         | QTY   | Price
A    | 2015-01-01   | 5     | 520 
A    | 2015-02-01   | 10    | 1100
A    | 2015-03-01   | 5     | 600
A    | 2015-04-01   | 10    | 1200


With 1 more row in Price such as ('A', 31, 50, 150), it gives the output you want:

PROD | date         | QTY   | Price
A    | 2015-01-01   | 5     | 520 
A    | 2015-02-01   | 10    | 1100
A    | 2015-03-01   | 5     | 600
A    | 2015-04-01   | 10    | 1500


SQL Fiddle with Price > 30.