SQL - How to duplicate rows but change only one column info?

Question

The tblWeeks table has column weeknum and date (every Thursday only) Like this:

WeekNum    Date
1496       2015-7-2
1497       2015-7-9
1498       2015-7-16

The fnBenchmarkData function will return weekly data based on specified weeknum parameter.

I used cross-apply to display the benchmark data for ALL weeks:

SELECT p.weeknum, p.date, q.Key, q.ID, q.Score      
FROM tblWeeks AS p 
cross apply 
fnBenchmarkData(p.weeknum) as q

Now my problem is, the score data generate from function and weeknum will update on every Thursday and remain the same till next Wednesday, i.e., Thursday - next Wednesday is a "loop" and all score data are the same, weeknum is same too. However, the function will return Thursday only.

The cross-apply will only display every Thursday data, I want to display the rest of the weekdays, basically duplicate the rows but change the date.

For example, below is what I get from cross-apply:

WeekNum    Date              Score
1496       2015-7-2 (Thur)     1
1497       2015-7-9 (Thur)     5   
1498       2015-7-16(Thur)     7
....

I want my cross-apply SQL return something like this:

WeekNum    Date              Score
1496       2015-7-2 (Thur)     1
1496       2015-7-3            1
                                     /* No data stored for weekend 
1496       2015-7-6            1   
1496       2015-7-7            1   
1496       2015-7-8            1

1497       2015-7-9 (Thur)     5
1497       2015-7-10           5

1497       2015-7-13           5   
1497       2015-7-14           5
1497       2015-7-15           5

1498       2015-7-16(Thur)     7  

Anyone who have idea how to do that? Thanks so much!

Answer 1

I think the easiest way is to use cross join to bring in the additional rows

SELECT p.weeknum, dateadd(day, n.n, p.date) as date, q.Key, q.ID, q.Score      
FROM tblWeeks p cross apply 
     fnBenchmarkData(p.weeknum) q cross join
     (select 0 as n union all select 1 as n union all select 2 union all select 3 union all
      select 4 union all select 5 union all select 6
     ) n;