i wants to distribute Price, price distribution is on base of student rank from table tblWinPriceRange.
There is two student have a first rank ,so that fall in range 1 in table tblWinPriceRange so each student have price is 25000.
on first rank 2 student so i want result sum of rank 1 and 2 in table tblWinPriceRange and divided by 2. so price for each student of first rank is (25000+12500)/2 = 18750 each for first rank.
DECLARE @tblWinPriceRange AS TABLE (
AutoID INT IDENTITY
,FromRank INT
,ToRank INT
,Price Money )
DECLARE @tblWinStudents AS TABLE (
AutoID INT IDENTITY
,StudentID INT ,Score INT
,Rank INT )
INSERT INTO @tblWinPriceRange (
FromRank
,ToRank
,Price )
SELECT 1, 1,25000 UNION ALL SELECT 2, 2,12500 UNION ALL
SELECT 3, 3,7500 UNION ALL SELECT 4, 10,1500 UNION ALL
SELECT 11, 30,600
INSERT INTO @tblWinStudents (
StudentID
,Score
,Rank )
SELECT 42,767.00,1 UNION ALL SELECT 48,767.00,1 UNION ALL
SELECT 55,616.00,3 UNION ALL SELECT 9,547.00,4 UNION ALL
SELECT 6,526.00,5 UNION ALL SELECT 49,491.00,6 UNION ALL
SELECT 54,478.00,7 UNION ALL SELECT 45,471.00,8 UNION ALL
SELECT 33,471.00,8 UNION ALL SELECT 17,470.00,10 UNION ALL
SELECT 56,419.00,11 UNION ALL SELECT 13,416.00,12 UNION ALL
SELECT 51,404.00,13 UNION ALL SELECT 52,404.00,13 UNION ALL
SELECT 46,398.00,15 UNION ALL SELECT 36,398.00,15 UNION ALL
SELECT 38,390.00,17 UNION ALL SELECT 44,390.00,17 UNION ALL
SELECT 32,362.00,19 UNION ALL SELECT 35,356.00,20 UNION ALL
SELECT 37,352.00,21 UNION ALL SELECT 34,351.00,22 UNION ALL
SELECT 57,334.00,23 UNION ALL SELECT 31,322.00,24 UNION ALL
SELECT 30,317.00,25 UNION ALL SELECT 7,302.00,26 UNION ALL
SELECT 14,285.00,27 UNION ALL SELECT 39,215.00,28 UNION ALL
SELECT 64,164.00,29 UNION ALL SELECT 61,162.00,30 UNION ALL
SELECT 62,162.00,30 UNION ALL SELECT 26,126.00,32 UNION ALL
SELECT 20,120.00,33 UNION ALL SELECT 8,108.00,34 UNION ALL
SELECT 60,108.00,34 UNION ALL SELECT 4,104.00,36 UNION ALL
SELECT 12,95.00,37
SELECT * FROM @tblWinPriceRange
SELECT * FROM @tblWinStudents
