Hi Lynn,

Thanks for the reply. Any guidance for better solutions are always welcome. I have to say I am glad to have found this community because the contributors (read as experts) are very supportive.

I am trying to keep things as simple as possible and the data loads (~1000 records once a week) are not that heavy. Unfortunately, it means I will make some sacrifices in scaleability (performance) in order to get it done simply (quickly) from my knowledge of TSQL.

The function is pretty simple but it seems to be getting modified after more people see the out put. That's expected I guess.

CREATE FUNCTION [dbo].[GetPmSteps]

(

-- Add the parameters for the function here

@eq_id char(12),

@bl_id char(8),

@fl_id char(4),

@rm_id char(8),

@dateToDo datetime

)

RETURNS VARCHAR(MAX)

AS

BEGIN

-- Declare the return variable here

DECLARE @steps VARCHAR(MAX)

SELECT @steps = COALESCE(@steps + CHAR(13) + CHAR(13), '') + '====== ' + UPPER(pm_procedure.description) + ' ======' + CHAR(13) + pm_procedure.instructions

FROM pm_schedule INNER JOIN

pm_procedure ON pm_schedule.pmp_id = pm_procedure.pmp_id

WHERE (pm_schedule.date_next_todo <= @dateToDo) AND (pm_schedule.active = 1)

AND (@eq_id IS NULL OR pm_schedule.eq_id = @eq_id) AND (@bl_id IS NULL OR pm_schedule.bl_id = @bl_id)

AND (@fl_id IS NULL OR pm_schedule.fl_id = @fl_id) AND (@rm_id IS NULL OR pm_schedule.rm_id = @rm_id)

ORDER BY pm_schedule.interval_type, pm_schedule.interval_freq DESC

-- Return the result of the function

RETURN @steps

END

I then Just call a select statement

SELECT eq_id, bl_id, fl_id, rm_id, dbo.GetPmSteps(eq_id, bl_id, fl_id, rm_id, getdate()) as steps

FROM pm_schedule

WHERE (active = 1) AND (date_next_todo <= getdate())

GROUP BY eq_id, bl_id, fl_id, rm_id

The problem was that I never know how many separate procedures would show up for a given date until run time. The majority will just be one procedure but there are cases when there could be a monthly, quarter, semi, and yearly all show up on the same date range. The idea is to have one "ticket" that has all the steps.

Thanks,

Craig