Professional SP Writer Needed! :)

OK here goes... I've just started work on a project previously created by someone else and i'm having a knightmare getting my head around it!

I have a SQL 2000 database and particularly this SP which I am currently working on:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER Procedure psel_BSListOfServicesDue
(
@DepotID int,
@DateFrom datetime,
@DateTo Datetime
)
AS

SET NOCOUNT ON
SELECT DISTINCT
ACAccountID,
DADeliveryAddressID,
AccountNum = CASE WHEN DADeliveryAddressID Is Null THEN ACAccountNum ELSE DADeliveryAccountNum END,
CustomerName = CASE WHEN DADeliveryAddressID Is Null THEN dbo.fsel_CustomerName(ACCompanyName, ACTitle, ACFirstName, '', ACSurname, ACCACategoryID)
ELSE dbo.fsel_CustomerName(DACompanyName, DATitle, DAFirstName, '', DASurname, ACCACategoryID) END,
PostCode = CASE WHEN DADeliveryAddressID Is Null THEN ACPostCode ELSE DAPostCode END,
AIEAccountInstalledEquipmentID,
InstalledEquipment = IEMake + ' ' + IEModel,
MPLastServiceDate,
MPNextServiceDate,
MPMaintenancePlanID
FROM tblAccountDetails
INNER JOIN tblMaintenancePlans ON ACAccountID = MPACAccountID
LEFT OUTER JOIN tblDeliveryAddresses ON MPDADeliveryAddressID = DADeliveryAddressID
LEFT OUTER JOIN tblAccountInstalledEquipment ON MPMaintenancePlanID = AIEMPMaintenancePlanID
LEFT OUTER JOIN tblInstalledEquipment ON AIEIEInstalledEquipmentID = IEInstalledEquipmentID
WHERE ACBoilerServiceDepotID = @DepotID
AND MPJHJobID Is Null
AND MPPlanTerminationDate Is Null
AND ((MPNextServiceDate >= @DateFrom AND MPNextServiceDate <= @DateTo)
OR @DateFrom Is Null)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Now this works fine but I need to also get another value which is the enginner that last worked on the service. At the moment within tblMaintenancePlans I have a field called MPPreviousJHJobID which holds a job reference number for the previous service. This can be linked to tblBSWorksheets to a field called WSJHJobID, but we need the TOP one as there is a 1 to many relationship. From there we need a field located in tblBSWorksheets called WSWorkSheetID and this links to a field called CLWSWorkSheetID loctaed in table tblBSCalendar. From here we can then get field CLEngineerEMEmployeeID which then links to the field EMEmployeeID within tblEmployees. From that table we then want EMName to be returned as part of the SP!!!

Hope this is clear as i'm not fully understanding it myself! Hope someone can help!?!

Many thanks.

Hi - Well writing a SP is ok, no problems with that, but your explaination is not too clear, and its difficult to understand for a lay man.

To make it simpler instead of using actual name of the tables, replace them with a simpler names and variables such as Table1, Table 2, EmpID, EmpName etc...and so on, depending on your fields in the database, that way I am sure someone will reply as it would be easier to understand.

Khidki Support