blogjava's web log

****************************************************************************************
第一章

****************************************************************************************


##########################################################################################
设置内存选项

##########################################################################################

-- 设置 min server memory 配置项
EXEC  sp_configure N ' min server memory (MB) ' ,  0

-- 设置 max server memory 配置项
EXEC  sp_configure N ' max server memory (MB) ' ,  256

-- 使更新生效
RECONFIGURE   WITH  OVERRIDE
##########################################################################################
调整tempdb数据库的文件属性
##########################################################################################
-- A. 将tempdb数据库的主数据文件大小设置为10MB。
ALTER   DATABASE  tempdb
MODIFY  FILE (
    name = tempdev,
    size = 100  MB)
GO

-- B. 将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。
-- 移动主数据文件
ALTER   DATABASE  tempdb MODIFY  FILE
  ( NAME = ' tempdev ' ,
   FILENAME  = ' d:\tempdb.mdf ' )

-- 添加次要数据文件
ALTER   DATABASE  tempdb  ADD   FILE
  ( NAME = ' tempdata_1 ' ,
   FILENAME  = ' d:\tempdb_data_1.ndf ' )

##########################################################################################
使用文件及文件组
##########################################################################################

/**/ /* --功能说明

    下面的代码是在SQL Server 2000上创建名为 MyDB 的数据库
    该数据库包括1个主要数据文件、3个用户定义的文件组和1个日志文件
    ALTER DATABASE语句将用户定义文件组指定为默认文件组。
    之后，通过指默认的文件组来创建表，并且将图像数据和索引放到指定的文件组中。
    最后，将文件组中的指定数据文件删除
-- */

-- 切换到 master 数据库
USE  master
GO

-- A. 创建数据库 MyDB
CREATE   DATABASE  MyDB
ON   PRIMARY                             -- 主文件组和主要数据文件
  ( NAME = ' MyDB_Primary ' ,
   FILENAME =   ' c:\MyDB_Prm.mdf ' ),
FILEGROUP MyDB_FG1                    -- 用户定义文件组1
  ( NAME  =   ' MyDB_FG1_Dat1 ' ,
   FILENAME  =   ' c:\MyDB_FG1_1.ndf ' ),   -- 次要数据文件1
  ( NAME  =   ' MyDB_FG1_Dat2 ' ,
   FILENAME  =   ' d:\MyDB_FG1_2.ndf ' ),   -- 次要数据文件2
FILEGROUP MyDB_FG2                    -- 用户定义文件组2
  ( NAME  =   ' MyDB_FG1_Dat ' ,
   FILENAME  =   ' e:\MyDB_FG2.ndf ' )      -- 次要数据文件
LOG   ON                                 -- 日志文件
  ( NAME = ' MyDB_log ' ,
   FILENAME  = ' d:\MyDB.ldf ' )
GO

-- B. 修改默认数据文件组
ALTER   DATABASE  MyDB MODIFY FILEGROUP MyDB_FG1  DEFAULT
GO

-- 切换到新建的数据库 MyDB
USE  MyDB

-- C. 在默认文件组MyDB_FG1创建表,并且指定图像数据保存在用户定义文件组MMyDB_FG2
CREATE   TABLE  MyTable
  ( cola    int     PRIMARY   KEY  ,
    colb    char ( 8 ) ,
    colc    image  )
    TEXTIMAGE_ON MyDB_FG2

-- 在用户定义文件组MyDB_FG2上创建索引
CREATE   INDEX  IX_MyTable  ON  MyTable(cola)  ON  MyDB_FG2
GO


-- D. 将要删除数据文件MyDB_FG1_Dat1上的数据转移到其他数据文件中，并且清空数据文件MyDB_FG1_Dat1
DBCC  SHRINKFILE(MyDB_FG1_Dat1,EMPTYFILE)
-- 删除数据文件MyDB_FG1_Dat1
ALTER   DATABASE  MyDB REMOVE  FILE  MyDB_FG1_Dat1




****************************************************************************************
第二章

****************************************************************************************


##########################################################################################
日期概念理解中的一些测试
##########################################################################################
-- A. 测试 datetime 精度问题
DECLARE   @t   TABLE (date  char ( 21 ))
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.000 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.001 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.009 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.002 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.003 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.004 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.005 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.006 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.007 '
INSERT   @t   SELECT   ' 1900-1-1 00:00:00.008 '
SELECT  date,转换后的日期 = CAST (date  as   datetime )  FROM   @t

/**/ /* --结果

date                  转换后的日期
--------------------- --------------------------
1900-1-1 00:00:00.000 1900-01-01 00:00:00.000
1900-1-1 00:00:00.001 1900-01-01 00:00:00.000
1900-1-1 00:00:00.009 1900-01-01 00:00:00.010
1900-1-1 00:00:00.002 1900-01-01 00:00:00.003
1900-1-1 00:00:00.003 1900-01-01 00:00:00.003
1900-1-1 00:00:00.004 1900-01-01 00:00:00.003
1900-1-1 00:00:00.005 1900-01-01 00:00:00.007
1900-1-1 00:00:00.006 1900-01-01 00:00:00.007
1900-1-1 00:00:00.007 1900-01-01 00:00:00.007
1900-1-1 00:00:00.008 1900-01-01 00:00:00.007

（所影响的行数为 10 行）
-- */
GO

-- B. 对于 datetime 类型的纯日期和时间的十六进制表示
DECLARE   @dt   datetime

-- 单纯的日期
SET   @dt = ' 1900-1-2 '
SELECT   CAST ( @dt   as   binary ( 8 ))
-- 结果: 0x0000000100000000

-- 单纯的时间
SET   @dt = ' 00:00:01 '
SELECT   CAST ( @dt   as   binary ( 8 ))
-- 结果: 0x000000000000012C
GO

-- C. 对于 smalldatetime 类型的纯日期和时间的十六进制表示
DECLARE   @dt   smalldatetime

-- 单纯的日期
SET   @dt = ' 1900-1-2 '
SELECT   CAST ( @dt   as   binary ( 4 ))
-- 结果: 0x00010000

-- 单纯的时间
SET   @dt = ' 00:10 '
SELECT   CAST ( @dt   as   binary ( 4 ))
-- 结果: 0x0000000A




##########################################################################################
CONVERT在日期转换中的使用示例
##########################################################################################
-- 字符转换为日期时,Style的使用

-- 1. Style=101时,表示日期字符串为:mm/dd/yyyy格式
SELECT   CONVERT ( datetime , ' 11/1/2003 ' , 101 )
-- 结果:2003-11-01 00:00:00.000

