【TSQL】挑战2

叶子 2011-08-02 12:47:30
让我们来看一下接下来的这个挑战（挑战1暂时略掉）

这是对于挑战2的测试数据 ：

ID StartDate EndDate
----------- ----------------------- -----------------------
1 2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2 2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3 2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4 2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5 2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6 2009-01-27 21:15:00.000 2009-01-28 09:15:00.000

这个挑战是去计算这个StartDate 和EndDate 之间的工作时间，我们定义工作时间为周一到周五的上午8点到下午5点。
如果StartDate是星期五的中午12点，并且EndDate 是星期一的上午10点，那么你只需要计算星期五的中午12点到下午5点和星期一的上午8点到10点之间的持续时间就可以了。

下面是我们预期的结果：

StartDate EndDate Hours Minutes
----------------------- ----------------------- ----------- -----------
2009-03-02 08:00:00.000 2009-03-02 15:00:00.000 7 0
2009-03-01 16:00:00.000 2009-03-03 14:00:00.000 15 0
2009-02-26 07:00:00.000 2009-02-26 22:00:00.000 9 0
2009-01-27 09:15:00.000 2009-01-27 17:15:00.000 7 45
2009-01-17 13:45:00.000 2009-01-19 07:45:00.000 0 0
2009-01-27 21:15:00.000 2009-01-28 09:15:00.000 1 15

这是生成测试数据的脚本



SET DATEFORMAT MDY

DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME) 

INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 

INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 

INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 

INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 

INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 

INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 



SELECT * FROM @t


备注: 请使用给出的测试数据完成查询。
...全文
325 24 打赏 收藏 转发到动态 举报
写回复
用AI写文章
24 条回复
切换为时间正序
请发表友善的回复…
发表回复
叶子 2011-10-23
  • 打赏
  • 举报
 回复
[Quote=引用 23 楼 izbox 的回复:]
这个题目让我很蛋疼。。。

SQL code

SET DATEFORMAT MDY
DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME)
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00P……
[/Quote]
SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM'

周六下午1:45到周一早上7:45之间没有工作时间，故为0.
izbox 2011-09-05
  • 打赏
  • 举报
 回复
这个题目让我很蛋疼。。。


SET DATEFORMAT MDY

DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME) 

INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00  这个对的 7个小时

INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00  这个3月1号是周日 所以 2号+3号 是 15个小时 

INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00  这个也木有问题 9个小时

INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 这个也没问题

INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 这个不对吧  = = 怎么是00？？？？？

INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 这个 怎么说呢？？？？



SELECT * FROM @t
CalvinR 2011-09-05
  • 打赏
  • 举报
 回复
楼主 偶是来打酱油的
楼主的这张景甜照很像刘亦菲 是这么回事儿吗??
wen523686945 2011-09-04
  • 打赏
  • 举报
 回复
强啊 ！！
Magic_bos 2011-08-23
  • 打赏
  • 举报
 回复
学习一下!!
稻草_木偶 2011-08-23
  • 打赏
  • 举报
 回复
来学习的
gw6328 2011-08-23
  • 打赏
  • 举报
 回复 


use test;

go

DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME) 

INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 

INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 

INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 

INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 

INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 

INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 



--select * from @t;



;with cte as (

SELECT *,DATEPART(dw,x)-1 as wk,case when DATEDIFF(MI,DATEADD(hh,8,apy.x),a.startDate)>0 then a.StartDate else DATEADD(hh,8,apy.x) end as s1

,case when DATEDIFF(MI,DATEADD(hh,17,apy.x),a.EndDate)>0 then dateadd(HH,17,apy.x) else a.EndDate end as s2 FROM @t a

outer apply(

 select convert(char(10),dateadd(dd,b.number,a.StartDate),120) as x from master..spt_values b where type='p' and convert(varchar(10),dateadd(dd,b.number,a.StartDate),120)<=convert(varchar(10),a.EndDate,120)

) apy

)

--select * from cte;

