Checking query in SQL

Question

1.00/5 (1 vote)

See more:

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.

Posted 31-Aug-16 0:07am

basitsar

Updated 31-Aug-16 10:00am

Andy Lanng

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ben J. Boyle · Answer 1 · 2016-08-31T06:06:00

Solution 2

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.

Posted 31-Aug-16 6:06am

Ben J. Boyle

Comments

Maciej Los 31-Aug-16 16:00pm

5ed!

Maciej Los · Answer 2 · 2016-08-31T10:00:00

Solution 3

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

Posted 31-Aug-16 10:00am

Maciej Los

Member 12696609 · Answer 3 · 2016-08-31T00:27:00

Solution 1

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.

Posted 31-Aug-16 0:27am

Member 12696609

Comments

basitsar 31-Aug-16 6:35am

Dear, there is no relation between thoes table only need to check start & endDate with operation table and check attendance table if employee is in between start & endDate then show this table.

Thanks
Basit.