>-----Original Message-----
>Hi
>
>I'm in the process of creating a maintenance cost report
for the Pulp
>and Paper company for whom I work. The company uses a
Maximo
>maintenance management software with a semi-normalised
SQL Server 2000
>backend. This stores the data from which the report is to
be derived.
>
>All maintenance costs at the mill are managed through
workorders and
>workorders can be set up in parent child hierarchies to
manage shuts
>and capital work etc.
>I'm attempting to create report to display shut/capital
costs. I have
>got the workorder hierarchy successfully captured using a
cursor loop
>and now i wish to also calculate a sum of related costs
for each
>workorder from various other tables such as purchase
requisitions and
>material requisitions . Unfortunately there are multiple
lines in many
>of the tables that relating back to one workorder. I have
attempted
>this with left outer joins and aggregates but aren't
having much luck.
>If someone could point me in the right disrection I would
be very
>appreciative. I will include a diagram and my stored
procedure to help
>clarify things.
>
>Thankyou for your time
>Michael Black
>
>
>Workorder1---M Purchase Requisitions=> Sum Puuchase Reqs
cost For WO
> 1---M Service Requisitions => Sum Service Reqs
cost for WO
> 1---M Service Receipts => Sum Service Receipts
cost for that
>WO
> 1---1 Location Description
> Workorder1---M Purchase Requisitions=> Sum Puuchase
Reqs cost For
>WO
> 1---M Service Requisitions => Sum Service
Reqs cost for
>WO
> 1---M Service Receipts => Sum Service
Receipts cost for
>that WO
> 1---1 Location Description
> Workorder1---M Purchase Requisitions=> Sum
Puuchase Reqs cost
>For WO
> 1---M Service Requisitions => Sum
Service Reqs cost
>for WO
> 1---M Service Receipts => Sum Service
Receipts cost
>for that WO
> 1---1 Location Description
> Workorder 1---M Purchase Requisitions=> Sum
Purchase Reqs
>costFor WO
> 1---M Service Requisitions => Sum
Service Reqs
>cost for WO
> 1---M Service Receipts => Sum
Service Rec Costs
>for WO
> 1---1 Location Description
>
>
>
>
>
>
>
>
>
>CREATE PROCEDURE CHHT_GetCapExpend
> @WONUM VarChar (20)
> /* @STARTDATE DATETIME,
> @ENDDATE DATETIME */
>
>AS
>
>SELECT Distinct Wo.Wonum, WO.Description,
>WO.parent,WO.location,WO.estlabhrs,WO.estlabcost,WO.estmat
cost,
>WO.esttoolcost,WO.estservcost,SUBSTRING(WO.location,5,2),
>WO.ReportDate,
>SUBSTRING(WO.Glaccount,8,6), WO.worktype, Wo.wopm6,
l.description,
>l1.description, wo.glaccount,
>SUBSTRING(WO.glaccount,12,2), Sum(SR.linecost), Sum
(MT.linecost),
>Sum(SPRL.Linecost), Sum(MPRL.Linecost),
>Sum(MR.TotalCost), Sum(POL.linecost)
>
>From WORKORDER WO
> LEFT OUTER JOIN Locations L on
>SUBSTRING(WO.location,1,4) = L.location
> LEFT OUTER JOIN Locations L1 on
SUBSTRING(WO.location,1,6) =
>L1.location
> LEFT OUTER JOIN ServRecTrans SR on
WO.Wonum = SR.Refwo
> LEFT OUTER JOIN MatUseTrans MT on
Wo.Wonum = MT.Refwo
> LEFT OUTER JOIN PRLine SPRL on WO.Wonum
= SPRL.Refwo
> LEFT OUTER JOIN PRLine MPRL on WO.Wonum
= MPRL.Refwo
> LEFT OUTER JOIN PR on SPRL.PRNUM =
PR.PRNUM
> LEFT OUTER JOIN PR PRPR on MPRL.PRNUM =
PRPR.PRNUM
> LEFT OUTER JOIN MR on WO.Wonum =
MR.Wonum
> LEFT OUTER JOIN POLine POL on WO.Wonum
= POL.RefWo
>
>
>Where WO.WONUM = @wonum
>Group By Wo.Wonum
>/*and SUBSTRING(WO.location,1,4) = L.location and
>SUBSTRING(WO.location,1,6) = L1.location and*/
>/* WO.reportdate between @startdate and @enddate */
>DECLARE SubWONums CURSOR LOCAL FOR
> SELECT WONUM FROM WORKORDER WHERE PARENT = @WONUM
>OPEN SubWONums
> FETCH NEXT FROM SubWONums INTO @WONUM
> WHILE @@FETCH_STATUS=0 BEGIN
> EXEC CHHT_GetCapExpend @WONUM /*,
@STARTDATE, @ENDDATE */
> FETCH NEXT FROM SubWONums INTO @WONUM
> END
>CLOSE SubWONums
>DEALLOCATE SubWONums
>.