-- 2. Style=101时,表示日期字符串为:dd/mm/yyyy格式
SELECT   CONVERT ( datetime , ' 11/1/2003 ' , 103 )
-- 结果:2003-01-11 00:00:00.000


/**/ /* == 日期转换为字符串 == */
DECLARE   @dt   datetime
SET   @dt = ' 2003-1-11 '

-- 1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式
SELECT   CONVERT ( varchar , @dt , 101 )
-- 结果:01/11/2003

-- 2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式
SELECT   CONVERT ( varchar , @dt , 103 )
-- 结果:11/01/2003


/**/ /* == 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 == */
SELECT   CONVERT ( varchar , ' 2003-1-11 ' , 101 )
-- 结果:2003-1-11



##########################################################################################
SET  DATEFORMAT对日期处理的影响
##########################################################################################
-- 1.
/**/ /* --说明
    SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响
    但不影响明确指定了style的CONVERT处理。
-- */

-- 示例 ，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了style，转换结果受style的影响。
-- 设置输入日期顺序为 日/月/年
SET  DATEFORMAT DMY

-- 不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响
SELECT   CONVERT ( datetime , ' 2-1-2005 ' )
-- 结果: 2005-01-02 00:00:00.000

-- 指定Style参数的CONVERT转换不受SET DATEFORMAT的影响
SELECT   CONVERT ( datetime , ' 2-1-2005 ' , 101 )
-- 结果: 2005-02-01 00:00:00.000
GO

-- 2.
/**/ /* --说明

    如果输入的日期包含了世纪部分，则对日期进行解释处理时
    年份的解释不受SET DATEFORMAT设置的影响。
-- */

-- 示例，在下面的代码中，同样的SET DATEFORMAT设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。
DECLARE   @dt   datetime

-- 设置SET DATEFORMAT为:月日年
SET  DATEFORMAT MDY

-- 输入的日期中指定世纪部分
SET   @dt = ' 01-2002-03 '
SELECT   @dt
-- 结果: 2002-01-03 00:00:00.000

-- 输入的日期中不指定世纪部分
SET   @dt = ' 01-02-03 '
SELECT   @dt
-- 结果: 2003-01-02 00:00:00.000
GO

-- 3.
/**/ /* --说明

    如果输入的日期不包含日期分隔符，那么SQL Server在对日期进行解释时
    将忽略SET DATEFORMAT的设置。
-- */

-- 示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SET DATEFORMAT设置下，其解释的结果是一样的。
DECLARE   @dt   datetime

-- 设置SET DATEFORMAT为:月日年
SET  DATEFORMAT MDY
SET   @dt = ' 010203 '
SELECT   @dt
-- 结果: 2001-02-03 00:00:00.000

-- 设置SET DATEFORMAT为:日月年
SET  DATEFORMAT DMY
SET   @dt = ' 010203 '
SELECT   @dt
-- 结果: 2001-02-03 00:00:00.000

-- 输入的日期中包含日期分隔符
SET   @dt = ' 01-02-03 '
SELECT   @dt
-- 结果: 2003-02-01 00:00:00.000



##########################################################################################
  SET  LANGUAGE对日期处理的影响示例
##########################################################################################
-- 以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。
USE  master

-- 设置会话的语言环境为: English
SET  LANGUAGE N ' English '
SELECT  
     DATENAME ( Month , GETDATE ())  AS   [ Month ] ,
     DATENAME (Weekday, GETDATE ())  AS   [ Weekday ] ,
     CONVERT ( varchar , GETDATE (), 109 )  AS   [ CONVERT ]
/**/ /* --结果:
Month    Weekday   CONVERT
------------- -------------- -------------------------------
March    Tuesday   Mar 15 2005  8:59PM
-- */

-- 设置会话的语言环境为: 简体中文
SET  LANGUAGE N ' 简体中文 '
SELECT  
     DATENAME ( Month , GETDATE ())  AS   [ Month ] ,
     DATENAME (Weekday, GETDATE ())  AS   [ Weekday ] ,
     CONVERT ( varchar , GETDATE (), 109 )  AS   [ CONVERT ]
/**/ /* --结果
Month    Weekday    CONVERT
------------- --------------- -----------------------------------------
05       星期四     05 19 2005  2:49:20:607PM
-- */



##########################################################################################
日期格式化处理
##########################################################################################
DECLARE   @dt   datetime
SET   @dt = GETDATE ()

-- 1．短日期格式：yyyy-m-d
SELECT   REPLACE ( CONVERT ( varchar ( 10 ), @dt , 120 ),N ' -0 ' , ' - ' )

-- 2．长日期格式：yyyy年mm月dd日 
-- A. 方法1 
SELECT   STUFF ( STUFF ( CONVERT ( char ( 8 ), @dt , 112 ), 5 , 0 ,N ' 年 ' ), 8 , 0 ,N ' 月 ' ) + N ' 日 '
-- B. 方法2 
SELECT   DATENAME ( Year , @dt ) + N ' 年 ' + DATENAME ( Month , @dt ) + N ' 月 ' + DATENAME ( Day , @dt ) + N ' 日 '

-- 3．长日期格式：yyyy年m月d日
SELECT   DATENAME ( Year , @dt ) + N ' 年 ' + CAST ( DATEPART ( Month , @dt )  AS   varchar ) + N ' 月 ' + DATENAME ( Day , @dt ) + N ' 日 '

-- 4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
SELECT   CONVERT ( char ( 11 ), @dt , 120 ) + CONVERT ( char ( 12 ), @dt , 114 )




##########################################################################################
 日期推算处理
##########################################################################################
DECLARE   @dt   datetime
SET   @dt = GETDATE ()

DECLARE   @number   int
SET   @number = 3

-- 1．指定日期该年的第一天或最后一天
-- A. 年的第一天
SELECT   CONVERT ( char ( 5 ), @dt , 120 ) + ' 1-1 '

-- B. 年的最后一天
SELECT   CONVERT ( char ( 5 ), @dt , 120 ) + ' 12-31 '


-- 2．指定日期所在季度的第一天或最后一天
-- A. 季度的第一天
SELECT   CONVERT ( datetime ,
     CONVERT ( char ( 8 ),
         DATEADD ( Month ,
             DATEPART (Quarter, @dt ) * 3 - Month ( @dt ) - 2 ,
             @dt ),
         120 ) + ' 1 ' )

