How to update the value (number) by the respective quintile number in a table by using the NTILE function ? (Transact-SQL)

Transact-SQL
,
1

Abstract: I am trying to rank these stocks factors by top quintile and bottom quintile to build a long/short portfolio..

Here is the table:

momentumntile.PNG697×388 6.76 KB

I have been trying this code:

                 DECLARE  @CDate Date


                 SET @CDate = '2006-12-30'  -- dates as string must be yyyy-mm-dd

                 WHILE @CDate < '2016-12-30'
                         BEGIN
	
			                 With cte
			                 as(
			                 select *, NTILE(5) OVER (order by @CDate desc) as ntile
		                  from
		                  MOMENTUM_Quintile
		                  )
		                  update cte 
		                  set @CDate =ntile

	              SET @CDate = DATEADD(mm, 1, @CDate) 
	              WHILE Month(@CDate) = Month(DATEADD(dd, 1, @CDate))
		            BEGIN
			             SET @CDate = DATEADD(dd, 1, @CDate)
		                END
                     END

To reach this result

But it just does not gives me what I want.. looking for some insight for this

2

Can you provide your input (or a sample of it) table in consumable format? The idea is, I want to play with your code but don't want to re-type the table data

3 
                 CREATE TABLE [dbo].[MOMENTUM_quintile_example](
[MNEMONIC] [varchar](50) NULL,
[2006-12-30] [money] NULL,
[2007-01-30] [money] NULL,
[2007-02-28] [money] NULL,
[2007-03-30] [money] NULL,
[2007-04-30] [money] NULL,
[2007-05-30] [money] NULL,
[2007-06-30] [money] NULL,
[2007-07-30] [money] NULL,
[2007-08-30] [money] NULL,
[2007-09-30] [money] NULL,
[2007-10-30] [money] NULL,
[2007-11-30] [money] NULL,
[2007-12-30] [money] NULL,
[2008-01-30] [money] NULL,
[2008-02-29] [money] NULL,
[2008-03-30] [money] NULL,
[2008-04-30] [money] NULL,
[2008-05-30] [money] NULL,
[2008-06-30] [money] NULL

        ) ON [PRIMARY]

         GO

                     INSERT INTO momentum_quintile_example (MNEMONIC,[2006-12-30],  [2007-01-30], [2007-02-28],  [2007-03-30],  [2007-04-30],  [2007-05-30], [2007-06-30])
                      VALUES ('AAL', 510.1708, 489.0758, 494.8104, 557.2972, 555.6318, 606.8441, 613.0896)
                      VALUES ('ABF', -6.5341	-6.4193	-6.5301	-6.8388	-7.3335	-7.385 -7.0781 )
                       VALUES ('ABC',	1.4600,	1.5030,	1.5352,	1.5609,	1.5447,	1.5609,	1.7493);
4

Is it ok like that?

5

Yup, that's fine.

Tell me, what are you trying to accomplish here:

set @CDate =ntile

That's invalid syntax of course. NTILE returns bigint, not date. Also, maybe think up a different alias. It's confusing when you use a built-in function name like ntile for a column alias.

6

I am trying to replace each value of every column into a quintile number.. that's why I am using the NTILE function;

The while function is a "try" to loop through each column so I don't have to write them down one by one

7

I hear you. So, given a an arbitrary ntile value, say, 42, what should the new value of @cdate be? (It has to be a date)

8

okay... I still don't have any clue how to do this.. (loop) ! and thanks for your help by the way..