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'
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