-- B. 季度的最后一天（CASE判断法）
SELECT   CONVERT ( datetime ,
     CONVERT ( char ( 8 ),
         DATEADD ( Month ,
             DATEPART (Quarter, @dt ) * 3 - Month ( @dt ),
             @dt ),
         120 )
     + CASE   WHEN   DATEPART (Quarter, @dt )  in ( 1 , 4 )
         THEN   ' 31 ' ELSE   ' 30 '   END )

-- C. 季度的最后一天（直接推算法）
SELECT   DATEADD ( Day , - 1 ,
     CONVERT ( char ( 8 ),
         DATEADD ( Month ,
             1 + DATEPART (Quarter, @dt ) * 3 - Month ( @dt ),
             @dt ),
         120 ) + ' 1 ' )


-- 3．指定日期所在月份的第一天或最后一天
-- A. 月的第一天
SELECT   CONVERT ( datetime , CONVERT ( char ( 8 ), @dt , 120 ) + ' 1 ' )

-- B. 月的最后一天
SELECT   DATEADD ( Day , - 1 , CONVERT ( char ( 8 ), DATEADD ( Month , 1 , @dt ), 120 ) + ' 1 ' )

-- C. 月的最后一天（容易使用的错误方法）
SELECT   DATEADD ( Month , 1 , DATEADD ( Day , - DAY ( @dt ), @dt ))


-- 4．指定日期所在周的任意一天
SELECT   DATEADD ( Day , @number - DATEPART (Weekday, @dt ), @dt )


-- 5．指定日期所在周的任意星期几
-- A.  星期天做为一周的第1天
SELECT   DATEADD ( Day , @number - ( DATEPART (Weekday, @dt ) + @@DATEFIRST - 1 ) % 7 , @dt )

-- B.  星期一做为一周的第1天
SELECT   DATEADD ( Day , @number - ( DATEPART (Weekday, @dt ) + @@DATEFIRST - 2 ) % 7 - 1 , @dt )




##########################################################################################
特殊日期加减函数
##########################################################################################

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_DateADD] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
     drop   function   [ dbo ] . [ f_DateADD ]
GO

/**/ /* --特殊日期加减函数

    对于日期指定部分的加减，使用DATEADD函数就可以轻松实现。
    在实际的处理中，还有一种比较另类的日期加减处理
    就是在指定的日期中，加上（或者减去）多个日期部分
    比如将2005年3月11日，加上1年3个月11天2小时。
    对于这种日期的加减处理，DATEADD函数的力量就显得有点不够。

    本函数实现这样格式的日期字符串加减处理：
    y-m-d h:m:s.m | -y-m-d h:m:s.m
    说明：
    要加减的日期字符输入方式与日期字符串相同。日期与时间部分用空格分隔
    最前面一个字符如果是减号（-）的话，表示做减法处理，否则做加法处理。
    如果日期字符只包含数字，则视为日期字符中，仅包含天的信息。
-- */

/**/ /* --调用示例

    SELECT dbo.f_DateADD(GETDATE(),'11:10')
-- */

CREATE   FUNCTION  dbo.f_DateADD(
@Date       datetime ,
@DateStr     varchar ( 23 )
) RETURNS   datetime
AS
BEGIN
     DECLARE   @bz   int , @s   varchar ( 12 ), @i   int

     IF   @DateStr   IS   NULL   OR   @Date   IS   NULL  
         OR ( CHARINDEX ( ' . ' , @DateStr ) > 0
             AND   @DateStr   NOT   LIKE   ' %[:]%[:]%.% ' )
         RETURN ( NULL )
     IF   @DateStr = ''   RETURN ( @Date )

     SELECT   @bz = CASE  
             WHEN   LEFT ( @DateStr , 1 ) = ' - '   THEN   - 1
             ELSE   1   END ,
         @DateStr = CASE  
             WHEN   LEFT ( @Date , 1 ) = ' - '  
             THEN   STUFF ( RTRIM ( LTRIM ( @DateStr )), 1 , 1 , '' )
             ELSE   RTRIM ( LTRIM ( @DateStr ))  END

     IF   CHARINDEX ( '   ' , @DateStr ) > 1
         OR   CHARINDEX ( ' - ' , @DateStr ) > 1
         OR ( CHARINDEX ( ' . ' , @DateStr ) = 0
             AND   CHARINDEX ( ' : ' , @DateStr ) = 0 )
     BEGIN
         SELECT   @i = CHARINDEX ( '   ' , @DateStr + '   ' )
            , @s = REVERSE ( LEFT ( @DateStr , @i - 1 )) + ' - '
            , @DateStr = STUFF ( @DateStr , 1 , @i , '' )
            , @i = 0
         WHILE   @s > ''   and   @i < 3
             SELECT   @Date = CASE   @i
                     WHEN   0   THEN   DATEADD ( Day , @bz * REVERSE ( LEFT ( @s , CHARINDEX ( ' - ' , @s ) - 1 )), @Date )
                     WHEN   1   THEN   DATEADD ( Month , @bz * REVERSE ( LEFT ( @s , CHARINDEX ( ' - ' , @s ) - 1 )), @Date )
                     WHEN   2   THEN   DATEADD ( Year , @bz * REVERSE ( LEFT ( @s , CHARINDEX ( ' - ' , @s ) - 1 )), @Date )
                 END ,
                 @s = STUFF ( @s , 1 , CHARINDEX ( ' - ' , @s ), '' ),
                 @i = @i + 1                 
     END
     IF   @DateStr > ''
     BEGIN
         IF   CHARINDEX ( ' . ' , @DateStr ) > 0
             SELECT   @Date = DATEADD (Millisecond
                    , @bz * STUFF ( @DateStr , 1 , CHARINDEX ( ' . ' , @DateStr ), '' ),
                     @Date ),
                 @DateStr = LEFT ( @DateStr , CHARINDEX ( ' . ' , @DateStr ) - 1 ) + ' : ' ,
                 @i = 0
         ELSE
             SELECT   @DateStr = @DateStr + ' : ' , @i = 0
         WHILE   @DateStr > ''   and   @i < 3
             SELECT   @Date = CASE   @i
                     WHEN   0   THEN   DATEADD (Hour, @bz * LEFT ( @DateStr , CHARINDEX ( ' : ' , @DateStr ) - 1 ), @Date )
                     WHEN   1   THEN   DATEADD (Minute, @bz * LEFT ( @DateStr , CHARINDEX ( ' : ' , @DateStr ) - 1 ), @Date )
                     WHEN   2   THEN   DATEADD (Second, @bz * LEFT ( @DateStr , CHARINDEX ( ' : ' , @DateStr ) - 1 ), @Date )
                 END ,
                 @DateStr = STUFF ( @DateStr , 1 , CHARINDEX ( ' : ' , @DateStr ), '' ),
                 @i = @i + 1
     END

     RETURN ( @Date )
