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

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

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

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

------解决方案--------------------

SQL code

declare @date datetimeset @date = '2012-07-18'select distinct dateadd(dd,-1*number,@date)from master..spt_valueswhere 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.0002012-07-09 00:00:00.0002012-07-10 00:00:00.0002012-07-11 00:00:00.0002012-07-12 00:00:00.0002012-07-13 00:00:00.0002012-07-14 00:00:00.000(7 行受影响)
------解决方案--------------------
SQL codedeclare @date datetimeset @date = '2012-07-18'set datefirst 1select  convert(varchar(8),dateadd(dd,-1*number,@date),112) as YM,datepart(dd,dateadd(dd,-1*number,@date)) as Dfrom master..spt_valueswhere datepart(week,dateadd(dd,-1*number,@date)) = datepart(week,@date) - 1and [type] = 'P' and number between 1 and 15/*YM       D-------- -----------20120715 1520120714 1420120713 1320120712 1220120711 1120120710 1020120709 9(7 行受影响)*/