×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Missing Records

Missing Records

Missing Records

(OP)
I am trying to modify an existing query to show when a person has 0 hours worked. I need all the records listed in the Exc Table and only the HoursWorked Field from the Records Table when there is a matching record in the Exc Table, if there is not a record that matches the record in the Exc Table then I still want to return the Exc Table record with an HoursWorked of 0.00. The existing query will not show a record that is in the Exc Table if it is not also in the Records Table. I have been working on this for days with no luck, please help. Here is the query...

SELECT     Exc.ExceptionOK, Exc.Comment, Exc.Manager, Exc.CreateDate, Exc.MaintDate, Records.HoursWorked, Exc.Employee, Exc.ExceptionDate
FROM         (SELECT     employee_account_name AS Employee, CONVERT(varchar(10), start_date_time, 101) AS TimeDate, CONVERT(Decimal(9, 2),
                                              SUM(TimePeriod)) AS HoursWorked
                       FROM          (SELECT     EmpMaster.employee_account_name, dbo.Time_Record.start_date_time, DATEDIFF(minute, dbo.Time_Record.start_date_time,
                                                                      dbo.Time_Record.end_date_time) / 60.0 AS TimePeriod
                                               FROM          dbo.Time_Record RIGHT OUTER JOIN
                                                                      dbo.Employee_Master AS EmpMaster ON
                                                                      dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name
                                               WHERE      (EmpMaster.supervisor <> 'Y') AND (EmpMaster.employee_account_name = @empAccount)) AS TimeRecords
                       GROUP BY employee_account_name, CONVERT(varchar(10), start_date_time, 101)) AS Records RIGHT OUTER JOIN
                      dbo.TimeExceptions AS Exc ON Records.Employee = Exc.Employee AND Records.TimeDate = Exc.ExceptionDate
WHERE     (Records.HoursWorked > @overtime) OR
                      (Records.HoursWorked < @undertime)
ORDER BY Records.Employee, Records.TimeDate

RE: Missing Records

The OUTER join is defeated by the WHERE clause.
I'd replace this:
FROM dbo.Time_Record RIGHT OUTER JOIN dbo.Employee_Master AS EmpMaster
ON dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name
WHERE (EmpMaster.supervisor <> 'Y') AND (EmpMaster.employee_account_name = @empAccount)
with this:
FROM dbo.Time_Record RIGHT OUTER JOIN dbo.Employee_Master AS EmpMaster
ON dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name
AND EmpMaster.supervisor <> 'Y' AND EmpMaster.employee_account_name = @empAccount

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Missing Records

(OP)
Thanks but that didn't change my results. It still does not return a record in the Exc Table if one does not exist in the Records Table.

RE: Missing Records

Replace this:
dbo.TimeExceptions AS Exc ON Records.Employee = Exc.Employee AND Records.TimeDate = Exc.ExceptionDate
WHERE     (Records.HoursWorked > @overtime) OR
                      (Records.HoursWorked < @undertime)
with this:
dbo.TimeExceptions AS Exc ON Records.Employee = Exc.Employee AND Records.TimeDate = Exc.ExceptionDate
AND (Records.HoursWorked > @overtime OR Records.HoursWorked < @undertime)

or this:
dbo.TimeExceptions AS Exc ON Records.Employee = Exc.Employee AND Records.TimeDate = Exc.ExceptionDate
AND NOT (Records.HoursWorked BETWEEN @undertime AND @overtime)

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Missing Records

(OP)
Thank you very much PHV, that solved my problem.

RE: Missing Records

(OP)
Working to modify the query so it will add all the HoursWorked for a person together on one line for a given date range instead of listing each instance of a person and their time for each day on multiple lines so I can select the person to be shown if the total HoursWorked for a week are less than 35 hours. I would appreciate any assistance with this.


SELECT     Records.Employee, Records.TimeDate AS ExceptionDate, TimeExc.ExceptionOK, TimeExc.Comment, TimeExc.Manager, TimeExc.CreateDate,
                      TimeExc.MaintDate, Records.HoursWorked
FROM         (SELECT     employee_account_name AS Employee, CONVERT(varchar(10), start_date_time, 101) AS TimeDate, SUM(TimePeriod)
                                              AS HoursWorked
                       FROM          (SELECT     EmpMaster.employee_account_name, dbo.Time_Record.start_date_time, DATEDIFF(minute, dbo.Time_Record.start_date_time,
                                                                      dbo.Time_Record.end_date_time) / 60.0 AS TimePeriod
                                               FROM          dbo.Time_Record RIGHT OUTER JOIN
                                                                      dbo.Employee_Master AS EmpMaster ON dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name)
                                              AS TimeRecords
                       GROUP BY employee_account_name, CONVERT(varchar(10), start_date_time, 101)) AS Records RIGHT OUTER JOIN
                      dbo.Employee_Master AS EmpMaster ON Records.Employee = EmpMaster.employee_account_name RIGHT OUTER JOIN
                      dbo.TimeExceptions AS TimeExc ON EmpMaster.employee_account_name = TimeExc.Employee AND Records.TimeDate = CONVERT(varchar(10),
                      TimeExc.ExceptionDate, 101)
WHERE     (EmpMaster.supervisor <> 'Y') AND (EmpMaster.time_end_date IS NULL) AND (Records.HoursWorked > @overtime OR
                      Records.HoursWorked < @undertime)
UNION
SELECT     Employee, ExceptionDate, ExceptionOK, Comment, Manager, CreateDate, MaintDate, '0' AS Expr1
FROM         dbo.TimeExceptions AS TExcp
WHERE     (CONVERT(varchar(10), ExceptionDate, 101) NOT IN
                          (SELECT     CONVERT(varchar(10), start_date_time, 101) AS Expr1
                            FROM          dbo.Time_Record AS TR
                            WHERE      (employee_account_name = '%') AND (start_date_time >= @sdt) AND (start_date_time <= @edt))) AND (Employee = '%') AND
                      (ExceptionDate <= @edt) AND (ExceptionDate >= @sdt)

RE: Missing Records

(OP)
Sorry I wasn't thinking straight. I have dropped the UNION statement since I don't need the TimeExceptions table for this query.

RE: Missing Records

(OP)
Here is my latest attempt at it but it is still not right.

SELECT     Records.Employee, Records.HoursWorked, Records.TimeDate
FROM         (SELECT     employee_account_name AS Employee, CONVERT(varchar(10), start_date_time, 101) AS TimeDate, SUM(TimePeriod)
                                              AS HoursWorked
                       FROM          (SELECT     EmpMaster.employee_account_name, dbo.Time_Record.start_date_time, DATEDIFF(minute, dbo.Time_Record.start_date_time,
                                                                      dbo.Time_Record.end_date_time) / 60.0 AS TimePeriod
                                               FROM          dbo.Time_Record RIGHT OUTER JOIN
                                                                      dbo.Employee_Master AS EmpMaster ON dbo.Time_Record.employee_account_name = EmpMaster.employee_account_name)
                                              AS TimeRecords
                       GROUP BY employee_account_name, CONVERT(varchar(10), start_date_time, 101)) AS Records RIGHT OUTER JOIN
                      dbo.Employee_Master AS EmpMaster ON Records.Employee = EmpMaster.employee_account_name
WHERE     (EmpMaster.supervisor <> 'Y') AND (EmpMaster.time_end_date IS NULL) AND (Records.TimeDate >= @startDate) AND
                      (Records.TimeDate <= @endDate)

RE: Missing Records

(OP)
Thanks r937

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close