I have a table that has the below data.
COUNTRY LEVEL NUM_OF_DUPLICATES US 9 6 US 8 24 US 7 12 US 6 20 US 5 39 US 4 81 US 3 80 US 2 430 US 1 178 US 0 430
I wrote a query that will calculate the sum of cumulative rows and got the below output .
COUNTRY LEVEL NUM_OF_DUPLICATES POOL US 9 6 6 US 8 24 30 US 7 12 42 US 6 20 62 US 5 39 101 US 4 81 182 US 3 80 262 US 2 130 392 US 1 178 570 US 0 254 824
Now I want to to filter the data and take only where the POOL <=300, if the POOL field does not have the value 300 then I should take the first value after 300. So, in the above example we do not have the value 300 in the field POOL, so we take the next immediate value after 300 which is 392. So I need a query so that I can pull the records POOL <= 392(as per the example above) which will yield me the output as
COUNTRY LEVEL NUM_OF_DUPLICATES POOL US 9 6 6 US 8 24 30 US 7 12 42 US 6 20 62 US 5 39 101 US 4 81 182 US 3 80 262 US 2 130 392
Please let me know your thoughts. Thanks in advance.
Advertisement
Answer
declare @t table(Country varchar(5), Level int, Num_of_Duplicates int) insert into @t(Country, Level, Num_of_Duplicates) values ('US', 9, 6), ('US', 8, 24), ('US', 7, 12), ('US', 6, 20), ('US', 5, 39), ('US', 4, 81), ('US', 3, 80), ('US', 2, 130/*-92*/), ('US', 1, 178), ('US', 0, 430); select *, sum(Num_of_Duplicates) over(partition by country order by Level desc), (sum(Num_of_Duplicates) over(partition by country order by Level desc)-Num_of_Duplicates) / 300 as flag,--any row which starts before 300 will have flag=0 --or case when sum(Num_of_Duplicates) over(partition by country order by Level desc)-Num_of_Duplicates < 300 then 1 else 0 end as startsbefore300 from @t; select * from ( select *, sum(Num_of_Duplicates) over(partition by country order by Level desc) as Pool from @t ) as t where Pool - Num_of_Duplicates < 300 ;