Sql确定两个日期之间的工作日数目

问题

给定两个日期,求它们之间(包括这两个日期本身)有多少个“工作”日。例如,如果1月10日是星期一,1月11日是星期二,由于这两个日期是典型的工作日,所以两个日期之间的工作日数是2。对于这个问题,“工作日”定义为非周六/周日的日子。

解决方案

下面的例子计算BLAKE 和JONES 的HIREDATE(聘用日期)之间的工作日数。要确定两个日期之间的工作日数,可以使用基干表,对两个日期(其中包括起始日期和结束日期)之间的每一天都返回一行。此后,计算工作日数只是数一下返回的日期中非周六/周日的数目。

注意:    如果也想去掉假期,则可以创建一个HOLIDAYS表。然后引入NOT IN前缀,就能够从解决方案中去掉HOLIDAYS 中列出的日子。

DB2

使用基干表T500,能够生成两个日期之间包含的行数(表示天数),然后,对非周末的所有日期计数。使用DAYNAME 函数可返回每个日期是星期几。例如:

 1  select sum(case when dayname(jones_hd+t500.id day -1 day)

 2                    in ( 'Saturday','Sunday' )

 3                  then 0 else 1

 4             end) as days

 5    from (

 6  select max(case when ename = 'BLAKE'

 7                  then hiredate

 8             end) as blake_hd,

 9         max(case when ename = 'JONES'

10                  then hiredate

11             end) as jones_hd

12    from emp

13   where ename in ( 'BLAKE','JONES' )

14         ) x,

15         t500

16   where t500.id <= blake_hd-jones_hd+1

MySQL

使用基干表T500能够生成两个日期之间包含的行数(天数),然后,对非周末的所有日期计数。使用DATE_ADD函数可以给每个日期增加天数;使用DATE_FORMAT函数判定每个日期是星期几:

 1  select sum(case when date_format(

 2                          date_add(jones_hd,

 3                                   interval t500.id-1 DAY),'%a')

 4                    in ( 'Sat','Sun' )

 5                  then 0 else 1

 6             end) as days

 7    from (

 8  select max(case when ename = 'BLAKE'

 9                  then hiredate

10             end) as blake_hd,

11         max(case when ename = 'JONES'

12                  then hiredate

13             end) as jones_hd

14    from emp

15   where ename in ( 'BLAKE','JONES' )

16         ) x,

17         t500

18   where t500.id <= datediff(blake_hd,jones_hd)+1

Oracle

使用基干表T500能够生成两个日期之间包含的行数(天数),然后,对非周末的所有日期计数。使用TO_CHAR函数确定每个日期是星期几:

 1  select sum(case when to_char(jones_hd+t500.id-1,'DY')

 2                    in ( 'SAT','SUN' )

 3                  then 0 else 1

 4             end) as days

 5    from (

 6  select max(case when ename = 'BLAKE'

 7                  then hiredate

 8             end) as blake_hd,

 9         max(case when ename = 'JONES'

10                  then hiredate

11             end) as jones_hd

12    from emp

13   where ename in ( 'BLAKE','JONES' )

14         ) x,

15         t500

16   where t500.id <= blake_hd-jones_hd+1

PostgreSQL

使用基干表T500能够生成两个日期之间包含的行数(天数),然后,对非周末的所有日期计数。使用TO_CHAR函数确定每个日期是星期几:

 1  select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY'))

 2                    in ( 'SATURDAY','SUNDAY' )

 3                  then 0 else 1

 4             end) as days

 5    from (

 6  select max(case when ename = 'BLAKE'

 7                  then hiredate

 8             end) as blake_hd,

 9         max(case when ename = 'JONES'

10                  then hiredate

11             end) as jones_hd

12    from emp

13   where ename in ( 'BLAKE','JONES' )

14         ) x,

15         t500

16   where t500.id <= blake_hd-jones_hd+1

SQL Server

使用基干表T500生成两个日期之间包含的行数(天数),然后,对非周末的所有日期计数。使用DATENAME函数确定每个日期是星期几:

 1  select sum(case when datename(dw,jones_hd+t500.id-1)

 2                    in ( 'SATURDAY','SUNDAY' )

 3                   then 0 else 1

 4             end) as days

 5    from (

 6  select max(case when ename = 'BLAKE'

 7                  then hiredate

 8             end) as blake_hd,

 9         max(case when ename = 'JONES'

10                  then hiredate

11             end) as jones_hd

12    from emp

13   where ename in ( 'BLAKE','JONES' )

14         ) x,

15         t500

16   where t500.id <= datediff(day,jones_hd-blake_hd)+1

讨论

尽管每个RDBMS都需要使用不同的内置函数确定日期名,对每个系统来说,总体解决方案都相同。可以把解决方案分成下面两个步骤:

