Solved

Joining Statement in SQL

Posted on 2014-04-30
7
34 Views
Last Modified: 2016-06-05
Hi,

The following statement generates output of attendance records (from Honeywell based access control system) and Lotus Notes based workflow application pulled the information to publish the records.  

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date,
MIN(a.TIMESTAMP) AS First_in,
MAX(a.TIMESTAMP) AS Last_Out
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '29-apr-2014'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
where b.CardNumber = '1'

Recently, we have implemented Biometrics equipments at various locations and IN/OUT of an employee is getting stored in a separate Table namely "Timewatch_attendance", which has the following four fields:

LastDate,
FirstIN,
LastOut,
EmpID  : EmpID is unique in both the case

Now, I am facing challenges in joining these two to generate a single output file, which I can finally display through Lotus Notes application.

The query should check and display First_IN & FirstIn (MIN) and Last_Out and LastOut (Max) in both the table/view and generate the output accordingly.

Can you help me please.

Regards,
0
Comment
Question by:AmitavaCh
  • 3
  • 2
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40032107
will there be the EMPID in both tables? or maybe just in either of the 2 tables?
you eventually may need a FULL OUTER JOIN...
SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date,
MIN(a.TIMESTAMP) AS First_in,
MAX(a.TIMESTAMP) AS Last_Out
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '29-apr-2014'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
JOIN Timewatch_attendance tw on tw.EmptID = ??.emp_id
where b.CardNumber = '1' 

Open in new window

0
 

Author Comment

by:AmitavaCh
ID: 40032136
Field 'EmpID' exists in both the table.

Since EmpID is common, query to check whether the person marked his presence at Office location or site location, similarly while leaving.

E.g. Today, I come to office and marked my presence through HID Based Card in the morning at 9:45 a.m. and left at 12:30 for our site. At 1:30 I  swiped my finger at Biometric machines and finally at 7:00 p.m. and left from there, next day my FirstIN will be 9:45 a.m. and lastOut will be at 7:00 p.m. (which is currently stored in Timewatch_attendance table).

Thank you
0
 
LVL 40

Expert Comment

by:Sharath
ID: 40033011
Provide some sample data and expected result.
What is your SQL Server version?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:AmitavaCh
ID: 40033907
Hi,
Enclosed please find the sample data and expected result.
I am using SQL Server 2005 STD.

Thank you,
EE-Solution.pptx
EE-Solution.xlsx
0
 

Author Comment

by:AmitavaCh
ID: 40034259
Hi Hengel,

Yes, Full Outer Join is required - I also need to extract Min & Max time once again to get the desired result.

SELECT * FROM
(SELECT a.link3,
REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') new_date,
MIN(a.TIMESTAMP) AS First_in,
MAX(a.TIMESTAMP) AS Last_Out
from History a
where REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-') = '29-apr-2014'
GROUP BY a.link3, REPLACE (CONVERT(VARCHAR(11),TIMESTAMP, 106),' ', '-')
) A
INNER JOIN emp_sms_email b ON a.link3 = b.CardHolderID
FULL OUTER JOIN TimeWatch_Attendance ON b.EmpID=TimeWatch_Attendance.Emp_Code
where b.CardNumber = '1';

The above query is giving me all the entries (dates) of Biometrics for Card # '1', however, I need the record only for 29-apr-2014 i.e. today-1
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40036443
FULL OUTER JOIN TimeWatch_Attendance ON b.EmpID=TimeWatch_Attendance.Emp_Code
where b.CardNumber = '1';

should then be

FULL OUTER JOIN TimeWatch_Attendance ON b.EmpID=TimeWatch_Attendance.Emp_Code
AND b.CardNumber = '1'
and b.LastDate >= convert(date, getdate()-1)
and b.LastDate < convert(date, getdate())
;
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now