sql转向添加日期范围和删除日期范围

问题描述：

我正在使用一个名为CELCAT的时间表应用程序，试图抽出一些关于学生应该被标记为报告的数据...这似乎是非常困难的，因为这样添加和学生登记的结构去除见下文： sql转向添加日期范围和删除日期范围

studentid eventid  fromdatetime    addition removal 
25149  25145  2009-09-12 10:30:00.000  Y   NULL 
25149  25145  2009-09-12 10:30:00.000  NULL  Y 
25149  25145  2009-09-12 10:30:00.000  Y   NULL 
25150  23013  2009-09-08 09:00:00.000  Y   NULL 
25150  23554  2009-09-07 09:00:00.000  Y   NULL 
25150  25145  2009-09-12 10:30:00.000  Y   NULL 
25150  25145  2009-07-27 00:00:00.000  NULL  Y 
25150  25145  2009-09-12 10:30:00.000  Y   NULL 
25150  25145  2009-09-12 10:30:00.000  NULL  Y 
25150  25145  2009-09-12 10:30:00.000  Y   NULL 
25150  25148  2009-09-12 15:00:00.000  Y   NULL 
25151  25145  2009-09-12 10:30:00.000  Y   NULL 
25151  25145  2009-10-10 00:00:00.000  NULL  Y 
25152  25145  2009-09-19 10:30:00.000  Y   NULL 
25152  25145  2009-07-27 00:00:00.000  NULL  Y

因此，一个除了学生的手段，他们应该从该日起在寄存器中被标记（寄存器是用自己的周轮廓每周reccurring事件，但我可以处理它的那一面）。删除将意味着学生不需要在此日期之后被标记，但是可能会在稍后的一周内添加，删除并重新添加学生。

我想会得到我在正确的方向将是让结构

studentid eventid fromdate      todate 
25149   25145 2009-09-12 10:30:00.000  2009-09-28 10:30:00.000  
25149   25145 2009-10-13 10:30:00.000  2009-10-24 10:30:00.000

任何想法如何做到这一点的桌子吗？还是更好的建议？我想它会涉及一些游标的使用，除非有人有一个很棒的解决方案。这些表格由CELCAT设计，不能修改。

哦，是它的SQL Server 2005的

通过KM编辑，这里是一些代码来测试的解决方案：

DECLARE @YourTable table (studentid int 
         ,eventid int 
         ,fromdatetime datetime 
         ,addition char(1) 
         ,removal char(1) 
         ) 

