【C#】根據開始時間和結束時間篩選存在的信息

背景

業務需求中,需要根絕開始時間和結束時間篩選一段時間內的任務存在個數。

示例圖片

根據開始時間 9:00到 結束時間11:00 篩選信息

總共有這么四種情況可能出現

示例圖片

插入測試數據

CREATE TABLE [dbo].[Schedule] (
[ScheduleId] int NOT NULL IDENTITY(1,1) ,
[ScheduleTheme] nvarchar(100) NOT NULL ,
[ScheduleContent] nvarchar(500) NULL ,
[ScheduleBeginTime] datetime NULL ,
[ScheduleEndTime] datetime NULL ,
[CreateTime] datetime  NULL 
)
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('測試標題001', '', '2020-07-04 10:00:00', '2020-07-08 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('測試標題001', '', '2020-07-06 10:00:00', '2020-07-09 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('測試標題001', '', '2020-07-04 10:00:00', '2020-07-06 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('測試標題001', '', '2020-07-06 10:00:00', '2020-07-06 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('測試標題001', '', '2020-07-01 10:00:00', '2020-07-04 11:00:00', GETDATE());
INSERT INTO [Demo].[dbo].[Schedule] ( [ScheduleTheme], [ScheduleContent], [ScheduleBeginTime], [ScheduleEndTime], [CreateTime] ) VALUES ('測試標題001', '', '2020-07-08 10:00:00', '2020-07-14 11:00:00', GETDATE());

示例LINQ 代碼

var beginDate=new DateTime(“2020-07-07”);
var endDate=new DateTime(“2020-07-07”);

var tasks = demo.Where(n => 
(n.ScheduleBeginTime > endDate && n.ScheduleBeginTime > beginDate)||
(n.ScheduleBeginTime > beginDate && n.ScheduleEndTime < endDate) || 
(n.ScheduleEndTime < endDate && n.ScheduleEndTime > beginDate) ||
(n.ScheduleEndTime > endDate && n.ScheduleBeginTime < beginDate);

sql 示例

SELECT * FROM Schedule WHERE 1!=1
 or (ScheduleBeginTime < '2020-07-07'  AND  ScheduleBeginTime > '2020-07-05')  --查詢1種情況
 or (ScheduleBeginTime > '2020-07-05'  AND  ScheduleEndTime < '2020-07-07' )   --查詢2種情況
 or (ScheduleEndTime < '2020-07-07'  AND  ScheduleEndTime > '2020-07-05')    --查詢3種情況
 or (ScheduleEndTime > '2020-07-07'  AND  ScheduleBeginTime < '2020-07-05') --查詢4種情況

posted @ 2020-07-17 23:57  程序胖  閱讀(...)  評論(...編輯  收藏
最新chease0ldman老人