END
GO



##########################################################################################
查询指定日期段内过生日的人员
##########################################################################################

-- 测试数据
DECLARE   @t   TABLE (ID  int ,Name  varchar ( 10 ),Birthday  datetime )
INSERT   @t   SELECT   1 , ' aa ' , ' 1999-01-01 '
UNION   ALL   SELECT   2 , ' bb ' , ' 1996-02-29 '
UNION   ALL   SELECT   3 , ' bb ' , ' 1934-03-01 '
UNION   ALL   SELECT   4 , ' bb ' , ' 1966-04-01 '
UNION   ALL   SELECT   5 , ' bb ' , ' 1997-05-01 '
UNION   ALL   SELECT   6 , ' bb ' , ' 1922-11-21 '
UNION   ALL   SELECT   7 , ' bb ' , ' 1989-12-11 '

DECLARE   @dt1   datetime , @dt2   datetime

-- 查询 2003-12-05 至 2004-02-28 生日的记录
SELECT   @dt1 = ' 2003-12-05 ' , @dt2 = ' 2004-02-28 '
SELECT   *   FROM   @t
WHERE   DATEADD ( Year , DATEDIFF ( Year ,Birthday, @dt1 ),Birthday)
         BETWEEN   @dt1   AND   @dt2
     OR   DATEADD ( Year , DATEDIFF ( Year ,Birthday, @dt2 ),Birthday)
         BETWEEN   @dt1   AND   @dt2
/**/ /* --结果
ID         Name       Birthday
---------------- ---------------- --------------------------
1           aa         1999-01-01 00:00:00.000
7           bb         1989-12-11 00:00:00.000
-- */

-- 查询 2003-12-05 至 2006-02-28 生日的记录
SET   @dt2 = ' 2006-02-28 '
SELECT   *   FROM   @t
WHERE   DATEADD ( Year , DATEDIFF ( Year ,Birthday, @dt1 ),Birthday)
         BETWEEN   @dt1   AND   @dt2
     OR   DATEADD ( Year , DATEDIFF ( Year ,Birthday, @dt2 ),Birthday)
         BETWEEN   @dt1   AND   @dt2
/**/ /* --查询结果
ID         Name       Birthday
---------------- ----------------- --------------------------
1           aa         1999-01-01 00:00:00.000
2           bb         1996-02-29 00:00:00.000
7           bb         1989-12-11 00:00:00.000
-- */



##########################################################################################
生成日期列表的函数
##########################################################################################

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_getdate] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_getdate ]
GO

/**/ /* --生成日期列表
    
    生成指定年份的工作日/休息日列表

--邹建 2003.12(引用请保留此信息)-- */

/**/ /* --调用示例

    --查询 2003 年的工作日列表
    SELECT * FROM dbo.f_getdate(2003,0)
    
    --查询 2003 年的休息日列表
    SELECT * FROM dbo.f_getdate(2003,1)

    --查询 2003 年全部日期列表
    SELECT * FROM dbo.f_getdate(2003,NULL)
-- */
CREATE   FUNCTION  dbo.f_getdate(
@year   int ,     -- 要查询的年份
@bz   bit         -- @bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
) RETURNS   @re   TABLE (id  int   identity ( 1 , 1 ),Date  datetime ,Weekday  nvarchar ( 3 ))
AS
BEGIN
     DECLARE   @tb   TABLE (ID  int   IDENTITY ( 0 , 1 ),Date  datetime )
     INSERT   INTO   @tb (Date)  SELECT   TOP   366   DATEADD ( Year , @YEAR - 1900 , ' 1900-1-1 ' )
     FROM  sysobjects a ,sysobjects b
     UPDATE   @tb   SET  Date = DATEADD ( DAY ,id,Date)
     DELETE   FROM   @tb   WHERE  Date > DATEADD ( Year , @YEAR - 1900 , ' 1900-12-31 ' )
    
     IF   @bz = 0
         INSERT   INTO   @re (Date,Weekday)
         SELECT  Date, DATENAME (Weekday,Date)
         FROM   @tb
         WHERE  ( DATEPART (Weekday,Date) + @@DATEFIRST - 1 ) % 7   BETWEEN   1   AND   5
     ELSE   IF   @bz = 1
         INSERT   INTO   @re (Date,Weekday)
         SELECT  Date, DATENAME (Weekday,Date)
         FROM   @tb
         WHERE  ( DATEPART (Weekday,Date) + @@DATEFIRST - 1 ) % 7   IN  ( 0 , 6 )
     ELSE
         INSERT   INTO   @re (Date,Weekday)
         SELECT  Date, DATENAME (Weekday,Date)
         FROM   @tb
        
     RETURN
END
GO


/**/ /* ==================================================================== */

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_getdate] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_getdate ]
GO

/**/ /* --生成列表

    生成指定日期段的日期列表

--邹建 2005.03(引用请保留此信息)-- */

/**/ /* --调用示例

    --查询工作日
    SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
    
    --查询休息日
    SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)
    
    --查询全部日期
    SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
-- */

CREATE   FUNCTION  dbo.f_getdate(
@begin_date   Datetime ,   -- 要查询的开始日期
@end_date   Datetime ,     -- 要查询的结束日期
@bz   bit                  -- @bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
) RETURNS   @re   TABLE (id  int   identity ( 1 , 1 ),Date  datetime ,Weekday  nvarchar ( 3 ))
AS
BEGIN
     DECLARE   @tb   TABLE (ID  int   IDENTITY ( 0 , 1 ),a  bit )
     INSERT   INTO   @tb (a)  SELECT   TOP   366   0
     FROM  sysobjects a ,sysobjects b
    
     IF   @bz = 0
         WHILE   @begin_date <= @end_date
         BEGIN
             INSERT   INTO   @re (Date,Weekday)
             SELECT  Date, DATENAME (Weekday,Date)
             FROM (
                 SELECT  Date = DATEADD ( Day ,ID, @begin_date )
                 FROM   @tb                 
            )a  WHERE  Date <= @end_date
                 AND  ( DATEPART (Weekday,Date) + @@DATEFIRST - 1 ) % 7   BETWEEN   1   AND   5
             SET   @begin_date = DATEADD ( Day , 366 , @begin_date )
         END
     ELSE   IF   @bz = 1
         WHILE   @begin_date <= @end_date
         BEGIN
             INSERT   INTO   @re (Date,Weekday)
             SELECT  Date, DATENAME (Weekday,Date)
             FROM (
                 SELECT  Date = DATEADD ( Day ,ID, @begin_date )
                 FROM   @tb                 
            )a  WHERE  Date <= @end_date
                 AND  ( DATEPART (Weekday,Date) + @@DATEFIRST - 1 ) % 7   in ( 0 , 6 )
             SET   @begin_date = DATEADD ( Day , 366 , @begin_date )
         END
     ELSE
         WHILE   @begin_date <= @end_date
         BEGIN
             INSERT   INTO   @re (Date,Weekday)
             SELECT  Date, DATENAME (Weekday,Date)
             FROM (
                 SELECT  Date = DATEADD ( Day ,ID, @begin_date )
                 FROM   @tb                 
            )a  WHERE  Date <= @end_date
             SET   @begin_date = DATEADD ( Day , 366 , @begin_date )
         END

     RETURN
