0

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'
  • 1
    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? – Julien Vavasseur Oct 16 '15 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 – Julien Vavasseur Oct 16 '15 at 12:51
  • 1
    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 – Sabin Bio Oct 16 '15 at 13:38
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.