How to find the price based on the given range

Question

Here i need to find the price of the qty based on which price range the qty lies. In the first date(2015/01/01) 5 quantities have been sold and we made entries from (1 to 5) ,On second day for 10 quantities, the entry should be made from (6-15) and so on.

Example : Product A will be sold in the price of $ 100 per quantity between 1-3 (qty)

The 4th qty and more will be sold in the price of $ 110.

I've written the query, but is there any way to make it simple ?

# PRODUCT #

PROD    DATE         QTY
 A |   2015-01-01  |  5
 A |   2015-02-01  | 10
 A |   2015-03-01  |  5
 A |   2015-04-01  | 12

# PRICE #

PROD |  SRANGE |  ERANGE |  PRICE
A    |    1    |  3      |   100
A    |    4    | 15      |   110
A    |   16    | 30      |   120

---

# RESULT #

PROD    DATE         QTY      PRICE
 A      2015-01-01   5     (300 +220) =520
 A      2015-02-01   10    1100
 A      2015-03-01   5     600
 A      2015-04-01   12    1500

---

Declare @Ddate as date
Set @Ddate = '2014-01-01'


select DISTINCT Top 1   case when
 (select COUNT (*) from PdtDet where ERange >=
 (select Top 1  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A' order by Qty  desc)  
  and ERange<=T0.Qty and Product ='A') =0

then (select sum(Price) from PdtDet PD where PD.Product = 'A' and
 PD.SRange >= (select Top 1 case when (select COUNT(*) from Pdt where Pdate < @Ddate and Product = 'A')  >0 then
 (select Top 1  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A' order by Qty  desc) else 1 end) and PD.SRange<=t0.Qty) 

 else
 (select Top 1 Price   from PdtDet PD where PD.Product = 'A' and
  PD.ERange >= (select  Qty +1 from Pdt where Pdate <= DATEADD(d,-1,@Ddate) and Product = 'A') and PD.ERange <= T0.Qty) 
 end 
   'Price'
     from Pdt T0 inner join PdtDet T1 ON T0.Product = T1.Product where t0.PDate = @Ddate and t1.Product = 'A'

Answer 1

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 >= pi.srange and l.n <= 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 < p1.date
    Group By p1.PROD, p1.date, p1.QTY
) as pr on pr.PROD = pi.PROD and l.n > pr.previous and l.n <= (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.