# How to find the price based on the given range

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'
``````
• why output 1 and 3 (QTY = 5) are not both equal to 520? row 2 should be 1070? => 300+770 and 1290 for row 4? Oct 16, 2015 at 8:14
• are you using SQL Server? Which version? You can edit your tags and add the relevant version of your RDBMS to it. ie. something like SQL-SERVER-2008-R2 or SQL-SERVER-2012 Oct 16, 2015 at 12:51
• What about 4th position? Is this `120 price *12 qty =1440` or how to you get 1500 ? Is it is outside of boundaries what happens ? I mean , total quantity for the 4th row is 32 and is no price for it Oct 16, 2015 at 13:38

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.