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