CTE not going through recursion

I am using CTE for the first time and am running into some difficulty. I have looked online and am trying to piece together examples.

I want to insert rows between each two rows returned to account for all the days inbetween. Row 1 has Date(A) and row 2 has Date(B). I want to insert a row for every day between A and B, where those rows all have the same values as Row 1.

If I run only my anchor definition on my test data, i get 341 rows. After running the CTE, i get 682. So it only runs once.

Any suggestions you can provide would be great. Thanks.

I have the following table schema:

field1 (varchar(10)) field2 (smalldatetime) field3 (nume开发者_开发技巧ric(18,0)) field4 (numeric(18,6)) field5 (numeric(18,6)) field6 (numeric(18,3))

An example of the input table is:

ABC  1-1-1990   0  0.1   0.1   0.125
ABC  1-5-1990   1  0.2   0.2   1.0
ABC  1-9-1990   0  0.3   0.3   0.750
ABC  1-13-1990  1  0.4   0.4   1.500

I want to turn that into this:

ABC  1-1-1990    0  0.1   0.1   0.125
ABC  1-2-1990    0  0.1   0.1   0.125
ABC  1-3-1990    0  0.1   0.1   0.125
ABC  1-4-1990    0  0.1   0.1   0.125
ABC  1-5-1990    1  0.2   0.2   1.0
ABC  1-6-1990    1  0.2   0.2   1.0
ABC  1-7-1990    1  0.2   0.2   1.0
ABC  1-8-1990    1  0.2   0.2   1.0
ABC  1-9-1990    0  0.3   0.3   0.750
ABC  1-10-1990   0  0.3   0.3   0.750
ABC  1-11-1990   0  0.3   0.3   0.750
ABC  1-12-1990   0  0.3   0.3   0.750
ABC  1-13-1990   1  0.4   0.4   1.500

Here is my current CTE:

WITH NewData (field1,field2,field3,field4,field5,field6) AS        
(        
    SELECT  m.field1,m.field2,m.field3,m.field4,m.field5,m.field6
    FROM    MyTable as m
    WHERE m.field1 is not null        
    GROUP BY m.field1,m.field2,m.field3,m.field4,m.field5,m.field6      
    UNION ALL        
    SELECT  m.field1, DATEADD(d, 1, m.field2), m.field3, m.field4, m.field5, m.field6   
    FROM MyTable as m              
)
SELECT  field1,field2,field3, field4, field5,field6    
FROM NewData
order by field1, field2
OPTION(MAXRECURSION 0)

Current Output (it misses dates 1-3-1990, 1-4-1990, 1-7-1990, 1-8-1990, 1-11-1990, 1-12-1990):

ABC  1-1-1990    0  0.1   0.1   0.125
ABC  1-2-1990    0  0.1   0.1   0.125
ABC  1-5-1990    1  0.2   0.2   1.0
ABC  1-6-1990    1  0.2   0.2   1.0
ABC  1-9-1990    0  0.3   0.3   0.750
ABC  1-10-1990   0  0.3   0.3   0.750
ABC  1-13-1990   1  0.4   0.4   1.500

---

Your CTE is not currently defined to be recursive, in that the portion you think is recursive is not since it does not reference itself - so what it does is just a normal union query (so you get more rows, making you think its is recursive, when its just a union)

http://msdn.microsoft.com/en-us/library/ms186243.aspx

WITH NewData (field1,field2,field3,field4,field5,field6) AS        
(    
SELECT  m.field1,m.field2,m.field3,m.field4,m.field5,m.field6    
FROM    MyTable as m    
WHERE m.field1 is not null            
GROUP BY m.field1,m.field2,m.field3,m.field4,m.field5,m.field6          
UNION ALL            
SELECT  m.field1, DATEADD(d, 1, m.field2), m.field3, m.field4, m.field5, m.field6       
FROM MyTable as m 
INNER JOIN NewData n on n.field1 = m.field1   
)

I am not entirely sure what join condition you want to recurse on so have just used field1 in the code example, but basically use that join to define how the rows relate.