select id,min(x.StartDate),max(x.EndDate),sum(case when DATEDIFF(MI,s1,s2)>0 then case when x.wk in(0,6) then 0 else DATEDIFF(MI,s1,s2) end else 0 end) as 分钟  from cte x group by id



/*

id                                                          分钟

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

1           2009-03-02 08:00:00.000 2009-03-02 15:00:00.000 420

2           2009-03-01 16:00:00.000 2009-03-03 14:00:00.000 900

3           2009-02-26 07:00:00.000 2009-02-26 22:00:00.000 540

4           2009-01-27 09:15:00.000 2009-01-27 17:15:00.000 465

5           2009-01-17 13:45:00.000 2009-01-19 07:45:00.000 0

6           2009-01-27 21:15:00.000 2009-01-28 09:15:00.000 75

*/
bridge05 2011-08-23
  • 打赏
  • 举报
 回复 


return



ID	StartDate	EndDate	(无列名)

1	2009-03-02 08:00:00.000	2009-03-02 15:00:00.000	420

2	2009-03-01 16:00:00.000	2009-03-03 14:00:00.000	900

3	2009-02-26 07:00:00.000	2009-02-26 22:00:00.000	540

4	2009-01-27 09:15:00.000	2009-01-27 17:15:00.000	465

5	2009-01-17 13:45:00.000	2009-01-19 07:45:00.000	0

6	2009-01-27 21:15:00.000	2009-01-28 09:15:00.000	75
bridge05 2011-08-23
  • 打赏
  • 举报
 回复
--测试数据
SET DATEFORMAT MDY

DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME) 

INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 

INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 

INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 

INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 

INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 

INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15


--function fn_WorkADay, 求某一天工作时间


create function [dbo].[fn_WorkADay](

	@StartDate datetime, 

	@EndDate datetime)

returns int

as

begin

	if datepart("DW", @StartDate) in (7, 1)

		return 0;

	declare @today datetime = convert(varchar(10), @StartDate, 120);

	declare @BTime datetime = DATEADD("MINUTE", 8 * 60, @today),

			@ETime datetime = DATEADD("MINUTE", 17 * 60, @today);

	

	declare @result int = 0;

	if (@StartDate <= @BTime) and (@EndDate >= @ETime)

		set @result = (17 - 8) *60

	else if (@StartDate <= @BTime) and (@EndDate < @ETime) and (@EndDate > @BTime)

		set @result = DATEDIFF("MINUTE", @BTime, @EndDate)

	else if (@StartDate > @BTime) and (@EndDate < @ETime)

		set @result = DATEDIFF("MINUTE", @StartDate, @EndDate)

	else if (@StartDate > @BTime) and (@EndDate > @ETime) and (@StartDate < @ETime)

		set @result = DATEDIFF("MINUTE", @StartDate, @ETime)

	;

		

	return @result;

end


--fn_WorkTime


create function [dbo].[fn_WorkTime](@StartDate datetime, @EndDate datetime)

returns int

as

begin

	declare @result int = 0,

			@workdays int = 0,

			@days int = DATEDIFF("DAY", @StartDate, @EndDate);

	if @days = 0

		set @result = dbo.fn_WorkADay(@StartDate, @EndDate)

	else

	begin

		set @result = dbo.fn_WorkADay(@StartDate, CONVERT(varchar(10), @StartDate, 120)+' 23:59:59') 

				+ dbo.fn_WorkADay(CONVERT(varchar(10), @EndDate, 120) + ' 00:0:00', @EndDate); 

		if @days >= 2

		begin

			select @StartDate = DATEADD("DAY", 1, @StartDate), @EndDate = DATEADD("DAY", -1, @EndDate);

			set @days = DATEDIFF("DAY", @StartDate, @EndDate) + 1;

			set @workdays  = @days / 7 * 5;

			set @days = @days % 7;

			if @days > 0

			begin

				if DATEPART("DW", @StartDate) = 1

					set @workdays = @workdays + @days - 1;

				else if DATEPART("DW", @StartDate) + @days - 1 = 7 

					set @workdays = @workdays + @days - 1

				else if DATEPART("DW", @StartDate) + @days - 1 > 7 

					set @workdays = @workdays + @days - 2

				else

					set @workdays = @workdays + @days 

				;

			end

		end

	end

	return @result + @workdays * (17-8)*60;

