Missing Records 1

[acesn8s]

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

 

[PHV]

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
FAQ181-2886

 

[acesn8s]

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.

 

[PHV]

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
FAQ181-2886

 

[acesn8s]

Thank you very much PHV, that solved my problem.

 

[acesn8s]

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)

 

[acesn8s]

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

 

[acesn8s]

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)

 

[r937]

FYI you should be in forum183

the ANSI SQL forum is for ANSI SQL, not microsoft's version

r937.com | rudy.ca

 

[acesn8s]

Thanks r937