END
GO



##########################################################################################
工作日处理函数(标准节假日)
##########################################################################################

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_WorkDay] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_WorkDay ]
GO

-- 计算两个日期相差的工作天数
CREATE   FUNCTION  f_WorkDay(
@dt_begin   datetime ,   -- 计算的开始日期
@dt_end    datetime      -- 计算的结束日期
) RETURNS   int
AS
BEGIN
     DECLARE   @workday   int , @i   int , @bz   bit , @dt   datetime
     IF   @dt_begin > @dt_end
         SELECT   @bz = 1 , @dt = @dt_begin , @dt_begin = @dt_end , @dt_end = @dt
     ELSE
         SET   @bz = 0
     SELECT   @i = DATEDIFF ( Day , @dt_begin , @dt_end ) + 1 ,
         @workday = @i / 7 * 5 ,
         @dt_begin = DATEADD ( Day , @i / 7 * 7 , @dt_begin )
     WHILE   @dt_begin <= @dt_end
     BEGIN
         SELECT   @workday = CASE  
             WHEN  ( @@DATEFIRST + DATEPART (Weekday, @dt_begin ) - 1 ) % 7   BETWEEN   1   AND   5
             THEN   @workday + 1   ELSE   @workday   END ,
             @dt_begin = @dt_begin + 1
     END
     RETURN ( CASE   WHEN   @bz = 1   THEN   - @workday   ELSE   @workday   END )
END
GO



/**/ /* ================================================================= */

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_WorkDayADD] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_WorkDayADD ]
GO

-- 在指定日期上,增加指定工作天数后的日期
CREATE   FUNCTION  f_WorkDayADD(
@date      datetime ,   -- 基础日期
@workday   int         -- 要增加的工作日数
) RETURNS   datetime
AS
BEGIN
     DECLARE   @bz   int
     -- 增加整周的天数
     SELECT   @bz = CASE   WHEN   @workday < 0   THEN   - 1   ELSE   1   END
        , @date = DATEADD (Week, @workday / 5 , @date )
        , @workday = @workday % 5
     -- 增加不是整周的工作天数
     WHILE   @workday <> 0  
         SELECT   @date = DATEADD ( Day , @bz , @date ),
             @workday = CASE   WHEN  ( @@DATEFIRST + DATEPART (Weekday, @date ) - 1 ) % 7   BETWEEN   1   AND   5
                 THEN   @workday - @bz   ELSE   @workday   END
     -- 避免处理后的日期停留在非工作日上
     WHILE  ( @@DATEFIRST + DATEPART (Weekday, @date ) - 1 ) % 7   in ( 0 , 6 ) 
         SET   @date = DATEADD ( Day , @bz , @date )
     RETURN ( @date )
END





##########################################################################################
工作日处理函数(自定义节假日)
##########################################################################################

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [tb_Holiday] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ tb_Holiday ]
GO

-- 定义节假日表
CREATE   TABLE  tb_Holiday(
HDate  smalldatetime   primary   key   clustered ,  -- 节假日期
Name  nvarchar ( 50 )  not   null )              -- 假日名称
GO

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_WorkDay] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_WorkDay ]
GO

-- 计算两个日期之间的工作天数
CREATE   FUNCTION  f_WorkDay(
@dt_begin   datetime ,   -- 计算的开始日期
@dt_end    datetime     -- 计算的结束日期
) RETURNS   int
AS
BEGIN
     IF   @dt_begin > @dt_end
         RETURN ( DATEDIFF ( Day , @dt_begin , @dt_end )
             + 1 - (
                 SELECT   COUNT ( * )  FROM  tb_Holiday
                 WHERE  HDate  BETWEEN   @dt_begin   AND   @dt_end ))
     RETURN ( - ( DATEDIFF ( Day , @dt_end , @dt_begin )
         + 1 - (
             SELECT   COUNT ( * )  FROM  tb_Holiday
             WHERE  HDate  BETWEEN   @dt_end   AND   @dt_begin )))
END
GO

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_WorkDayADD] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_WorkDayADD ]
GO

-- 在指定日期上增加工作天数
CREATE   FUNCTION  f_WorkDayADD(
@date      datetime ,   -- 基础日期
@workday   int         -- 要增加的工作日数
) RETURNS   datetime
AS
BEGIN
     IF   @workday > 0
         WHILE   @workday > 0
             SELECT   @date = @date + @workday , @workday = count ( * )
             FROM  tb_Holiday
             WHERE  HDate  BETWEEN   @date   AND   @date + @workday
     ELSE
         WHILE   @workday < 0
             SELECT   @date = @date + @workday , @workday =- count ( * )
             FROM  tb_Holiday
             WHERE  HDate  BETWEEN   @date   AND   @date + @workday
     RETURN ( @date )
END



##########################################################################################
计算工作时间的函数
##########################################################################################
if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [tb_worktime] ' )  and   OBJECTPROPERTY (id, N ' IsUserTable ' )  =   1 )
drop   table   [ tb_worktime ]
GO

-- 定义工作时间表
CREATE   TABLE  tb_worktime(
    ID        int   identity ( 1 , 1 )  PRIMARY   KEY ,             -- 序号
    time_start  smalldatetime ,                             -- 工作的开始时间
    time_end   smalldatetime ,                            -- 工作的结束时间
    worktime   AS   DATEDIFF (Minute,time_start,time_end)   -- 工作时数(分钟)
)
GO

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_WorkTime] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_WorkTime ]
GO

