Creating quintile stock portfolios with SQL -

i have sql code allow me form quintile portfolios monthly stock data. formation of quintile portfolios depend on ratio (called b/m in spreadsheet). code automatically generates each month different quintile portfolios, stocks/companies added or withdrawn month compared previous month. ratios can change following month stock can ranked in quintile.

i added printscreen briefly show how organized excel sheet. basically, sorted per month.enter image description here

note: applies ms sql 2008+.

i don't know data structure looks like, maybe along lines of

/* test data */ stocks (     /* jan = 10 = 2 per quint */     select 'abc' stockname, 100.00 stockprice, '20170101' pricedate union     select 'def' stockname, 99.00 stockprice, '20170101' pricedate union     select 'ghi' stockname, 50.00 stockprice, '20170101' pricedate union     select 'jkl' stockname, 50.00 stockprice, '20170101' pricedate union     select 'mno' stockname, 75.00 stockprice, '20170101' pricedate union     select 'pqr' stockname, 77.00 stockprice, '20170101' pricedate union     select 'stu' stockname, 20.00 stockprice, '20170101' pricedate union     select 'vwx' stockname, 10.00 stockprice, '20170101' pricedate union     select 'yz1' stockname,  2.00 stockprice, '20170101' pricedate union     select '234' stockname,  1.00 stockprice, '20170101' pricedate union      /* feb = 7 = uneven quints */     select 'abc' stockname, 1.00 stockprice, '20170201' pricedate union     select 'def' stockname, 2.00 stockprice, '20170201' pricedate union     select 'ghi' stockname, 20.00 stockprice, '20170201' pricedate union     select 'jkl' stockname, 55.00 stockprice, '20170201' pricedate union     select 'mno' stockname, 50.00 stockprice, '20170201' pricedate union     select 'pqr' stockname, 100.00 stockprice, '20170201' pricedate union     select 'stu' stockname, 90.00 stockprice, '20170201' pricedate union      /* mar = 3 = not enough 5 quints. */     select 'abc' stockname, 42.00 stockprice, '20170301' pricedate union     select 'jkl' stockname, 42.00 stockprice, '20170301' pricedate union     select 'vwx' stockname, 42.00 stockprice, '20170301' pricedate )  /* query */     select y.stockname, y.stockprice, y.pricemonth, y.quintile (     select x.stockname, x.stockprice, month(x.pricedate) pricemonth          , ntile(5) on (partition month(x.pricedate) order x.stockprice desc) quintile     stocks x     group x.stockname, x.stockprice, month(x.pricedate) ) y order y.pricemonth, y.quintile asc 

gives you

stockname  stockprice  pricemonth  quintile abc        100.00      1           1 def         99.00      1           1 pqr         77.00      1           2 mno         75.00      1           2 ghi         50.00      1           3 jkl         50.00      1           3 stu         20.00      1           4 vwx         10.00      1           4 yz1          2.00      1           5 234          1.00      1           5 pqr        100.00      2           1 stu         90.00      2           1 jkl         55.00      2           2 mno         50.00      2           2 ghi         20.00      2           3 def          2.00      2           4 abc          1.00      2           5 abc         42.00      3           1 jkl         42.00      3           2 vwx         42.00      3           3 

then when display it, can sort/group quintile.

also, example above illustrates how ntile() may not give you're looking for. may have calculate create quintiles yourself. see march group >> $42, yet got put 3 different quintiles. it's below other quintile 3 prices. check it's want.

lastly, better add date dimension table pre-calculates date parts you, , join main sub-query, whole different discussion.