I wish I'd paid more attension in SQL class!

[HairyMonkeyMan]

:confused: :confused: :confused:

I'm using Microsoft SQL Server

Hello there :) This one is a bit of a head scratcher. I haven't been able to get this into a query that would finish running before christmas!!

Basically, I've been developing something along the lines of TimeTracker (using asp.net), but with a bit more to it. Members of staff can enter their time and mileage expenses through this system (which is working). Another part of the system is where the project managers come along and approve the time and mileage expenses. I want to provide the project manager with a list of everyone that has entered time or mileage expenses that have not yet been approved - so they can approve, edit or delete the time/mileage records.

Have a look at this ERD:
http://www.mnd.co.uk/mike/pmacs_erd.gif

What I'm interested in here is the Project, Phase, Time, Mileage and Staff tables.

The project manager's staffref is in the Project table under Engineer1.

I have been playing around with the following queries:

--Selecting Staff Records first (this one selects in reasonable time, but I
--don't get all the info needed:
SELECT Staff.FirstName + ' ' + Staff.Surname AS StaffName, Staff.StaffRef
FROM Staff
INNER JOIN Times ON (Times.StaffRef = Staff.StaffRef)
WHERE Times.Approved = '0'

--Selecting Project Records first (this one would get all the info needed,
--but it would take a very long time to run:
SELECT null FROM Project
INNER JOIN [Phase] ON (Project.ProjectNo = Phase.ProjectNo)
INNER JOIN [Times] ON (Times.ProjPhaseID = Phase.ProjPhaseID)
INNER JOIN [Mileage] ON (Mileage.StaffRef = Staff.StaffRef)
WHERE Engineer1 = @Manager AND Times.Approved = '0' OR Mileage.Approved = '0'

If anyone could help with this, that would be excellent. Equally, if anyone just wants to laugh and point - thats cool as well :wave:

[jdt]

If it was me I would have displayed mileage and time separately. It will execute faster and your “approver” will have a clear view of what (s)he is doing.

SELECT “your attributes” FROM Project
INNER JOIN [Phase] ON (Project.ProjectNo = Phase.ProjectNo)
INNER JOIN [Times] ON (Times.ProjPhaseID = Phase.ProjPhaseID)
WHERE Engineer1 = @Manager AND Times.Approved = '0'

SELECT “your attributes” FROM Project
INNER JOIN [Phase] ON (Project.ProjectNo = Phase.ProjectNo)
INNER JOIN [Mileage] ON (Mileage.ProjPhaseID = Phase.ProjPhaseID)
WHERE Engineer1 = @Manager AND Mileage.Approved = '0'

Your join doesn’t make sense to me. You link Mileage to Staff and not Project. If you should join both to Project then you will only return records that have both Mileage and Time for the same Phase. Probably not what you had in mind. You could look at LEFT OUTER JOIN and UNION, but personally I would go for my first suggestion.

Hope that was of any help.

[HairyMonkeyMan]

Thanks for helping jdt :thumb:

If it was me I would have displayed mileage and time separately

I will be executing the time and mileage queries when I am creating the lists, the same way I am in the time/expense entry page:
http://www.mnd.co.uk/mike/TimeExpenses.gif

You link Mileage to Staff and not Project. If you should join both to Project then you will only return records that have both Mileage and Time for the same Phase.

Thats where the problem lays. What I'm looking for is a list of names and not a list of project phases. The pm can use this list to select the person who has time/expenses waiting to be approved.

I'll check out your idea about union or left outer join,
Thanks again and Regards ;)

[jdt]

Here's a small start - hope I understood your problem this time. Just include the ProjectPhaseID in the "X" subquery and you can join this to your project / Program manager.

Select s.* from [Staff] s,
(SELECT StaffRef FROM [Mileage]
UNION ALL
SELECT StaffRef FROM [Time]) X
where x.StaffRef = s.StaffRef;

Cheers

[HairyMonkeyMan]

Hey jdt,

Thanks man, this is what I'm going with:

SELECT DISTINCT s.FirstName + ' ' + s.Surname AS StaffName, s.StaffRef from [Staff] s,
(SELECT StaffRef, Approved FROM [Mileage]
UNION ALL
SELECT StaffRef, Approved FROM [Times]) x
where x.StaffRef = s.StaffRef AND x.Approved = '0';

exactly what I needed (still playing with it to get project manager's ref.. Reckon I can handle that) :thumb:

edit: This is the finished query:
SELECT DISTINCT s.FirstName + ' ' + s.Surname AS StaffName, s.StaffRef
FROM [Staff] s,
(SELECT StaffRef,ProjPhaseID, Approved FROM [Mileage]
UNION ALL
SELECT StaffRef, ProjPhaseID, Approved FROM [Times]) x
INNER JOIN Phase ON (Phase.ProjPhaseID = x.ProjPhaseID)
INNER JOIN Project ON (Project.ProjectNo = Phase.ProjectNo)
WHERE x.StaffRef = s.StaffRef AND x.Approved = '0' AND Project.Engineer1 = @Engineer1;

:wave:

[cjard]

You mix two types of joining there, which is not good practice and some database vendors dont support it:

SELECT DISTINCT s.FirstName + ' ' + s.Surname AS StaffName, s.StaffRef
FROM [Staff] s,
(SELECT StaffRef,ProjPhaseID, Approved FROM [Mileage]
UNION ALL
SELECT StaffRef, ProjPhaseID, Approved FROM [Times]) x
INNER JOIN Phase ON (Phase.ProjPhaseID = x.ProjPhaseID)
INNER JOIN Project ON (Project.ProjectNo = Phase.ProjectNo)
WHERE x.StaffRef = s.StaffRef AND x.Approved = '0' AND Project.Engineer1 = @Engineer1;

SELECT DISTINCT s.FirstName + ' ' + s.Surname AS StaffName, s.StaffRef
FROM [Staff] s INNER JOIN
(SELECT StaffRef,ProjPhaseID, Approved FROM [Mileage]
UNION ALL
SELECT StaffRef, ProjPhaseID, Approved FROM [Times]) x
ON x.StaffRef = s.StaffRef
INNER JOIN Phase ON (Phase.ProjPhaseID = x.ProjPhaseID)
INNER JOIN Project ON (Project.ProjectNo = Phase.ProjectNo)
WHERE x.Approved = '0' AND Project.Engineer1 = @Engineer1;


Additionally.. why are you using DISTINCT? most people do this because their joins are broken and they end up with multiple identical rows in the result. Try and avoid using distinct; fix the cuase of the repeated rows rather than use DISTINCT as a fudge at the end to strip them out.