-- 计算两个日期之间的工作时间
CREATE   FUNCTION  f_WorkTime(
@date_begin   datetime ,   -- 计算的开始时间
@date_end   datetime       -- 计算的结束时间
) RETURNS   int
AS
BEGIN
     DECLARE   @worktime   int
     IF   DATEDIFF ( Day , @date_begin , @date_end ) = 0
         SELECT   @worktime = SUM ( DATEDIFF (Minute,
             CASE   WHEN   CONVERT ( VARCHAR , @date_begin , 108 ) > time_start
                 THEN   CONVERT ( VARCHAR , @date_begin , 108 )
                 ELSE  time_start  END ,
             CASE   WHEN   CONVERT ( VARCHAR , @date_end , 108 ) < time_end
                 THEN   CONVERT ( VARCHAR , @date_end , 108 )
                 ELSE  time_end  END ))
         FROM  tb_worktime 
         WHERE  time_end > CONVERT ( VARCHAR , @date_begin , 108 )
             AND  time_start < CONVERT ( VARCHAR , @date_end , 108 )
     ELSE
         SET   @worktime
             = ( SELECT   SUM ( CASE
                     WHEN   CONVERT ( VARCHAR , @date_begin , 108 ) > time_start
                     THEN   DATEDIFF (Minute, CONVERT ( VARCHAR , @date_begin , 108 ),time_end)
                     ELSE  worktime  END )
                 FROM  tb_worktime 
                 WHERE  time_end > CONVERT ( VARCHAR , @date_begin , 108 ))
             + ( SELECT   SUM ( CASE  
                     WHEN   CONVERT ( VARCHAR , @date_end , 108 ) < time_end
                     THEN   DATEDIFF (Minute,time_start, CONVERT ( VARCHAR , @date_end , 108 ))
                     ELSE  worktime  END )
                 FROM  tb_worktime 
                 WHERE  time_start < CONVERT ( VARCHAR , @date_end , 108 ))
             + CASE  
                 WHEN   DATEDIFF ( Day , @date_begin , @date_end ) > 1  
                 THEN  ( DATEDIFF ( Day , @date_begin , @date_end ) - 1 )
                     * ( SELECT   SUM (worktime)  FROM  tb_worktime)
                 ELSE   0   END
     RETURN ( @worktime )
END


其他

##########################################################################################
复杂年月处理
##########################################################################################

-- 定义基本数字表
declare   @T1   table (代码  int ,名称  varchar ( 10 ),参加时间  datetime ,终止时间  datetime )
insert   into   @T1
     select   12 , ' 单位1 ' , ' 2003/04/01 ' , ' 2004/05/01 '
     union   all   select   22 , ' 单位2 ' , ' 2001/02/01 ' , ' 2003/02/01 '
     union   all   select   42 , ' 单位3 ' , ' 2000/04/01 ' , ' 2003/05/01 '
     union   all   select   25 , ' 单位5 ' , ' 2003/04/01 ' , ' 2003/05/01 '

-- 定义年表
declare   @NB   table (代码  int ,名称  varchar ( 10 ),年份  int )
insert   into   @NB
     select   12 , ' 单位1 ' , 2003
     union   all   select   12 , ' 单位1 ' , 2004
     union   all   select   22 , ' 单位2 ' , 2001
     union   all   select   22 , ' 单位2 ' , 2002
     union   all   select   22 , ' 单位2 ' , 2003

-- 定义月表
declare   @YB   table (代码  int ,名称  varchar ( 10 ),年份  int ,月份  varchar ( 2 ))
insert   into   @YB
     select   12 , ' 单位1 ' , 2003 , ' 04 '
     union   all   select   22 , ' 单位2 ' , 2001 , ' 01 '
     union   all   select   22 , ' 单位2 ' , 2001 , ' 12 '

-- 为年表+月表数据处理准备临时表
select   top   8246  y = identity ( int , 1753 , 1 )
into  #tby  from
    ( select  id  from  syscolumns) a,
    ( select  id  from  syscolumns) b,
    ( select  id  from  syscolumns) c

-- 为月表数据处理准备临时表
select   top   12  m = identity ( int , 1 , 1 )
into  #tbm  from  syscolumns

/**/ /* --数据处理-- */
-- 年表数据处理
select  a. *
from (
select  a.代码,a.名称,年份 = b.y
from   @T1  a,#tby b
where  b.y  between   year (参加时间)  and   year (终止时间)
) a  left   join   @NB  b  on  a.代码 = b.代码  and  a.年份 = b.年份
where  b.代码  is   null

-- 月表数据处理
select  a. *
from (
select  a.代码,a.名称,年份 = b.y,月份 = right ( ' 00 ' + cast (c.m  as   varchar ), 2 )
from   @T1  a,#tby b,#tbm c
where  b.y * 100 + c.m  between   convert ( varchar ( 6 ),参加时间, 112 ) 
     and   convert ( varchar ( 6 ),终止时间, 112 )
) a  left   join   @YB  b  on  a.代码 = b.代码  and  a.年份 = b.年份  and  a.月份 = b.月份
where  b.代码  is   null
order   by  a.代码,a.名称,a.年份,a.月份

-- 删除数据处理临时表
drop   table  #tby,#tbm





##########################################################################################
交叉表
##########################################################################################


-- 示例

-- 示例数据
create   table  tb(ID  int ,Time  datetime )
insert  tb  select   1 , ' 2005/01/24 16:20 '
union   all   select   2 , ' 2005/01/23 22:45 '
union   all   select   3 , ' 2005/01/23 0:30 '
union   all   select   4 , ' 2005/01/21 4:28 '
union   all   select   5 , ' 2005/01/20 13:22 '
union   all   select   6 , ' 2005/01/19 20:30 '
union   all   select   7 , ' 2005/01/19 18:23 '
union   all   select   8 , ' 2005/01/18 9:14 '
union   all   select   9 , ' 2005/01/18 18:04 '
go

-- 查询处理：
select       case   when   grouping (b.Time) = 1   then   ' Total '   else  b.Time  end ,
     [ Mon ] = sum ( case  a.week  when   1   then   1   else   0   end ),
     [ Tue ] = sum ( case  a.week  when   2   then   1   else   0   end ),
     [ Wed ] = sum ( case  a.week  when   3   then   1   else   0   end ),
     [ Thu ] = sum ( case  a.week  when   4   then   1   else   0   end ),
     [ Fri ] = sum ( case  a.week  when   5   then   1   else   0   end ),
     [ Sat ] = sum ( case  a.week  when   6   then   1   else   0   end ),
     [ Sun ] = sum ( case  a.week  when   0   then   1   else   0   end ),
     [ Total ] = count (a.week)