SET NOCOUNT ON 
INSERT INTO @YourTable VALUES (25149,25145,'2009-09-12 10:30:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25149,25145,'2009-09-12 10:30:00.000', NULL,'Y') 
INSERT INTO @YourTable VALUES (25149,25145,'2009-09-12 10:30:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25150,23013,'2009-09-08 09:00:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25150,23554,'2009-09-07 09:00:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25150,25145,'2009-09-12 10:30:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25150,25145,'2009-07-27 00:00:00.000', NULL,'Y') 
INSERT INTO @YourTable VALUES (25150,25145,'2009-09-12 10:30:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25150,25145,'2009-09-12 10:30:00.000', NULL,'Y') 
INSERT INTO @YourTable VALUES (25150,25145,'2009-09-12 10:30:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25150,25148,'2009-09-12 15:00:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25151,25145,'2009-09-12 10:30:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25151,25145,'2009-10-10 00:00:00.000', NULL,'Y') 
INSERT INTO @YourTable VALUES (25152,25145,'2009-09-19 10:30:00.000','Y' ,NULL) 
INSERT INTO @YourTable VALUES (25152,25145,'2009-07-27 00:00:00.000', NULL,'Y') 
SET NOCOUNT OFF

如何从给定的样本数据中获取这两个日期范围行？当您想要的“答案”与给定的样本数据不匹配时，很难为您做出查询。 – 2009-10-01 17:44:22

此表/视图中是否有另一列表示添加记录时的时间戳或至少是增量标识符？如果不是这样，那么为ID = 25149的学生设置一个逻辑是相当困难的，因为他被添加到同一个事件或从同一个事件中移除了3次，但是不清楚先做了什么。 – van 2009-10-02 15:33:20

如果你指出你希望得到什么样的“最终”结果，而不仅仅是“......正确的方向......”，它也会有所帮助。谢谢 – van 2009-10-02 16:10:07

答

一个简单的方法是创建一个标量函数的studentid和一个eventid返回了一点，表明该学生是否在进入或退出：

create function Event_IsStudentIn(@eventID int, @studentID int) 
returns bit as 
begin 
    declare @in bit 

    set @in = 0 

    select 
    @in = case when addition = 'Y' then 1 else 0 end 
    from [table] 
    where eventid = @eventID and studentid = @studentID 
    order by fromdatetime desc     

    return @in 
end

但是，在未来，您可能想要调整数据。

我不明白这是如何解决的，也许我没有足够好地解答这个问题。 – PeteT 2009-10-01 16:00:01

答

我不确定您正在尝试获取的查询以及日期范围如何提供帮助。

但是，您可以保留当前表，它就像一个日志。只需添加一个保持当前状态的汇总表。插入旧表并更新新表。你可以查询新的来判断IN/OUT并使用旧的进行更详细的分析。

答

我不太清楚你需要怎样处理数据，但是这里是如何将数据转换成你想要的格式。

SELECT studentid, eventid, fromdatetime as fromdate, 
    (SELECT TOP 1 fromdatetime FROM table 
    WHERE studentid = t1.studentid 
     AND eventid = t1.eventid 
     AND removal = 'Y' AND fromdatetime > t1.fromdatetime 
    ) AS todate 
FROM table t1 
WHERE addition = 'Y'

这个查询会产生你要的格式的数据：

studentid eventid fromdate      todate 
25149  25145  2009-09-12 10:30:00.000  2009-09-28 10:30:00.000 
25149  25145  2009-10-13 10:30:00.000  2009-10-24 10:30:00.000

此查询可能是低效的大量数据，所以我真的希望你的表有标识列这是索引。如果是，请将AND fromdatetime > t1.fromdatetime替换为AND id > t1.id。这应该使子查询更有效率。

我试过这个查询，它不起作用。我将用表定义编辑原始问题并插入以填充示例数据。从那里你所要做的就是将两个“FROM表”替换为“FROM @YourTable”，并使用原来问题中的代码。 – 2009-10-02 17:12:44

我看了这个，查询确实按照我认为应该的方式工作，但目前的问题是数据。也许我误解了数据的结构，但在上面的示例数据中，插入后有删除，同一天有多个插入，同一天插入和删除。也许OP可以准确解释数据代表什么，以及上面发布的样本是实时数据还是样机。 – Richard 2009-10-03 16:07:22

答

我觉得有一些错误的样本数据一共有3条记录

 
25149  25145 2009-09-12 10:30:00.000  Y   NULL 
25149  25145 2009-09-12 10:30:00.000  NULL  Y 
25149  25145 2009-09-12 10:30:00.000  Y   NULL

基本上他们说，加入学生/删除，并在同一时间再次添加。但是，请记住，选择订单时不能保证。如何知道学生不是先被移除然后再添加两次？该问题要求输出中的行

25149  25145  2009-10-13 10:30:00.000  2009-10-24 10:30:00.000

，但'2009-10-13'值在数据中无处。

然后这三个：

25150  25145 2009-09-12 10:30:00.000  Y    NULL 
25150  25145 2009-07-27 00:00:00.000  NULL   Y 
25150  25145 2009-09-12 10:30:00.000  Y   NULL

正是我上述假设的情景 - 通过日期来看，学生首先被移除，然后添加两次！

sql转向添加日期范围和删除日期范围

相关推荐