0019 sql 使用大全（2）

13：工作日处理函数（标准节假日）

14：工作日处理函数（自定义节假日）

15：计算工作时间的函数

16：复杂年月处理

17：交叉表

18：任意的两个时间的星期几的次数（横向显示）

19：交叉表+日期+优先

13：------------------------------------工作日处理函数（标准节假日）------------------------------------




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




14：---------------------------------工作日处理函数（自定义节假日）-------------------------------------




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




15：------------------------------------计算工作时间的函数-------------------------------------------------




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

16：-------------------------------------复杂年月处理--------------------------------------------




--定义基本数字表

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




17：--------------------------------------------交叉表-------------------------------------------------

--示例

 

--示例数据

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

 

--*/




18：---------------------------任意的两个时间的星期几的次数（横向显示）-------------------------------

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




19：--------------------------------------交叉表+日期+优先---------------------------------------------

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

 

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   装修         未订

--*/