from (
     select  Time = convert ( char ( 5 ), dateadd (hour, - 1 ,Time), 108 )
             -- 时间交界点是1am,所以减1小时，避免进行跨天处理
        ,week = ( @@datefirst + datepart (weekday,Time) - 1 ) % 7
             -- 考虑@@datefirst对datepart的影响
     from  tb
)a  right   join (
     select  id = 1 ,a = ' 16:00 ' ,b = ' 19:59 ' ,Time = ' [5pm - 9pm) '   union   all
     select  id = 2 ,a = ' 20:00 ' ,b = ' 23:59 ' ,Time = ' [9pm - 1am) '   union   all
     select  id = 3 ,a = ' 00:00 ' ,b = ' 02:59 ' ,Time = ' [1am - 4am) '   union   all
     select  id = 4 ,a = ' 03:00 ' ,b = ' 07:29 ' ,Time = ' [4am - 8:30am) '   union   all
     select  id = 5 ,a = ' 07:30 ' ,b = ' 11:59 ' ,Time = ' [8:30am - 1pm) '   union   all
     select  id = 6 ,a = ' 12:00 ' ,b = ' 15:59 ' ,Time = ' [1pm - 5pm) '
)b  on  a.Time >= b.a  and  a.Time < b.b
group   by  b.id,b.Time  with  rollup
having   grouping (b.Time) = 0   or   grouping (b.id) = 1
go

-- 删除测试
drop   table  tb

/**/ /* --测试结果

               Mon   Tue   Wed   Thu   Fri   Sat   Sun   Total 
-------------- ----- ----- ----- ----- ----- ------ ---- -------
[5pm - 9pm)    0     1     2     0     0     0     0     3
[9pm - 1am)    0     0     0     0     0     0     2     2
[1am - 4am)    0     0     0     0     0     0     0     0
[4am - 8:30am) 0     0     0     0     1     0     0     1
[8:30am - 1pm) 0     1     0     0     0     0     0     1
[1pm - 5pm)    1     0     0     1     0     0     0     2
Total          1     2     2     1     1     0     2     9

（所影响的行数为 7 行）
-- */


##########################################################################################
任意两个时间之间的星期几的次数 - 横
##########################################################################################


if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_weekdaycount] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_weekdaycount ]
GO

/**/ /* --计算任意两个时间之间的星期几的次数(横向显示)

    本方法直接判断 @@datefirst 做对应处理
    不受 sp_language 及 set datefirst 的影响     

--邹建 2004.08(引用请保留此信息)-- */

/**/ /* --调用示例
    
    select * from f_weekdaycount('2004-9-01','2004-9-02')
-- */
create   function  f_weekdaycount(
@dt_begin   datetime ,
@dt_end   datetime
) returns   table
as
return (
     select  跨周数
        ,周一 = case  a
             when   - 1   then   case   when   1   between  b  and  c  then   1   else   0   end
             when    0   then   case   when  b <= 1   then   1   else   0   end
                     + case   when  c >= 1   then   1   else   0   end
             else  a + case   when  b <= 1   then   1   else   0   end
                 + case   when  c >= 1   then   1   else   0   end
             end
        ,周二 = case  a
             when   - 1   then   case   when   2   between  b  and  c  then   1   else   0   end
             when    0   then   case   when  b <= 2   then   1   else   0   end
                     + case   when  c >= 2   then   1   else   0   end
             else  a + case   when  b <= 2   then   1   else   0   end
                 + case   when  c >= 2   then   1   else   0   end
             end
        ,周三 = case  a
             when   - 1   then   case   when   3   between  b  and  c  then   1   else   0   end
             when    0   then   case   when  b <= 3   then   1   else   0   end
                     + case   when  c >= 3   then   1   else   0   end
             else  a + case   when  b <= 3   then   1   else   0   end
                 + case   when  c >= 3   then   1   else   0   end
             end
        ,周四 = case  a
             when   - 1   then   case   when   4   between  b  and  c  then   1   else   0   end
             when    0   then   case   when  b <= 4   then   1   else   0   end
                     + case   when  c >= 4   then   1   else   0   end
             else  a + case   when  b <= 4   then   1   else   0   end
                 + case   when  c >= 4   then   1   else   0   end
             end
        ,周五 = case  a
             when   - 1   then   case   when   5   between  b  and  c  then   1   else   0   end
             when    0   then   case   when  b <= 5   then   1   else   0   end
                     + case   when  c >= 5   then   1   else   0   end
             else  a + case   when  b <= 5   then   1   else   0   end
                 + case   when  c >= 5   then   1   else   0   end
             end
        ,周六 = case  a
             when   - 1   then   case   when   6   between  b  and  c  then   1   else   0   end
             when    0   then   case   when  b <= 6   then   1   else   0   end
                     + case   when  c >= 6   then   1   else   0   end
             else  a + case   when  b <= 6   then   1   else   0   end
                 + case   when  c >= 6   then   1   else   0   end
             end
        ,周日 = case  a
             when   - 1   then   case   when   0   between  b  and  c  then   1   else   0   end
             when    0   then   case   when  b <= 0   then   1   else   0   end
                     + case   when  c >= 0   then   1   else   0   end
             else  a + case   when  b <= 0   then   1   else   0   end
                 + case   when  c >= 0   then   1   else   0   end
             end
     from (
         select  跨周数 = case   when   @dt_begin < @dt_end
                 then  ( datediff ( day , @dt_begin , @dt_end ) + 7 ) / 7
                 else  ( datediff ( day , @dt_end , @dt_begin ) + 7 ) / 7   end
            ,a = case   when   @dt_begin < @dt_end
                 then   datediff (week, @dt_begin , @dt_end ) - 1
                 else   datediff (week, @dt_end , @dt_begin ) - 1   end
            ,b = case   when   @dt_begin < @dt_end
                 then  ( @@datefirst + datepart (weekday, @dt_begin ) - 1 ) % 7
                 else  ( @@datefirst + datepart (weekday, @dt_end ) - 1 ) % 7   end
            ,c = case   when   @dt_begin < @dt_end
                 then  ( @@datefirst + datepart (weekday, @dt_end ) - 1 ) % 7
                 else  ( @@datefirst + datepart (weekday, @dt_begin ) - 1 ) % 7   end )a
)
go




##########################################################################################
任意两个时间之间的星期几的次数 - 纵.sql
##########################################################################################

if   exists  ( select   *   from  dbo.sysobjects  where  id  =   object_id (N ' [dbo].[f_weekdaycount] ' )  and  xtype  in  (N ' FN ' , N ' IF ' , N ' TF ' ))
drop   function   [ dbo ] . [ f_weekdaycount ]
GO

