日期处理函数集锦

今天药忘吃喽~ 2022-09-27 03:58 210阅读 0赞

\--工作日处理函数(标准节假日).sql

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][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][-_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  
\----工作日处理函数(标准节假日)

\--工作日处理函数(自定义节假日).sql

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  
\----工作日处理函数(自定义节假日).sql  
\--日期格式化处理.sql

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)

\---日期格式化处理

\---日期推算处理.sql

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)

日期的推算:(转邹老大的代码)  
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)

\---日期推算处理

\--生成日期列表的函数.sql

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_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_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][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][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][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][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][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][end_date]  
            SET @begin\_date=DATEADD(Day,366,@begin\_date)  
        END

RETURN  
END  
GO  
\---特殊日期加减函数.sql

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

\--计算工作时间的函数.sql  
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

\---两个时间之间的星期几的次数-横

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

\---统计--交叉表+日期+优先

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

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   装修         未订  
\--\*/  
\---交叉表

\--示例

\--示例数据  
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 行）  
\--\*/

[dt_end]: mailto:=@dt_end
[-_workday]: mailto:-@workday
[--_bz_0]: mailto:--@bz=0
[end_date]: mailto:=@end_date