在SQL中检查查询 [英] Checking query in SQL

问题描述

我有一张名为Attendance的桌子





Atn_No int

Atn_Date date

atn_EmpNo varchar





我有另一张桌子叫做操作



Opn_No int

OPn_StartDate date

Opn_EndDate date

Opn_Activity varchar

Opn_ActivityHour int



我想通过帮助Start& amp;结束日期谁的员工正在进行该操作



以下是操作表数据

 Opn_StartDate Opn_EndDate Opn_Activity Opn_ActivityHour 
 01-01-2016 03-01-2016 ABC 1 
 04-01-2016 06-01-2016 XYZ 2 
 07-01-2016 08 -01-2016 lmn 4 

以下出勤表DATA < br $>

 Atn_No Atn_Date Atn_EmpNo 
 Atn-1 02-01-2016 A-1 
 Atn-2 02-01-2016 A-2 
 Atn_3 05-01-2016 A-3 

谢谢

巴斯特。



我的尝试：



我在想什么创建一个临时表首先用节奏保存所有操作记录如果在开始和结束之间的出勤保存该记录，则在Ray循环的帮助下添加检查出勤。但我正在寻找sql查询，如果easiy然后不需要创建这个方法。

解决方案

假设你没有两个重叠日期的操作，试试这个：

从操作中选择* 
 o 
左外连接参加a.atn_date> = o.opn_startdate和a。 atn_date< = o.opn_enddate 

如果你有重叠的操作，那么你需要将SOMETHING放在你的数据库中以链接两个记录。

我强烈建议你阅读：

连续数字中的岛屿和差距 [ ^ ]

序列中的差距和岛屿的SQL - S实施讲座 [ ^ ]

表1



Opn_StartDate Opn_EndDate Opn_Activity Opn_ActivityHour Atn_EmpNo（外键）



表2



Atn_No Atn_Date Atn_EmpNo（主键）









现在只需使用加入即可获得答案。

I have a table called Attendance


Atn_No int
Atn_Date date
Atn_EmpNo varchar


I have another table Called Operation

Opn_No int
OPn_StartDate date
Opn_EndDate date
Opn_Activity varchar
Opn_ActivityHour int

What i want to check with Operation with the help Start & End Date Who's employees was working on that operation

Below is Operation Table DATA

Opn_StartDate      Opn_EndDate           Opn_Activity         Opn_ActivityHour
01-01-2016         03-01-2016            ABC                      1
04-01-2016         06-01-2016            XYZ                      2
07-01-2016         08-01-2016            lmn                      4

Below Attendance Table DATA

Atn_No            Atn_Date           Atn_EmpNo
Atn-1             02-01-2016          A-1
Atn-2             02-01-2016          A-2
Atn_3             05-01-2016          A-3

Thanks
Basit.

What I have tried:

What i was thinking create a temporay table first save all the operation records with temporay table and with the help of while loop add check the attendance if the attendance in between start and enddate save that records. but im looking for sql query if easiy then no need to create this method.

解决方案

Assuming you do not have two operations with overlapping dates, try this:

Select * 
 from operation o
 left outer join Attendance a on a.atn_date >= o.opn_startdate and a.atn_date <= o.opn_enddate

If you have overlapping operations, then you'll need to put SOMETHING in your database to link the two records.

I'd strongly recommend to read this:
Islands and Gaps in Sequential Numbers[^]
The SQL of Gaps and Islands in Sequences - Simple Talk[^]

table 1

Opn_StartDate Opn_EndDate Opn_Activity Opn_ActivityHour Atn_EmpNo(foreign key)

table 2

Atn_No Atn_Date Atn_EmpNo(primary key)




now just use join and you will get your answer.

这篇关于在SQL中检查查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！