July 13, 2010 at 9:38 am
In the past I have used Select Distinct to avoid this issue but I really want to stop using that 'shortcut'.
I have a table that collects work orders. A second table collects progress notes entered about that work order. (I have attached the create statments for the tables to this post)
My main query returns every work order entered with some contraints shown in my where clause below.
I have written a scalar function that concatenates every note entered into the workorderProgressNotes table for the workorder. I'd like this to add a column to the coumns returned by the main query. Currently the main query is returning a record for each note pulled by this function!
Can someone show me a better way of doing this? I'm tired of long running queries which result from using Distinct!
SELECT WorkOrder.WorkOrderID,
convert(varchar,WorkOrder.EnteredDate,101) as SubmittedDate,
WorkOrder.Location,
dbo.fnGetStaffFullNameWithID(SupervisorReview_StaffRosterID,'') + ' ' + convert(varchar,SupervisorReviewDate,101) SuperVisorApproval, 'Category: ' + zWorkOrderCategory.Description + '
Priority: ' + zPriority.[Description] + '
Date Required: ' + convert(varchar,WorkOrder.DateRequired,101) + '
Description: ' + WorkOrder.WorkDescription as Details,
dbo.fnGetWorkOrderApproval(WorkOrder.WorkOrderID) as Approval,
ProductionDB.dbo.fnGetWorkOrderProgressNote(WorkOrder.WorkOrderID)
FROM WorkOrder LEFT OUTER JOIN WorkOrderProgressNote ON
WorkOrder.WorkOrderID = WorkOrderProgressNote.WorkOrderID INNER JOIN zPriority ON
WorkOrder.PriorityID = zPriority.PriorityID INNER JOIN zWorkOrderCategory ON
WorkOrder.WorkOrderCategoryID = zWorkOrderCategory.WorkOrderCategoryID INNER JOIN StaffRoster ON WorkOrder.StaffRosterID = StaffRoster.StaffRosterID
WHERE WorkOrder.isValid = 1 AND
WorkOrder.StaffRosterID = 456 AND
WorkOrder.SupervisorReviewStatusID < 3
Thank you
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
July 13, 2010 at 11:00 am
Please provide some sample data to populate the tables you provided the DDL for.
That would allow us to see the results you're getting with your current query.
What we also would like to see is the result you'd like to get based on your sample data.
If the functions in your query are relevant to get the desired result, post those too.
Based on what I understood so far I probably would use a subquery or CTE to get the concatenated notes and join that back to the WorkOrder table...
July 15, 2010 at 11:41 am
Sorry for the dlay. So many fires to put out with such a tiny bucket. I have attached an insert statement that will put one record into the workorder table and two records into the WorkOrderProgressNotes field (assuming this is the first time you have ran an insert and the WorkOrderID will be '1').
I appreciate the help with this.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
July 15, 2010 at 3:03 pm
Based on your sample data I would use the following concept.
Unfortunately, I was unable to run your original query since there are tables and functions missing. So I had to make something up:
;
WITH cte AS
(
SELECT
WorkOrderID,
STUFF((SELECT ', ' + Note
FROM WorkOrderProgressNote t2
WHERE t2.WorkOrderID = t1.WorkOrderID
FOR XML PATH(''), TYPE
).value('./text()[1]', 'NVARCHAR(MAX)'),1,2,'') AS Note
FROM
WorkOrderProgressNote t1
GROUP BY
WorkOrderID
)
SELECT WorkOrder.*,cte.Note
FROM cte
INNER JOIN WorkOrder
ON workorder.WorkOrderID = cte.WorkOrderID
/* result set
WorkOrderID StaffRosterID SupervisorReviewStatusID SupervisorReviewDate SupervisorReview_StaffRosterID PriorityID DateRequired WorkOrderCategoryID Location WorkDescription AdminReviewStatusID AdminReviewDate AdminReview_StaffRosterID WorkOrderAssignedTo EstimatedCompletionDate CompletedDate Completed_StaffRosterID EnteredDate EnteredBy isValid Note
1000 944 1 2010-07-12 00:00:00.000 456 1 2010-08-01 00:00:00.000 3 Warren Plastering downstairs wall 1 2010-07-14 00:00:00.000 371 359 2010-08-01 00:00:00.000 NULL NULL 2010-07-12 00:00:00.000 944 1 This is Note one, This is Note Two
*/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply