使用日期差异计算空闲时段 [英] Calculate Free Slot using Date Difference
问题描述
我正在报告教育领域教师的主表。老师在一天内开始多个讲座，每个批次都有一个独特的批处理，这个表记录了他们各自击球的讲座开始时间讲座结束时间
以下是我桌子的架构（'M / W / F /'表示星期一星期三星期五&'T / T / S'/表示星期二星期四和星期六）
I am reporting a educational domain teacher's master table . The teacher commences multiple lectures in one day and each batch has got a unique batchid, along with this the table logs lecture starttime lecture end time for their respective battches
Following is the schema of my table ('M/W/F/' means Monday Wednesday Friday & 'T/T/S'/ means Tuesday Thursday and Saturday)
ID TEACHERID STARTDATE EXPECTEDENDDATE DAYS STARTTIME ENDTIME
3 1254 2007-11-28 2008-01-21 M/W/F/ 9:00 AM 10:00AM
4 1254 2008-02-20 2008-03-17 M/W/F/ 11:30 AM 1:00PM
5 1315 2008-11-17 2008-12-17 T/T/S/ 2:00 PM 3:30PM
6 1315 2008-11-05 2008-12-05 T/T/S/ 12:00 AM 1:30PM
8 1155 2008-04-18 2008-04-30 M/W/F/ 10:30 AM 12:00AM
9 1514 2008-04-01 2008-08-06 T/T/S/ 12:30 PM 1:00PM
考虑1254老师，他从上午9:00到上午10:00进行批量处理
然后11:30上午1点到下午1点，所以他有10:00 AM到11:30 AM免费插槽。如果我将teacherID作为输入参数，我该如何为每位教师获取它。
我想在Asp.net前端页面显示报告。
Consider 1254 teacher , he is taking a batch from 9:00 AM to 10:00AM
then 11:30 AM to 1:00PM so he has 10:00AM to 11:30 AM free slot . How do I fetch it for each teacher if I give teacherID as input paramater .
I want to display the report in Asp.net front end page.
推荐答案
你需要重新整理你的数据，因为它的存储过于复杂你的问题
天M / W / F - >你应该单独列出 。最简单的方法是每天记录一次，剩下的数据重复。
备选：使用ID并将工作日分发在一个单独的表中作为参考。
完成后，您可以创建（动态地或更容易地，作为tmp表）每天的列表 - 单独
现在 - 让我们假装您将每个项目的数据重新组织为单独的日子（更大的表格），这样每条记录现在只需一个开始和结束时间就可以在一天内显示一个人。 />
您现在可以按日期和时间对它们进行排序（您是否开始看到策略？）
差距现在可以使用相邻记录之间的SQL datetime DATEDIFF（）函数计算，将记录n的结束时间与记录n + 1的开始时间进行比较。如果> 0，那么你有一个空白（日期是每个记录的一部分）。
你需要设置你的边界条件，例如一天的开始时间，一天的结束时间，当前没有人分配的日子。
许多问题的关键是你的桌子适当的架构 - 多思考一下像电脑而不是人。想想在你的计算中实际使用M / W / F和M / W / Th是多么困难！
修复数据组织后，最困难的是part将在相邻的行上运行。
You need to reorganize your data quite a bit as its storage is over-complicating your problem
Days M/W/F -> you should have them listed separately. Simplest way is a single record for each day, remaining data duplicated.
Alternative: use the ID and have the work day distribution in a separate table by reference.
With that done, you can create (dynamically, or more easily, as a tmp table) a list for each day - individually
Now - lets pretend that you have your data reorganized for each item as individual days (a much larger table) so that each record now shows a single person on a single day with a single start and end time.
You can now sort them by date and time (are you beginning to see the strategy?)
Gaps can now be calculated using the SQL datetime DATEDIFF() function between adjacent records, comparing the end time of record n with the start time of record n+1. If > 0, then you have a gap (date is part of each record).
You will need to set up your boundary conditions for things such as start time for a day, end time for a day, days with no one currently assigned.
The key to many problems is an appropriate schema for your table(s) - thinking a bit more like a computer and not a human. Think how difficult it would be to actually use M/W/F vs M/W/Th in your calculations!
Once you fix your data organization, the most difficult part will be operating on adjacent rows.
DECLARE @tbl AS TABLE ( Id INT IDENTITY (1,1),Teacherid INT,StartTime DATETIME,EndTime DATETIME )
INSERT INTO @tbl
(Teacherid, StartTime, EndTime)
VALUES
(1254, '2007-11-29 09:00 AM','2007-11-29 10:00 AM'),
(1254, '2007-11-29 11:30 AM','2007-11-29 1:00 PM'),
(1254, '2007-11-30 09:00 AM','2007-11-30 10:00 AM'),
(1254, '2007-11-30 11:30 AM','2007-11-30 1:00 PM'),
(1254, '2007-12-01 09:00 AM','2007-12-01 10:00 AM'),
(1254, '2007-12-01 11:30 AM','2007-12-01 1:00 PM')
SELECT t.EndTime, t2.StartTime
FROM @tbl t LEFT JOIN @tbl t2 ON t2.Id = t.Id+1
请仔细查看我已经输入的表格，而不仅仅是间隔时间
希望你理解，而这只是一个例子，你可以决定你自己的逻辑
please take close look at the table strucrure i have entered all days not just the interval
hope you understand and more over this is just a sample you may decide your own logic
这篇关于使用日期差异计算空闲时段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
