Sql 每月第二周

USE [UILogDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_jx_weekdate]    Script Date: 11/08/2011 17:57:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 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 int

AS
BEGIN
    -- 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 为日期字段