end



select *, dbo.fn_WorkTime(StartDate, EndDate) from @t
帝企鹅已被占用 2011-08-22
  • 打赏
  • 举报
 回复
我是来学习的
geniuswjt 2011-08-22
  • 打赏
  • 举报
 回复
支持，学习，睡醒了想下。。。午睡ing
zzzzzzzzzzzzzzzzzZZZZZZZZZZZZZZZZZZZZZZZZzzzzzzzzzzzzzzzzzzzzzzzzzzzz...
bancxc 2011-08-22
  • 打赏
  • 举报
 回复
没事睡会午觉 挑战毛毛啊
Wang471981125 2011-08-22
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 guguda2008 的回复:]

最后一个WHEN写错了，小改一下
SQL code
USE TEMPDB
GO
IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU
GO
CREATE FUNCTION FUN_MU( @SDATE DATETIME ,@EDATE DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @I INT
SELE……
[/Quote]
来学习的
lunhui13 2011-08-22
  • 打赏
  • 举报
 回复
我也是来学习的
bridge05 2011-08-22
  • 打赏
  • 举报
 回复
下班回家试试
--小F-- 2011-08-02
  • 打赏
  • 举报
 回复
我是来学习的
guguda2008 2011-08-02
  • 打赏
  • 举报
 回复
最后一个WHEN写错了，小改一下
USE TEMPDB

GO

IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU

GO

CREATE FUNCTION FUN_MU( @SDATE DATETIME ,@EDATE DATETIME)

RETURNS INT

AS

BEGIN

DECLARE @I INT

SELECT @I=SUM(CASE

WHEN DATEPART(DW,SDATE) IN (1,7) THEN 0  

WHEN @SDATE<SDATE AND @EDATE>EDATE THEN DATEDIFF(MI,SDATE,EDATE) 

WHEN @SDATE<SDATE AND @EDATE<=EDATE AND @EDATE>SDATE THEN DATEDIFF(MI,SDATE,@EDATE)

WHEN @SDATE>=SDATE AND @EDATE>EDATE AND EDATE>@SDATE THEN DATEDIFF(MI,@SDATE,EDATE)

WHEN @SDATE>=SDATE AND @EDATE<=EDATE AND @EDATE>SDATE THEN DATEDIFF(MI,@SDATE,@EDATE)

ELSE 0

END)

FROM (

SELECT DATEADD(DAY,NUMBER,CONVERT(VARCHAR(10),@SDATE,120)+' 08:00') AS SDATE

,DATEADD(DAY,NUMBER,CONVERT(VARCHAR(10),@SDATE,120)+' 17:00') AS EDATE

FROM MASTER..SPT_VALUES

WHERE TYPE='P' AND DATEADD(DAY,NUMBER,CONVERT(VARCHAR(10),@SDATE,120))<=@EDATE

) T

RETURN ISNULL(@I,0)

END

GO

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

GO

CREATE TABLE TB(

ID INT IDENTITY, 

StartDate DATETIME, 

EndDate DATETIME) 

INSERT INTO TB (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 

INSERT INTO TB (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 

INSERT INTO TB (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 

INSERT INTO TB (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 

INSERT INTO TB (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 

INSERT INTO TB (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 

GO

SELECT *

,DBO.FUN_MU(STARTDATE,ENDDATE)

FROM TB

/*

1	2009-03-02 08:00:00.000	2009-03-02 15:00:00.000	420

2	2009-03-01 16:00:00.000	2009-03-03 14:00:00.000	900

3	2009-02-26 07:00:00.000	2009-02-26 22:00:00.000	540

4	2009-01-27 09:15:00.000	2009-01-27 17:15:00.000	465

5	2009-01-17 13:45:00.000	2009-01-19 07:45:00.000	0

6	2009-01-27 21:15:00.000	2009-01-28 09:15:00.000	75

*/

guguda2008 2011-08-02
  • 打赏
  • 举报
 回复
时间转化懒得写了，反正是写着玩的
USE TEMPDB

GO

IF OBJECT_ID('FUN_MU') IS NOT NULL DROP FUNCTION FUN_MU

GO

CREATE FUNCTION FUN_MU( @SDATE DATETIME ,@EDATE DATETIME)

RETURNS INT

AS

BEGIN

DECLARE @I INT

SELECT @I=SUM(CASE

WHEN DATEPART(DW,SDATE) IN (1,7) THEN 0  

WHEN @SDATE<SDATE AND @EDATE>EDATE THEN DATEDIFF(MI,SDATE,EDATE) 

WHEN @SDATE<SDATE AND @EDATE<=EDATE AND @EDATE>SDATE THEN DATEDIFF(MI,SDATE,@EDATE)

WHEN @SDATE>=SDATE AND @EDATE>EDATE AND EDATE>@SDATE THEN DATEDIFF(MI,@SDATE,EDATE)

WHEN @SDATE>=EDATE AND @SDATE<=EDATE AND @EDATE>SDATE THEN DATEDIFF(MI,@SDATE,@EDATE)

ELSE 0

END)

FROM (

SELECT DATEADD(DAY,NUMBER,CONVERT(VARCHAR(10),@SDATE,120)+' 08:00') AS SDATE

,DATEADD(DAY,NUMBER,CONVERT(VARCHAR(10),@SDATE,120)+' 17:00') AS EDATE

FROM MASTER..SPT_VALUES

WHERE TYPE='P' AND DATEADD(DAY,NUMBER,CONVERT(VARCHAR(10),@SDATE,120))<=@EDATE

) T

RETURN ISNULL(@I,0)

END

GO

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB

GO

CREATE TABLE TB(

ID INT IDENTITY, 

StartDate DATETIME, 

EndDate DATETIME) 

INSERT INTO TB (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 

INSERT INTO TB (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 

INSERT INTO TB (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 

INSERT INTO TB (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 

INSERT INTO TB (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 

INSERT INTO TB (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 

GO

SELECT *

,DBO.FUN_MU(STARTDATE,ENDDATE)

FROM TB

/*

1	2009-03-02 08:00:00.000	2009-03-02 15:00:00.000	0

2	2009-03-01 16:00:00.000	2009-03-03 14:00:00.000	900

3	2009-02-26 07:00:00.000	2009-02-26 22:00:00.000	540

4	2009-01-27 09:15:00.000	2009-01-27 17:15:00.000	465

5	2009-01-17 13:45:00.000	2009-01-19 07:45:00.000	0

6	2009-01-27 21:15:00.000	2009-01-28 09:15:00.000	75

*/
快溜 2011-08-02
  • 打赏
  • 举报
 回复
40分太少了吧。
sekai2011 2011-08-02
  • 打赏
  • 举报
 回复
mark 上班没时间，回家再看
加载更多回复（4）
SQL挑战
SQL家庭作业-员工数据库：两部分之谜 背景 这是一个美丽的春天，距离您被Pewlett Hackard聘为新数据... 在本地git存储库中，为SQL挑战创建一个目录。 使用文件夹名称来对应挑战： EmployeeSQL 。 将文件添加到此文件夹
SQL_挑战：CWRU作业Wk9
SQL_挑战 CWRU家庭作业Wk9回购包含： 表架构（schema.sql） Postgres数据库查询（HomeworkQuery.sql） EmployeeSQL文件夹（包含项目的所有CSV文件的数据文件夹） ERD图像和导出的sql代码（QuickDBD文件-VARCHAR...
HW9--SQL挑战
SQL家庭作业-员工数据库：两部分之谜 背景 这是一个美丽的春天，距离您被Pewlett Hackard聘为新数据工程师已经两个星期了。 您的第一个主要任务是针对1980年代和1990年代公司员工的研究项目。 该时期的员工数据库中...
EnergyCatapultPresumedOpenDataChallange-
电动梦想预报器-POD Data Science Challange目录关于该项目 POD数据科学挑战推定公开数据是一项与能源系统弹射器和可持续能源中心合作的西方电力分配（WPD）项目，该项目展示了通过增加能源数据的可视性和可用性而...
SQL---Challenge
SQL--挑战
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章