1.    返回起始日期和结束日期之间的天数(二者均包含在内);

2.    计数除周末以外共有多少天(即行数)。

内联视图X 完成第一步操作。如果检查内联视图X,会注意到,它使用了聚集函数MAX,该函数用于删除NULL。如果对MAX的用法不是很清楚,下面这个例子会有助于读者理解。以下显示了未使用MAX时内联视图X的结果:

select case when ename = 'BLAKE'

            then hiredate

       end as blake_hd,

       case when ename = 'JONES'

            then hiredate

       end as jones_hd

  from emp

 where ename in ( 'BLAKE','JONES' )

BLAKE_HD    JONES_HD

----------- -----------

            02-APR-1981

01-MAY-1981

如果不使用MAX,会返回两行。而使用MAX,只返回一行,且去掉了NULL:

select max(case when ename = 'BLAKE'

            then hiredate

       end) as blake_hd,

       max(case when ename = 'JONES'

            then hiredate

       end) as jones_hd

  from emp

 where ename in ( 'BLAKE','JONES' )

BLAKE_HD    JONES_HD

----------- -----------

01-MAY-1981 02-APR-1981

上述两个日期之间的天数(其中包括两个日期本身)是30。既然两个日期处于一行,那么,下一步就要对这30天 的每一天分别生成一行记录。要返回30天(行),需使用表T500。由于表T500中的每个ID值都比它的前一个值大1,在两个日期中较早的一个 (JONES_HD)上分别加上T500中各行的ID,就可以生成从JONES_HD日期开始直到BLAKE_HD(包括)的连续工作日。其结果如下所示 (使用Oracle语法):

select x.*, t500.*, jones_hd+t500.id-1

  from (

select max(case when ename = 'BLAKE'

                then hiredate

           end) as blake_hd,

       max(case when ename = 'JONES'

                then hiredate

           end) as jones_hd

  from emp

 where ename in ( 'BLAKE','JONES' )

       ) x,

       t500

 where t500.id <= blake_hd-jones_hd+1

BLAKE_HD    JONES_HD            ID JONES_HD+T5

----------- ----------- ---------- -----------

01-MAY-1981 02-APR-1981          1 02-APR-1981

01-MAY-1981 02-APR-1981          2 03-APR-1981

01-MAY-1981 02-APR-1981          3 04-APR-1981

01-MAY-1981 02-APR-1981          4 05-APR-1981

01-MAY-1981 02-APR-1981          5 06-APR-1981

01-MAY-1981 02-APR-1981          6 07-APR-1981

01-MAY-1981 02-APR-1981          7 08-APR-1981

01-MAY-1981 02-APR-1981          8 09-APR-1981

01-MAY-1981 02-APR-1981          9 10-APR-1981

01-MAY-1981 02-APR-1981         10 11-APR-1981

01-MAY-1981 02-APR-1981         11 12-APR-1981

01-MAY-1981 02-APR-1981         12 13-APR-1981

01-MAY-1981 02-APR-1981         13 14-APR-1981

01-MAY-1981 02-APR-1981         14 15-APR-1981

01-MAY-1981 02-APR-1981         15 16-APR-1981

01-MAY-1981 02-APR-1981         16 17-APR-1981

01-MAY-1981 02-APR-1981         17 18-APR-1981

01-MAY-1981 02-APR-1981         18 19-APR-1981

01-MAY-1981 02-APR-1981         19 20-APR-1981

01-MAY-1981 02-APR-1981         20 21-APR-1981

01-MAY-1981 02-APR-1981         21 22-APR-1981

01-MAY-1981 02-APR-1981         22 23-APR-1981

01-MAY-1981 02-APR-1981         23 24-APR-1981

01-MAY-1981 02-APR-1981         24 25-APR-1981

01-MAY-1981 02-APR-1981         25 26-APR-1981

01-MAY-1981 02-APR-1981         26 27-APR-1981

01-MAY-1981 02-APR-1981         27 28-APR-1981

01-MAY-1981 02-APR-1981         28 29-APR-1981

01-MAY-1981 02-APR-1981         29 30-APR-1981

01-MAY-1981 02-APR-1981         30 01-MAY-1981

检查WHERE子句会注意到,对BLAKE_HD和JONES_HD之差进行了加1操作,生成了30行(否则 的话,将生成29行)。另外,还会看到,对外部查询的SELECT列表中的T500.ID进行了减1操作,这是由于ID的起始值为1,而且,对 JONES_HD进行加1操作,会导致从最终结果中减掉JONES_HD。

一旦生成了结果集需要的行数,可以使用CASE表达式“标记”返回的每一天是工作日还是周末(工作日返回1,周末返回0)。最后一步,使用求和函数SUM计算1的个数,以得到最终答案。

 

你可能感兴趣的