請幫取出上一周的記錄，並按年月，天分開

[人生无悔]

舉例：今天2012-07-18號，則上周的記錄為
YM D
201207 9
201207 10
201207 11
201207 12
201207 13
201207 14
201207 15

說明：請不要用while循環及直接一倏倏插入,謝謝！

[AcHerat]

declare @date datetime
set @date = '2012-07-18'

select distinct dateadd(dd,-1*number,@date)
from master..spt_values
where datepart(week,dateadd(dd,-1*number,@date)) = datepart(week,@date) - 1
	and [type] = 'P' and number between 1 and 15

/***********************

-----------------------
2012-07-08 00:00:00.000
2012-07-09 00:00:00.000
2012-07-10 00:00:00.000
2012-07-11 00:00:00.000
2012-07-12 00:00:00.000
2012-07-13 00:00:00.000
2012-07-14 00:00:00.000

(7 行受影响)

[AcHerat]

declare @date datetime
set @date = '2012-07-18'

set datefirst 1
select distinct dateadd(dd,-1*number,@date)
from master..spt_values
where datepart(week,dateadd(dd,-1*number,@date)) = datepart(week,@date) - 1
	and [type] = 'P' and number between 1 and 15

/******************

-----------------------
2012-07-09 00:00:00.000
2012-07-10 00:00:00.000
2012-07-11 00:00:00.000
2012-07-12 00:00:00.000
2012-07-13 00:00:00.000
2012-07-14 00:00:00.000
2012-07-15 00:00:00.000

(7 行受影响)

[--小F--]

declare @date datetime
set @date = '2012-07-18'

set datefirst 1
select 
 convert(varchar(8),dateadd(dd,-1*number,@date),112) as YM,datepart(dd,dateadd(dd,-1*number,@date)) as D
from
 master..spt_values
where
 datepart(week,dateadd(dd,-1*number,@date)) = datepart(week,@date) - 1
and
 [type] = 'P' and number between 1 and 15


/*YM       D
-------- -----------
20120715 15
20120714 14
20120713 13
20120712 12
20120711 11
20120710 10
20120709 9

(7 行受影响)
*/

[人生无悔]

多谢两位帅哥，美女