/**/ /* --计算任意两个时间之间的星期几的次数(纵向显示)

    本方法直接判断 @@datefirst 做对应处理
    不受 sp_language 及 set datefirst 的影响     

--邹建 2004.08(引用请保留此信息)-- */

/**/ /* --调用示例
    
    select * from f_weekdaycount('2004-8-02','2004-8-8')
-- */
create   function  f_weekdaycount(
@dt_begin   datetime ,
@dt_end   datetime
) returns   table
as
return (
     select  项目 = ' 跨周数 '
        ,值 = case   when   @dt_begin < @dt_end
             then  ( datediff ( day , @dt_begin , @dt_end ) + 7 ) / 7
             else  ( datediff ( day , @dt_end , @dt_begin ) + 7 ) / 7   end
     union   all
     select  a.a, case  b.a
         when   - 1   then   case   when  a.b  between  b.b  and  b.c  then   1   else   0   end
         when    0   then   case   when  b.b <= a.b  then   1   else   0   end
             + case   when  b.c >= a.b  then   1   else   0   end
         else  b.a + case   when  b.b <= a.b  then   1   else   0   end
             + case   when  b.c >= a.b  then   1   else   0   end
         end
     from ( select  a = ' 星期一 ' ,b = 1  
         union   all   select   ' 星期二 ' , 2   union   all   select   ' 星期三 ' , 3
         union   all   select   ' 星期四 ' , 4   union   all   select   ' 星期五 ' , 5
         union   all   select   ' 星期六 ' , 6   union   all   select   ' 星期日 ' , 0  
    )a,( select  a = case   when   @dt_begin < @dt_end
             then   datediff (week, @dt_begin , @dt_end ) - 1
             else   datediff (week, @dt_end , @dt_begin ) - 1   end
        ,b = case   when   @dt_begin < @dt_end
             then  ( @@datefirst + datepart (weekday, @dt_begin ) - 1 ) % 7
             else  ( @@datefirst + datepart (weekday, @dt_end ) - 1 ) % 7   end
        ,c = case   when   @dt_begin < @dt_end
             then  ( @@datefirst + datepart (weekday, @dt_end ) - 1 ) % 7
             else  ( @@datefirst + datepart (weekday, @dt_begin ) - 1 ) % 7   end )b
)
go





##########################################################################################
统计 -- 交叉表+日期+优先.sql
##########################################################################################

-- 交叉表,根据优先级取数据,日期处理

create   table  tb(qid  int ,rid  nvarchar ( 4 ),tagname  nvarchar ( 10 ),starttime  smalldatetime ,endtime  smalldatetime ,startweekday  int ,endweekday  int ,startdate  smalldatetime ,enddate  smalldatetime ,d  int )
insert  tb  select   1 , ' A1 ' , ' 未订 ' , ' 08:00 ' , ' 09:00 ' , 1    , 5    , null        , null        , 1
union   all   select   1 , ' A1 ' , ' 未订 ' , ' 09:00 ' , ' 10:00 ' , 1    , 5    , null        , null        , 1
union   all   select   1 , ' A1 ' , ' 未订 ' , ' 10:00 ' , ' 11:00 ' , 1    , 5    , null        , null        , 1
union   all   select   1 , ' A1 ' , ' 装修 ' , ' 08:00 ' , ' 09:00 ' , null , null , ' 2005-1-18 ' , ' 2005-1-19 ' , 2  
-- union all select 1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
union   all   select   1 , ' A1 ' , ' 装修 ' , ' 10:00 ' , ' 11:00 ' , null , null , ' 2005-1-18 ' , ' 2005-1-19 ' , 2
union   all   select   1 , ' A2 ' , ' 未订 ' , ' 08:00 ' , ' 09:00 ' , 1    , 5    , null        , null        , 1
union   all   select   1 , ' A2 ' , ' 未订 ' , ' 09:00 ' , ' 10:00 ' , 1    , 5    , null        , null        , 1
union   all   select   1 , ' A2 ' , ' 未订 ' , ' 10:00 ' , ' 11:00 ' , 1    , 5    , null        , null        , 1
-- union all select 1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
union   all   select   1 , ' A2 ' , ' 装修 ' , ' 09:00 ' , ' 10:00 ' , null , null , ' 2005-1-18 ' , ' 2005-1-19 ' , 2
-- union all select 1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
go

/**/ /* --楼主这个问题要考虑几个方面

    1. 取星期时,set datefirst 的影响
    2. 优先级问题
    3. qid,rid 应该是未知的(动态变化的)
-- */

-- 实现的存储过程如下
create   proc  p_qry
@date   smalldatetime   -- 要查询的日期
as
set  nocount  on
declare   @week   int , @s   nvarchar ( 4000 )
-- 格式化日期和得到星期
select   @date = convert ( char ( 10 ), @date , 120 )
    , @week = ( @@datefirst + datepart (weekday, @date ) - 1 ) % 7
    , @s = ''
select  id = identity ( int ), *   into  #t
from (
     select   top   100   percent
        qid,rid,tagname,
        starttime = convert ( char ( 5 ),starttime, 108 ),
        endtime = convert ( char ( 5 ),endtime, 108 )
     from  tb
     where  ( @week   between  startweekday  and  endweekday)
         or ( @date   between  startdate  and  enddate)
     order   by  qid,rid,starttime,d  desc )a

select   @s = @s + N ' ,[ ' + rtrim (rid)
     + N ' ]=max(case when qid= ' + rtrim (qid)
     + N '  and rid=N ''' + rtrim (rid)
     + N '''  then tagname else N ''''  end) '
from  #t  group   by  qid,rid
exec ( '
select starttime,endtime ' + @s + '  
from #t a
where not exists(
    select * from #t
    where qid=a.qid and rid=a.rid 
        and starttime=a.starttime
        and endtime=a.endtime
        and id<a.id)
group by starttime,endtime ' )
go

-- 调用
exec  p_qry  ' 2005-1-17 '
exec  p_qry  ' 2005-1-18 '
go

-- 删除测试
drop   table  tb
drop   proc  p_qry

/**/ /* --测试结果

starttime endtime A1         A2         
--------- ------- ---------- ---------- 
08:00     09:00   未订         未订
09:00     10:00   未订         未订
10:00     11:00   未订         未订

starttime endtime A1         A2         
--------- ------- ---------- ---------- 
08:00     09:00   装修         未订
09:00     10:00   未订         装修
10:00     11:00   装修         未订
-- */