Date parsed: 20/10/2007 04:29:44
Date: Sat, 20 Oct 2007 09:29:44 -0500

I think your best approach is to combine the separate queries into a single
query for the join rather than use the proc encapsulation. This will
provide the best performance. The example below simply uses derrived tables
since I don't know your data model.

SELECT *
FROM (
SELECT
tbl_TimeEntry.JHD_JobHeader_PK,
SUM(tbl_TimeEntry.TME_Hours) AS Actual,
tbl_TimeEntry.PHA_Phases_PK
FROM dbo.tbl_TimeEntry
INNER JOIN dbo.tbl_Phases ON
tbl_TimeEntry.PHA_Phases_PK = tbl_Phases.PHA_Phases_PK
GROUP BY
tbl_TimeEntry.JHD_JobHeader_PK,
tbl_TimeEntry.PHA_Phases_PK
) a
JOIN (
SELECT
tbl_JobMaterialItems.JHD_JobHeader_PK,
SUM(tbl_JobEstimatedLabor.JEL_StandardHours *
tbl_JobMaterialItems.JMI_QuantityOrdered) AS EstHours,
tbl_JobMaterialItems.JMI_ShippingPhase_PK,
tbl_Status.STS_Name
FROM dbo.tbl_JobEstimatedLabor
INNER JOIN dbo.tbl_JobMaterialItems ON
tbl_JobEstimatedLabor.JMI_JobMaterialItems_PK =
tbl_JobMaterialItems.JMI_JobMaterialItems_PK
INNER JOIN dbo.tbl_JobHeader ON
tbl_JobMaterialItems.JHD_JobHeader_PK =
tbl_JobHeader.JHD_JobHeader_PK
INNER JOIN dbo.tbl_Status ON
tbl_JobHeader.STS_Status_PK = tbl_Status.STS_Status_PK
GROUP BY
tbl_JobMaterialItems.JHD_JobHeader_PK,
tbl_JobMaterialItems.JMI_ShippingPhase_PK,
tbl_Status.STS_Name
HAVING (tbl_Status.STS_Name = 'Complete')
) b ON ...


Other methods include inserting the proc results into temp tables using
INSERT...EXEC or executing the procs with OPENROWSET and joining the
results. See the Books Online for more information.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rockn" <Rockn@newsgroups.nospam> wrote in message
news:uQE6oYoEIHA.2004@TK2MSFTNGP06.phx.gbl...
>I have to pull some information from my tables and join the two pieces
>together. One table stores all of the estimated labor based on the items in
>that table for each phase of the job. The other table stores the actual
>labor based on the actual work done in the field based on the phase for
>that job. I can create the stored procedures to seperately pull all of the
>data in, but would like one SP or query or an SP nested inside of a query.
>I can get this to work fine with imported SQL data within Access, but I am
>rpetty much a SQL newb. Here are the Stored procedures for both:
>
> ALTER PROCEDURE actualLabor
>
> AS
>
> SELECT tbl_TimeEntry.JHD_JobHeader_PK, SUM(tbl_TimeEntry.TME_Hours) AS
> Actual, tbl_TimeEntry.PHA_Phases_PK
>
> FROM tbl_TimeEntry INNER JOIN
>
> tbl_Phases ON tbl_TimeEntry.PHA_Phases_PK = tbl_Phases.PHA_Phases_PK
>
> GROUP BY tbl_TimeEntry.JHD_JobHeader_PK, tbl_TimeEntry.PHA_Phases_PK
>
> RETURN
>
>
> ALTER PROCEDURE estLabor
>
>
> AS
>
> SELECT tbl_JobMaterialItems.JHD_JobHeader_PK,
> SUM(tbl_JobEstimatedLabor.JEL_StandardHours *
> tbl_JobMaterialItems.JMI_QuantityOrdered) AS EstHours,
>
> tbl_JobMaterialItems.JMI_ShippingPhase_PK, tbl_Status.STS_Name
>
> FROM tbl_JobEstimatedLabor INNER JOIN
>
> tbl_JobMaterialItems ON tbl_JobEstimatedLabor.JMI_JobMaterialItems_PK =
> tbl_JobMaterialItems.JMI_JobMaterialItems_PK INNER JOIN
>
> tbl_JobHeader ON tbl_JobMaterialItems.JHD_JobHeader_PK =
> tbl_JobHeader.JHD_JobHeader_PK INNER JOIN
>
> tbl_Status ON tbl_JobHeader.STS_Status_PK = tbl_Status.STS_Status_PK
>
> GROUP BY tbl_JobMaterialItems.JHD_JobHeader_PK,
> tbl_JobMaterialItems.JMI_ShippingPhase_PK, tbl_Status.STS_Name
>
> HAVING (tbl_Status.STS_Name = 'Complete')
>
>
>
> RETURN
>
> Thanks!!
>