Server 2008 - StartTime、EndTime在选定点上的分割时间间隔
Server 2008 - StartTime、EndTime在选定点上的分割时间间隔
提问于 2014-11-26 15:46:41
在Server 2008中，我有一个表，它以列StartTime、EndTime的形式包含时间间隔。该表还包含一些其他标识符。该表的前3行如下所示：
create table #ExampleTable (ID int, PRDATE date, RID int, StartTime DateTime, EndTime DateTime);
INSERT INTO #ExampleTable(ID, PRDATE, RID, StartTime, EndTime) VALUES
(10000422,'2014-09-01',6,'2014-09-01 04:08:15.000','2014-09-01 04:13:13.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:08:23.000','2014-09-01 09:44:47.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:08:51.000','2014-09-01 05:07:38.000'); 我想在其他表#SplitPoint中给出的SplitPoints中分割间隔，
create table #SplitPoints (SplitPoint DateTime);
INSERT INTO #SplitPoints(SplitPoint) VALUES
('2014-09-01 04:15:00.000'),
('2014-09-01 04:45:00.000'),
('2014-09-01 05:30:00.000');适当时创建新行。所需的结果如下
create table #DesiredResult (ID int, PRDATE date, RID int, StartTime DateTime, EndTime DateTime);
INSERT INTO #DesiredResult(ID, PRDATE, RID, StartTime, EndTime) VALUES
(10000422,'2014-09-01',6,'2014-09-01 04:08:15.000','2014-09-01 04:13:13.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:08:23.000','2014-09-01 04:15:00.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:15:00.000','2014-09-01 04:45:00.000'),
(10000457,'2014-09-01',103,'2014-09-01 04:45:00.000','2014-09-01 05:30:00.000'),
(10000457,'2014-09-01',103,'2014-09-01 05:30:00.000','2014-09-01 09:44:47.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:08:51.000','2014-09-01 04:15:00.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:15:00.000','2014-09-01 04:45:00.000'),
(20000432,'2014-09-01',116,'2014-09-01 04:45:00.000','2014-09-01 05:07:38.000'); 我怎么能忍受这一切？
Stack Overflow用户
发布于 2014-11-26 16:39:58
通过使用UNION，您可能能够完成您正在寻找的内容，我做了一个快速而肮脏的查询，以显示如何以我以为您希望这样做的方式拉出日期。其中最棘手的部分是上一次选择中的Start_Date，因为除非您想要我在查询中输入的逻辑，否则您将需要进行调整以满足您的需要。我还在你的桌子上增加了一个分裂开始和分裂的时间，这使得联盟再次有可能，不确定这对你是否可能，但我想如果我可以的话，我会尽力帮助你。
DECLARE
@Start_Date DATETIME = '2014-09-01 04:08:23.000'
,@End_Date DATETIME = '2014-09-01 09:44:47.000'
create table #SplitPoints (SplitPointStart DateTime,SplitPointEnd DateTime);
INSERT INTO #SplitPoints(SplitPointStart,SplitPointEnd) VALUES
('2014-09-01 04:15:00.000','2014-09-01 04:45:00.000'),
('2014-09-01 04:45:00.000','2014-09-01 05:30:00.000'),
('2014-09-01 05:30:00.000','2014-09-01 06:00:00.000');
SELECT
@Start_Date
,CASE
WHEN DATEPART(m,@Start_Date) <= 15 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@Start_Date)AS VARCHAR) + ':15.000'
WHEN DATEPART(m,@Start_Date) <= 30 AND DATEPART(m,@Start_Date) > 15 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@Start_Date)AS VARCHAR) + ':30.000'
WHEN DATEPART(m,@Start_Date) <= 45 AND DATEPART(m,@Start_Date) > 30 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@Start_Date)AS VARCHAR) + ':45.000'
WHEN DATEPART(m,@Start_Date) <= 60 AND DATEPART(m,@Start_Date) > 45 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,DATEADD(hh,1,@Start_Date))AS VARCHAR) + ':00.000'
ELSE ''
END
UNION
SELECT
SplitPointStart,SplitPointEnd
FROM #Splitpoints
WHERE
SplitPointStart > @Start_Date
AND SplitPointEnd < @End_Date
UNION
SELECT
CASE
WHEN DATEPART(m,DATEADD(mm,-30,@End_Date)) <= 15 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@End_Date)AS VARCHAR) + ':15.000'
WHEN DATEPART(m,DATEADD(mm,-30,@End_Date)) <= 30 AND DATEPART(m,@End_Date) > 15 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@End_Date)AS VARCHAR) + ':30.000'
WHEN DATEPART(m,DATEADD(mm,-30,@End_Date)) <= 45 AND DATEPART(m,@End_Date) > 30 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,@End_Date)AS VARCHAR) + ':45.000'
WHEN DATEPART(m,DATEADD(mm,-30,@End_Date)) <= 60 AND DATEPART(m,@End_Date) > 45 THEN CAST(CAST(GETDATE() AS DATE)AS VARCHAR) + ' '+ CAST(DATEPART(hh,DATEADD(hh,-1,@End_Date))AS VARCHAR) + ':00.000'
ELSE ''
END
,@End_Date
drop table #SplitPoints页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/27153326
复制相关文章
相似问题
腾讯云开发者
