CODEWITHSUNDEEP
sqlsql-server
weskpga
weskpga

Reputation: 2087

Getting Min and Max based on calculated values in a table

I am selecting some rows from a temporary table, #t1, that I have created:

SELECT FutContract, 
    Quantity, 
    Date, 
    Price, 
    TotalQuantity,
    PercentOfTotalQ, 
    CumulativePercentile=
        (SELECT ROUND(SUM(PercentOfTotalVol),2) FROM #t1 b
        WHERE b.OvernightVol <= a.OvernightVol AND b.FutContract = a.FutContract)

FROM #t1 a

I would like to create two more rows that represent the MIN(Price) and MAX(Price) for which CumulativePercentile is greater than 0.3 (30th percentile), but the only ways that I can think of doing it involve creating another temporary table. I would prefer not to have to, if possible. Any ideas?

EDIT:

;WITH z AS
(SELECT FutContract, OvernightVol, MorningDate, Price, TotalVol,
    PercentOfTotalVol, CumulativePercentile=
    (SELECT ROUND(SUM(PercentOfTotalVol),2) FROM #t1 b
    WHERE b.OvernightVol <= a.OvernightVol AND b.FutContract = a.FutContract) FROM #t1 a)

    SELECT *, 
        (SELECT MIN(Price) OVER(PARTITION BY FutContract) FROM z WHERE CumulativePercentile > 0.3) AS min70px,
        (SELECT MAX(Price) OVER(PARTITION BY FutContract) FROM z WHERE CumulativePercentile > 0.3) AS max70px
FROM z

Upvotes: 1

Views: 926

Answers (1)

iruvar
iruvar

Reputation: 23364

if you are on SQL Server 2005 or higher, a CTE should help

;with z as 
(SELECT FutContract, 
    Quantity, 
    Date, 
    Price, 
    TotalQuantity,
    PercentOfTotalQ, 
    CumulativePercentile=
        (SELECT ROUND(SUM(PercentOfTotalVol),2) FROM #t1 b
        WHERE b.OvernightVol <= a.OvernightVol AND b.FutContract = a.FutContract)

FROM #t1 a
)
select 
(select min(price) from z Z1 where Z1.CumulativePercentile > 0.3 and Z1.FutContract = z.FutContract) min_price,
(select max(price) from z Z1 where Z1.CumulativePercentile > 0.3 and Z1.FutContract = z.FutContract) max_price,
*
from z

Upvotes: 4

Related Questions