sql server - Summing and then getting min and max of top 70% in SQL -

i have data purchases of product formatted this:

item  |  price  |  quantity bought  abc      10.10     4 def      8.30      12 def      7.75      8 abc      10.50     20 ghi      15.4      1 ghi      15.2      12 abc      10.25     8 ...      ...       ... 

where each row represents individual purchasing amount @ price. aggregate data , eliminate prices below 30th percentile total quantity bought table.

for example, in above data set total amount of product abc bought (4+20+8) = 32 units, average price = (4*10.10 + 8*10.25 + 20*10.50)/32 = 10.39.

i organize above data set this:

item  |  vwp   |  total vol  |  70th %ile min  |  70th %ile max abc      10.39    32            ???               ??? def      ...      20            ???               ??? ghi      ...      13            ???               ??? 

where vwp volume weighted price, , 70th %ile min/max represent minimum , maximum prices within top 70% of volume.

in other words, want eliminate prices lowest volumes until have 70% of total volume day contained in remaining prices. publish min , max price ones left in 70th %ile min/max columns.

i tried clear possible, if tough follow along please let me know parts need clarification.

note: these not columns contained in dataset, , selecting , calculating other values well. included columns relevant specific calculation.

edit:

here code far, , need incorporate calculation (the variables '@' symbol before them inputs given user:

select item,     sum(quantity) total_vol,     dateadd(day, -@dateoffset, convert(date, getdate())) buydate,    min(price) minprice,    max(price) maxprice,    max(price) - min(price) pricerange,    round(sum(price * quantity)/sum(quantity), 6) vwp,  transacttracker..custdata -- @dateoffset (number of days data offset by) -- @starttime (time start data in hours) -- @endtime (time stop data in hours)  datediff(day, tradedatetime, getdate()) = (@dateoffset+1) , datepart(hh, tradedatetime) >= @starttime , hittake = ''  or datediff(day, tradedatetime, getdate()) = @dateoffset , datepart(hh, tradedatetime) < @endtime , hittake = ''  group item 

edit 2:

from (select p.*, (select sum(quantity) transacttracker..custdata p2      p2.series = p.series , p2.size >= p.size) volcum transacttracker..custdata p ) p 

edit 3:

(case when cast(qcum float) / sum(quantity) <= 0.7 min(price) end) min70px, (case when cast(qcum float) / sum(quantity) <= 0.7 max(price) end) max70px   (select p.*,   (select sum(quantity) transacttracker..custdata p2    p2.item = p.item , p2.quantity >= p.quantity)    qcum transacttracker..custdata p) cd 

there ambiguity on how define 70 % when goes on threshold. however, challenge 2 fold. after identifying cumulative proportion, query needs choose appropriate row. suggests using row_number() selection.

this solution using sql server 2012 syntax calculates cumulative sum. takes assigns sequential value based on how close ratio 70%.

select item,        sum(price * quantity) / sum(quantity) vwp,        sum(quantity) total_vol,        min(case when seqnum = 1 price end) min70price,        max(case when seqnum = 1 price end) max70price (select p.*,              row_number() on (partition item order abs(0.7 - qcum/qtot) seqnum       (select p.*,                    sum(quantity) on (partition item order vol desc) qcum,                    sum(quantity) on (partition item) qtot             purchases p            ) p      ) p group item; 

to largest value less 70%, use:

max(case when qcum < qtot*0.7 qcum end) on (partition item) lastqcum 

and case statements in outer select be:

min(case when lastqcum = qcum price end) . .  

in earlier versions of sql server, can same effect correlated subquery:

select item,        sum(price * quantity) / sum(quantity) vwp,        sum(quantity) total_vol,        min(case when seqnum = 1 price end) min70price,        max(case when seqnum = 1 price end) max70price (select p.*,              row_number() on (partition item order abs(0.7 - qcum/qtot) seqnum       (select p.*,                    (select sum(quantity) purchases p2 p2.item = p.item , p2.quantity >= p.quantity                    ) qsum,                    sum(quantity) on (partition item) qtot             purchases p            ) p      ) p group item 

here example code:

select item,     sum(quantity) total_vol,     dateadd(day, -@dateoffset, convert(date, getdate())) buydate,    min(price) minprice,    max(price) maxprice,    max(price) - min(price) pricerange,    round(sum(price * quantity)/sum(quantity), 6) vwp,    min(case when seqnum = 1 price end) min70price,    max(case when seqnum = 1 price end) max70price (select p.*,              row_number() on (partition item order abs(0.7 - qcum/qtot) seqnum       (select p.*,                    (select sum(quantity) transacttracker..custdata p2 p2.item = p.item , p2.quantity >= p.quantity                    ) qsum,                    sum(quantity) on (partition item) qtot             purchases transacttracker..custdata            ) p      ) cd -- @dateoffset (number of days data offset by) -- @starttime (time start data in hours) -- @endtime (time stop data in hours)  datediff(day, tradedatetime, getdate()) = (@dateoffset+1) , datepart(hh, tradedatetime) >= @starttime , hittake = ''  or datediff(day, tradedatetime, getdate()) = @dateoffset , datepart(hh, tradedatetime) < @endtime , hittake = ''  group item