SQL How to create patterned number sequence? 1

[makeitwork09]

I have the following set of data. It is created using a sum of principal + interest to get the payment and it is grouped by the loan number, year of as of date, and quarter of as of date. The latter two are not shown.

[pre]
LOAN PAYMENT
3715142 252441
3715142 252441
3715142 252441
3715142 252441
3715142 252441
3715142 167499.55
3716354 463512
3716354 463512
3716354 463512
3716354 463512
3716354 463512
3716354 308886.17
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 204009
3726410 67946.34
[/pre]

I need a third column. It follows a specific sequence:
[ol 1]
[li]The first record for each loan is 0[/li]
[li]The format of the numbers after the first record is #.#[/li]
[li]The second part of #.# is 1 to 4[/li]
[li]The first part of #.# is the number of resets[/li]
[/ol]

Therefore I want the following:
[pre]
CUSTOM LOAN PAYMENT
0 3715142 252441
1.1 3715142 252441
1.2 3715142 252441
1.3 3715142 252441
1.4 3715142 252441
2.1 3715142 167499.55
0 3716354 463512
1.1 3716354 463512
1.2 3716354 463512
1.3 3716354 463512
1.4 3716354 463512
2.1 3716354 308886.17
0 3726410 204009
1.1 3726410 204009
1.2 3726410 204009
1.3 3726410 204009
1.4 3726410 204009
2.1 3726410 204009
2.2 3726410 204009
2.3 3726410 204009
2.4 3726410 204009
3.1 3726410 204009
3.2 3726410 204009
3.3 3726410 204009
3.4 3726410 204009
4.1 3726410 204009
4.2 3726410 204009
4.3 3726410 204009
4.4 3726410 204009
5.1 3726410 204009
5.2 3726410 67946.34
[/pre]

I tried starting with the following, but then got stuck with what to do next.

(row_number() over(partition by c.loan order by c.loan,year(c.ASOF_DATE),DATEPART(QUARTER,c.ASOF_DATE))% 5) - 1

The result was

LOAN	PAYMENT	  START
3715142	252441	  0
3715142	252441	  1
3715142	252441	  2
3715142	252441	  3
3715142	252441	 -1
3715142	167499.55 0
3716354	463512	  0
3716354	463512	  1
3716354	463512	  2
3716354	463512	  3
3716354	463512	 -1
3716354	308886.17 0
3726410	204009	  0
3726410	204009	  1
3726410	204009	  2
3726410	204009	  3
3726410	204009	 -1
3726410	204009	  0
3726410	204009	  1
3726410	204009	  2
3726410	204009	  3
3726410	204009	 -1
3726410	204009	  0
3726410	204009	  1
3726410	204009	  2
3726410	204009	  3
3726410	204009	 -1
3726410	204009	  0
3726410	204009	  1
3726410	204009	  2
3726410	67946.34  3

 

[DaveInIowa]

Once you have a row number (from either a CTE or Temp table), you can calculate the third column value with a CASE statement like...

CASE
   WHEN RowNo = 1 THEN '0'
   WHEN RowNo % 4 = 1 THEN CAST((RowNo / 4) + 0 AS VARCHAR) + '.4'
   WHEN RowNo % 4 = 0 THEN CAST((RowNo / 4) + 0 AS VARCHAR) + '.3'
   WHEN RowNo % 2 = 1 THEN CAST((RowNo / 4) + 1 AS VARCHAR) + '.2'
   WHEN RowNo % 2 = 0 THEN CAST((RowNo / 4) + 1 AS VARCHAR) + '.1'
END ThirdColumn

 

[makeitwork09]

DaveInIowa - Thank You very much! That worked nicely!