Sql 每月第二周

USE [UILogDB]GO/****** Object:  StoredProcedure [dbo].[usp_jx_weekdate]    Script Date: 11/08/2011 17:57:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:        <jianxiong>-- Create date: <2011-11-08>-- Description:    <weekdate>-- =============================================CREATE PROCEDURE [dbo].[usp_jx_weekdate]    -- Add the parameters for the stored procedure here@tablename varchar(500),@datefield varchar(500),@weeknum intASBEGIN    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    SET NOCOUNT ON;        declare @sql1 varchar(max)    declare @sql2 varchar (max)    declare @weekbegin int    declare @weekend int    declare @showfield varchar(max)    -- Insert statements for procedure here        if @weeknum=1    begin        set @weekbegin=2        set @weekend=8    end    else if @weeknum=2    begin        set @weekbegin=9        set @weekend=15    end    else if @weeknum=3    begin        set @weekbegin=16        set @weekend=22    end    else if @weeknum=4    begin        set @weekbegin=23        set @weekend=29    end    else if @weeknum=5    begin        set @weekbegin=30        set @weekend=36    end        else    begin        set @weekbegin=2        set @weekend=8    end        set @showfield='select'    select @showfield=@showfield+ ',['+name+'] ' from syscolumns where id=object_id(@tablename) order by colid    set @showfield=REPLACE(@showfield,'select,','')        set @sql1=    ('        with week as        (        select         case             when MONTH('+@datefield+')=1 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=1 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=2 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=2 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=3 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=3 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=4 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=4 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=5 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=5 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=6 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=6 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=7 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=7 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=8 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=8 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=9 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=9 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')    ')        set @sql2=    ('            when MONTH('+@datefield+')=10 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=10 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=11 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=11 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')            when MONTH('+@datefield+')=12 then (select top 1 '+@datefield+'PART(DW,'+@datefield+'ADD(day,-1,'+@datefield+')) as week from '+@tablename+' b where MONTH('+@datefield+')=12 and year(a.'+@datefield+')=year(b.'+@datefield+') order by b.date)+day('+@datefield+')                        end as week,*        from '+@tablename+' a        )        select '+@showfield+' from week where  week between '+convert(varchar(5),@weekbegin)+' and '+convert(varchar(5),@weekend)+'        ')        exec (@sql1+@sql2)END

其中

yr_view_orders_rate 为原数据表名

date 为日期字段