Link to home
Start Free TrialLog in
Avatar of CalebP
CalebPFlag for United States of America

asked on

Microsoft SQL JOIN Issue

I get the following error messages when I try to alter this stored procedure.  The code I changed is in the Q0 select statement I added dbo.[NAV Corporate - Setup$Job Task].Description AS [Task Description] to correspond with the Job Task No_.  I figure there is something wrong with my inner join on the Q0 or something simple. Any help would be appreciated!

Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange2, Line 16
The multi-part identifier "dbo.NAV Corporate - Setup$Job Task.Job Task No_" could not be bound.
Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange2, Line 16
The multi-part identifier "dbo.NAV Corporate - Setup$Job Task.Job No_" could not be bound.
Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange2, Line 16
The multi-part identifier "dbo.NAV Corporate Setup$Job Task.Description" could not be bound.

USE [ACT_NAV]
GO
/****** Object:  StoredProcedure [dbo].[spPMAnalysisByDateRange2]    Script Date: 01/03/2011 10:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPMAnalysisByDateRange2] 
	-- Add the parameters for the stored procedure here
	(
	@BeginDate datetime,
	@EndDate datetime, 
	@PM varchar(50), 
	@Job varchar(100),
	@Company varchar(50)
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
SELECT Q0.[Job No_], Q0.[Old No.], Q0.[Bill Type], Q0.Description, Q0.PM, Q0.Company, Q0.[Job Task No_], Q0.[Task Description], COALESCE(Q1.[Labor and Burden],0) AS [Labor and Burden], COALESCE(Q3.Hours,0) AS Hours, 
COALESCE(Q4.Material,0) AS Material, COALESCE(Q5.Equipment, 0) AS Equipment, COALESCE(Q6.Travel, 0) AS Travel, COALESCE(Q7.Subcontract, 0) AS Subcontract, 
COALESCE(Q8.[Per Diem],0) AS [Per Diem], COALESCE(Q9.[Other Non Labor],0) AS [Other Non Labor], COALESCE (Q10.[Committed Cost], 0) AS [Committed Cost], SUM(COALESCE(Q2.[Total Cost],0)) AS [Total Cost],
COALESCE(Q11.[Labor and Burden Budget], 0) AS [Labor and Burden Budget], COALESCE(Q13.[Hours Budget],0) AS [Hours Budget], COALESCE(Q14.[Material Budget],0) AS [Material Budget], COALESCE(Q12.[Total Budget],0) AS [Total Budget], COALESCE(Q15.[Per Diem Budget],0) AS 
[Per Diem Budget], COALESCE(Q16.[Travel Budget],0) AS [Travel Budget], COALESCE(Q17.[Subcontract Budget],0) AS [SubContract Budget], 
COALESCE(Q18.[Equipment Budget],0) AS [Equipment Budget], COALESCE(Q19.[Other Non Labor Budget],0) AS [Other Non Labor Budget] -- put your other columns from each Q1-Q4 query here in the order you want
FROM (
SELECT DISTINCT [Job No_], [Job Task No_], dbo.[NAV Corporate Setup$Job Task].Description AS [Task Description], dbo.vwJobInfo.Description, dbo.vwJobInfo.Name AS Company, dbo.vwJobInfo.[Old No.], dbo.vwJobInfo.PM, dbo.vwJobInfo.[Bill Type]
FROM dbo.[NAV Corporate - Setup$Job Ledger Entry] INNER JOIN
                      dbo.vwJobInfo ON dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_] = dbo.vwJobInfo.[No_] AND dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_] = dbo.[NAV Corporate - Setup$Job Task].[Job Task No_]
					AND dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_] = dbo.[NAV Corporate - Setup$Job Task].[Job No_]
WHERE	([Posting Date] BETWEEN 
                      @BeginDate AND @EndDate) AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_] LIKE '%999')) AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_] LIKE '%888'))

 ) Q0
LEFT OUTER JOIN
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(Quantity * [Unit Cost (LCY)]) AS [Labor and Burden], [Entry Type], [Cost Category], min([Posting Date]) AS [Begin Date], max([Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE      ([Cost Category] = 0) AND ([Line Type] = 0) AND ([Entry Type] = 0) AND ([Posting Date] BETWEEN 
                      @BeginDate AND @EndDate)
GROUP BY [Entry Type], [Cost Category], [Line Type], [Job No_], [Job Task No_]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q1 ON Q0.[Job No_] = Q1.[Job No_] AND Q0.[Job Task No_] = Q1.[Job Task No_] --End of Q1.  Q1 is Actual Labor + Burden by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Total Cost], min(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date], max(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE   (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] Between 
@BeginDate AND @EndDate)
GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type], 
                      dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q2 ON Q0.[Job No_] = Q2.[Job No_] AND Q0.[Job Task No_] = Q2.[Job Task No_] --end of Q2.  Q2 is Total Cost by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(Quantity)
                      AS Hours, min(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date], max(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] Between 
@BeginDate AND @EndDate)
GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type], 
                      dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q3 ON Q0.[Job No_] = Q3.[Job No_] AND Q0.[Job Task No_] = Q3.[Job Task No_] --end of Q3.  Q3 is Total Hours by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) 
                      AS [Material], min(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date], max(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'MATERIALS - JOB') AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] Between 
@BeginDate AND @EndDate)
GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type], 
                      dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q4 ON Q0.[Job No_] = Q4.[Job No_] AND Q0.[Job Task No_] = Q4.[Job Task No_] --end of Q4.  Q4 is Total MATERIALS - JOB by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) 
                      AS [Equipment], min(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date], max(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'EQUIPMENT - JOB') AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] Between 
@BeginDate AND @EndDate)
GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type], 
                      dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q5 ON Q0.[Job No_] = Q5.[Job No_] AND Q0.[Job Task No_] = Q5.[Job Task No_] --end of Q5.  Q5 is Total EQUIPMENT - JOB by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) 
                      AS [Travel], min(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date], max(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'TRAVEL - JOB') AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] Between 
@BeginDate AND @EndDate)
GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type], 
                      dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q6 ON Q0.[Job No_] = Q6.[Job No_] AND Q0.[Job Task No_] = Q6.[Job Task No_] --end of Q6.  Q6 is Total TRAVEL - JOB by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) 
                      AS [Subcontract], min(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date], max(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'SUBCONTRACT - JOB') AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] Between 
@BeginDate AND @EndDate)
GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type], 
                      dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q7 ON Q0.[Job No_] = Q7.[Job No_] AND Q0.[Job Task No_] = Q7.[Job Task No_] --end of Q7.  Q7 is Total SUBCONTRACT - JOB by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) 
                      AS [Per Diem], min(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date], max(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'PER DIEM - JOB') AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] Between 
@BeginDate AND @EndDate)
GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type], 
                      dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q8 ON Q0.[Job No_] = Q8.[Job No_] AND Q0.[Job Task No_] = Q8.[Job Task No_] --end of Q8.  Q8 is Total PER DIEM - JOB by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) 
                      AS [Other Non Labor], min(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date], max(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
FROM         dbo.[NAV Corporate - Setup$Job Ledger Entry]
WHERE      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0) AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ LIKE '% - JOB')) AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] Between 
@BeginDate AND @EndDate)
GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_], dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type], 
                      dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
) Q9 ON Q0.[Job No_] = Q9.[Job No_] AND Q0.[Job Task No_] = Q9.[Job Task No_] --end of Q9.  Q9 is Total OTHER NON LABOR - JOB by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Task].[Job No_], SUM(dbo.[NAV Corporate - Setup$Purchase Line].[Outstanding Amount]) AS [Committed Cost], 
                      dbo.[NAV Corporate - Setup$Job Task].[Job Task No_]
FROM         dbo.[NAV Corporate - Setup$Job Task] INNER JOIN
                      dbo.[NAV Corporate - Setup$Purchase Line] ON 
                      dbo.[NAV Corporate - Setup$Job Task].[Job Task No_] = dbo.[NAV Corporate - Setup$Purchase Line].[Job Task No_] AND 
                      dbo.[NAV Corporate - Setup$Job Task].[Job No_] = dbo.[NAV Corporate - Setup$Purchase Line].[Job No_]
GROUP BY dbo.[NAV Corporate - Setup$Job Task].[Job No_], dbo.[NAV Corporate - Setup$Job Task].[Job Task Type], 
                      dbo.[NAV Corporate - Setup$Purchase Line].[Document Type], dbo.[NAV Corporate - Setup$Job Task].[Job Task No_]
HAVING      (dbo.[NAV Corporate - Setup$Purchase Line].[Document Type] = 1) AND (dbo.[NAV Corporate - Setup$Job Task].[Job Task Type] = 0)
ORDER BY dbo.[NAV Corporate - Setup$Job Task].[Job No_]
) Q10 ON Q0.[Job No_] = Q10.[Job No_] AND Q0.[Job Task No_] = Q10.[Job Task No_] --end of Q10.  Q10 is Committed Cost by Task Code.
LEFT OUTER JOIN
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(Quantity * [Unit Cost (LCY)]) AS [Labor and Burden Budget], [Cost Category]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
WHERE      ([Cost Category] = 0) AND ([Line Type] = 0)
GROUP BY [Cost Category], [Line Type], [Job No_], [Job Task No_]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
) Q11 ON Q0.[Job No_] = Q11.[Job No_] AND Q0.[Job Task No_] = Q11.[Job Task No_] --End of Q11.  Q11 is Actual Labor + Burden Budget by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Total Budget]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category], dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
) Q12 ON Q0.[Job No_] = Q12.[Job No_] AND Q0.[Job Task No_] = Q12.[Job Task No_] --end of Q12.  Q12 is Total Budget by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(Quantity)
                      AS [Hours Budget]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
WHERE      (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] = 0) AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)                      
GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_], 
                      dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category], dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
) Q13 ON Q0.[Job No_] = Q13.[Job No_] AND Q0.[Job Task No_] = Q13.[Job Task No_] --end of Q13.  Q13 is Total Budgeted Hours by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) 
                      AS [Material Budget]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
WHERE      (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'MATERIALS - JOB')
GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
) Q14 ON Q0.[Job No_] = Q14.[Job No_] AND Q0.[Job Task No_] = Q14.[Job Task No_] --end of Q14.  Q14 is Total Budgeted Materials - Job Cost by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) 
                      AS [Per Diem Budget]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
WHERE      (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'PER DIEM - JOB')
GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_], 
                      dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
) Q15 ON Q0.[Job No_] = Q15.[Job No_] AND Q0.[Job Task No_] = Q15.[Job Task No_] --end of Q15. Q15 is Total Budgeted Per Diem - Job Cost by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) 
                      AS [Travel Budget]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
WHERE      (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'TRAVEL - JOB')
GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_], 
                      dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
) Q16 ON Q0.[Job No_] = Q16.[Job No_] AND Q0.[Job Task No_] = Q16.[Job Task No_] --end of Q16. Q16 is Total Budgeted Travel - Job Cost by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) 
                      AS [Subcontract Budget]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
WHERE      (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'SUBCONTRACT - JOB')
GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
) Q17 ON Q0.[Job No_] = Q17.[Job No_] AND Q0.[Job Task No_] = Q17.[Job Task No_] --end of Q17.  Q17 is Total Budgeted SUBCONTRACT - JOB by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) 
                      AS [Equipment Budget]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
WHERE      (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0) AND 
                      (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'Equipment - JOB')
GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
) Q18 ON Q0.[Job No_] = Q18.[Job No_] AND Q0.[Job Task No_] = Q18.[Job Task No_] --end of Q18.  Q18 is Total Budgeted Equipment - JOB by Task Code.
LEFT OUTER JOIN 
(
SELECT     TOP (100) PERCENT dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], [Job Task No_], SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) 
                      AS [Other Non Labor Budget]
FROM         dbo.[NAV Corporate - Setup$Job Planning Line]
WHERE      (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0) AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0) AND 
                      (NOT (dbo.[NAV Corporate - Setup$Job Planning Line].No_ LIKE '% - JOB'))
GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_], dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_], 
                      dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
) Q19 ON Q0.[Job No_] = Q19.[Job No_] AND Q0.[Job Task No_] = Q19.[Job Task No_] --end of Q19.  Q19 is Total Budgeted OTHER NON LABOR - JOB by Task Code.
WHERE	(Company IN (@Company)) AND (PM IN (@PM)) AND (Q0.[Job No_] IN (@Job))	
GROUP BY Q0.[Job No_], Q0.[Old No.], Q0.[Bill Type], Q0.Description, Q0.PM, Q0.Company, Q0.[Job Task No_], Q0.[Task Description], Q1.[Labor and Burden]
		, Q3.Hours, Q4.Material, Q5.Equipment, Q6.Travel, Q7.Subcontract, Q8.[Per Diem], Q9.[Other Non Labor], Q10.[Committed Cost],
		Q11.[Labor and Burden Budget], Q13.[Hours Budget], Q14.[Material Budget], Q12.[Total Budget], Q15.[Per Diem Budget],
		Q16.[Travel Budget], Q17.[Subcontract Budget], Q18.[Equipment Budget], Q19.[Other Non Labor Budget]
ORDER BY Q0.[Job No_], Q0.[Job Task No_]
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CalebP

ASKER

Hey Dale Burrell,
I pasted that code in place of my Q0 select statement and still get the following message:

Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange2, Line 16
The multi-part identifier "dbo.NAV Corporate - Setup$Job Task.Job Task No_" could not be bound.
Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange2, Line 16
The multi-part identifier "dbo.NAV Corporate - Setup$Job Task.Job No_" could not be bound.
Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange2, Line 16
The multi-part identifier "dbo.NAV Corporate Setup$Job Task.Description" could not be bound.

I am not sure about your question question on where does NAV Corporate - Setup$Job Task] come from.  
Incidentally and unrelated, doing SELECT TOP (100) PERCENT ... ORDER BY is pointless and should be removed/commented out.
Also, the following should be in your WHERE clause and not your HAVING clause:
HAVING (dbo.[NAV Corporate - Setup$Purchase Line].[Document Type] = 1)
             AND (dbo.[NAV Corporate - Setup$Job Task].[Job Task Type] = 0)

In other words, your Stored Procedure should look something like this:
USE [ACT_NAV]
GO
/****** Object:  StoredProcedure [dbo].[spPMAnalysisByDateRange2]    Script Date: 01/03/2011 10:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPMAnalysisByDateRange2] 
	-- Add the parameters for the stored procedure here
    (@BeginDate datetime,
     @EndDate datetime,
     @PM varchar(50),
     @Job varchar(100),
     @Company varchar(50))
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
    SET NOCOUNT ON ;
 
    SELECT  Q0.[Job No_],
            Q0.[Old No.],
            Q0.[Bill Type],
            Q0.Description,
            Q0.PM,
            Q0.Company,
            Q0.[Job Task No_],
            Q0.[Task Description],
            COALESCE(Q1.[Labor and Burden], 0) AS [Labor and Burden],
            COALESCE(Q3.Hours, 0) AS Hours,
            COALESCE(Q4.Material, 0) AS Material,
            COALESCE(Q5.Equipment, 0) AS Equipment,
            COALESCE(Q6.Travel, 0) AS Travel,
            COALESCE(Q7.Subcontract, 0) AS Subcontract,
            COALESCE(Q8.[Per Diem], 0) AS [Per Diem],
            COALESCE(Q9.[Other Non Labor], 0) AS [Other Non Labor],
            COALESCE(Q10.[Committed Cost], 0) AS [Committed Cost],
            SUM(COALESCE(Q2.[Total Cost], 0)) AS [Total Cost],
            COALESCE(Q11.[Labor and Burden Budget], 0) AS [Labor and Burden Budget],
            COALESCE(Q13.[Hours Budget], 0) AS [Hours Budget],
            COALESCE(Q14.[Material Budget], 0) AS [Material Budget],
            COALESCE(Q12.[Total Budget], 0) AS [Total Budget],
            COALESCE(Q15.[Per Diem Budget], 0) AS [Per Diem Budget],
            COALESCE(Q16.[Travel Budget], 0) AS [Travel Budget],
            COALESCE(Q17.[Subcontract Budget], 0) AS [SubContract Budget],
            COALESCE(Q18.[Equipment Budget], 0) AS [Equipment Budget],
            COALESCE(Q19.[Other Non Labor Budget], 0) AS [Other Non Labor Budget] -- put your other columns from each Q1-Q4 query here in the order you want
    FROM    (SELECT DISTINCT
                    [Job No_],
                    [Job Task No_],
                    dbo.[NAV Corporate Setup$Job Task].Description AS [Task Description],
                    dbo.vwJobInfo.Description,
                    dbo.vwJobInfo.Name AS Company,
                    dbo.vwJobInfo.[Old No.],
                    dbo.vwJobInfo.PM,
                    dbo.vwJobInfo.[Bill Type]
             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                    INNER JOIN dbo.vwJobInfo ON dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_] = dbo.vwJobInfo.[No_]
                                                AND dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_] = dbo.[NAV Corporate - Setup$Job Task].[Job Task No_]
                                                AND dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_] = dbo.[NAV Corporate - Setup$Job Task].[Job No_]
             WHERE  ([Posting Date] BETWEEN @BeginDate AND @EndDate)
                    AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_] LIKE '%999'))
                    AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_] LIKE '%888'))
            ) Q0
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(Quantity * [Unit Cost (LCY)]) AS [Labor and Burden],
                                    [Entry Type],
                                    [Cost Category],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  ([Cost Category] = 0)
                                    AND ([Line Type] = 0)
                                    AND ([Entry Type] = 0)
                                    AND ([Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY [Entry Type],
                                    [Cost Category],
                                    [Line Type],
                                    [Job No_],
                                    [Job Task No_]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q1 ON Q0.[Job No_] = Q1.[Job No_]
                                    AND Q0.[Job Task No_] = Q1.[Job Task No_] --End of Q1.  Q1 is Actual Labor + Burden by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Total Cost],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q2 ON Q0.[Job No_] = Q2.[Job No_]
                                    AND Q0.[Job Task No_] = Q2.[Job Task No_] --end of Q2.  Q2 is Total Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(Quantity) AS Hours,
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q3 ON Q0.[Job No_] = Q3.[Job No_]
                                    AND Q0.[Job Task No_] = Q3.[Job Task No_] --end of Q3.  Q3 is Total Hours by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Material],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'MATERIALS - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             --ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q4 ON Q0.[Job No_] = Q4.[Job No_]
                                    AND Q0.[Job Task No_] = Q4.[Job Task No_] --end of Q4.  Q4 is Total MATERIALS - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Equipment],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'EQUIPMENT - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q5 ON Q0.[Job No_] = Q5.[Job No_]
                                    AND Q0.[Job Task No_] = Q5.[Job Task No_] --end of Q5.  Q5 is Total EQUIPMENT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Travel],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'TRAVEL - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q6 ON Q0.[Job No_] = Q6.[Job No_]
                                    AND Q0.[Job Task No_] = Q6.[Job Task No_] --end of Q6.  Q6 is Total TRAVEL - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Subcontract],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'SUBCONTRACT - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q7 ON Q0.[Job No_] = Q7.[Job No_]
                                    AND Q0.[Job Task No_] = Q7.[Job Task No_] --end of Q7.  Q7 is Total SUBCONTRACT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Per Diem],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'PER DIEM - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q8 ON Q0.[Job No_] = Q8.[Job No_]
                                    AND Q0.[Job Task No_] = Q8.[Job Task No_] --end of Q8.  Q8 is Total PER DIEM - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Other Non Labor],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ LIKE '% - JOB'))
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q9 ON Q0.[Job No_] = Q9.[Job No_]
                                    AND Q0.[Job Task No_] = Q9.[Job Task No_] --end of Q9.  Q9 is Total OTHER NON LABOR - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Task].[Job No_],
                                    SUM(dbo.[NAV Corporate - Setup$Purchase Line].[Outstanding Amount]) AS [Committed Cost],
                                    dbo.[NAV Corporate - Setup$Job Task].[Job Task No_]
                             FROM   dbo.[NAV Corporate - Setup$Job Task]
                                    INNER JOIN dbo.[NAV Corporate - Setup$Purchase Line] ON dbo.[NAV Corporate - Setup$Job Task].[Job Task No_] = dbo.[NAV Corporate - Setup$Purchase Line].[Job Task No_]
                                                                                            AND dbo.[NAV Corporate - Setup$Job Task].[Job No_] = dbo.[NAV Corporate - Setup$Purchase Line].[Job No_]
                             WHERE	(dbo.[NAV Corporate - Setup$Purchase Line].[Document Type] = 1)
                                    AND (dbo.[NAV Corporate - Setup$Job Task].[Job Task Type] = 0)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Task].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Task].[Job Task Type],
                                    dbo.[NAV Corporate - Setup$Purchase Line].[Document Type],
                                    dbo.[NAV Corporate - Setup$Job Task].[Job Task No_]
                             -- HAVING (dbo.[NAV Corporate - Setup$Purchase Line].[Document Type] = 1)
                             --       AND (dbo.[NAV Corporate - Setup$Job Task].[Job Task Type] = 0)
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Task].[Job No_]
                            ) Q10 ON Q0.[Job No_] = Q10.[Job No_]
                                     AND Q0.[Job Task No_] = Q10.[Job Task No_] --end of Q10.  Q10 is Committed Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(Quantity * [Unit Cost (LCY)]) AS [Labor and Burden Budget],
                                    [Cost Category]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  ([Cost Category] = 0)
                                    AND ([Line Type] = 0)
                             GROUP BY [Cost Category],
                                    [Line Type],
                                    [Job No_],
                                    [Job Task No_]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q11 ON Q0.[Job No_] = Q11.[Job No_]
                                     AND Q0.[Job Task No_] = Q11.[Job Task No_] --End of Q11.  Q11 is Actual Labor + Burden Budget by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Total Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q12 ON Q0.[Job No_] = Q12.[Job No_]
                                     AND Q0.[Job Task No_] = Q12.[Job Task No_] --end of Q12.  Q12 is Total Budget by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(Quantity) AS [Hours Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q13 ON Q0.[Job No_] = Q13.[Job No_]
                                     AND Q0.[Job Task No_] = Q13.[Job Task No_] --end of Q13.  Q13 is Total Budgeted Hours by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Material Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'MATERIALS - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q14 ON Q0.[Job No_] = Q14.[Job No_]
                                     AND Q0.[Job Task No_] = Q14.[Job Task No_] --end of Q14.  Q14 is Total Budgeted Materials - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Per Diem Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'PER DIEM - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q15 ON Q0.[Job No_] = Q15.[Job No_]
                                     AND Q0.[Job Task No_] = Q15.[Job Task No_] --end of Q15. Q15 is Total Budgeted Per Diem - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT --TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Travel Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'TRAVEL - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q16 ON Q0.[Job No_] = Q16.[Job No_]
                                     AND Q0.[Job Task No_] = Q16.[Job Task No_] --end of Q16. Q16 is Total Budgeted Travel - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Subcontract Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'SUBCONTRACT - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q17 ON Q0.[Job No_] = Q17.[Job No_]
                                     AND Q0.[Job Task No_] = Q17.[Job Task No_] --end of Q17.  Q17 is Total Budgeted SUBCONTRACT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Equipment Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'Equipment - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q18 ON Q0.[Job No_] = Q18.[Job No_]
                                     AND Q0.[Job Task No_] = Q18.[Job Task No_] --end of Q18.  Q18 is Total Budgeted Equipment - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Other Non Labor Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)
                                    AND (NOT (dbo.[NAV Corporate - Setup$Job Planning Line].No_ LIKE '% - JOB')
                                        )
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q19 ON Q0.[Job No_] = Q19.[Job No_]
                                     AND Q0.[Job Task No_] = Q19.[Job Task No_] --end of Q19.  Q19 is Total Budgeted OTHER NON LABOR - JOB by Task Code.
    WHERE   (Company IN (@Company))
            AND (PM IN (@PM))
            AND (Q0.[Job No_] IN (@Job))
    GROUP BY Q0.[Job No_],
            Q0.[Old No.],
            Q0.[Bill Type],
            Q0.Description,
            Q0.PM,
            Q0.Company,
            Q0.[Job Task No_],
            Q0.[Task Description],
            Q1.[Labor and Burden],
            Q3.Hours,
            Q4.Material,
            Q5.Equipment,
            Q6.Travel,
            Q7.Subcontract,
            Q8.[Per Diem],
            Q9.[Other Non Labor],
            Q10.[Committed Cost],
            Q11.[Labor and Burden Budget],
            Q13.[Hours Budget],
            Q14.[Material Budget],
            Q12.[Total Budget],
            Q15.[Per Diem Budget],
            Q16.[Travel Budget],
            Q17.[Subcontract Budget],
            Q18.[Equipment Budget],
            Q19.[Other Non Labor Budget]
    ORDER BY Q0.[Job No_],
            Q0.[Job Task No_]
END

Open in new window

>>I am not sure about your question question on where does NAV Corporate - Setup$Job Task] <<
It is in your code.  It needs to be referenced in your FROM clause.
Avatar of CalebP

ASKER

I ran the stored procedure code and received the same error message.  That does look a lot nicer!  Definitely helped me see a better way to code this.  Thanks!!!!

Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange, Line 14
The multi-part identifier "dbo.NAV Corporate - Setup$Job Task.Job Task No_" could not be bound.
Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange, Line 14
The multi-part identifier "dbo.NAV Corporate - Setup$Job Task.Job No_" could not be bound.
Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange, Line 14
The multi-part identifier "dbo.NAV Corporate Setup$Job Task.Description" could not be bound.
acperkins has already said it all, but to clarify I didn't fix you problem as I don't know how you want to include the missing table in your query. I was showing you how to use a table alias.

In your query you twice reference table dbo.[NAV Corporate - Setup$Job Task] i.e.

"AND dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_] = dbo.[NAV Corporate - Setup$Job Task].[Job No_]"

but you haven't added dbo.[NAV Corporate - Setup$Job Task] to the query at any stage - it needs to either have

from dbo.[NAV Corporate - Setup$Job Task]

or

{some} join dbo.[NAV Corporate - Setup$Job Task] to allow you to reference this table.
Avatar of CalebP

ASKER

You're both right.  I need to include an INNER Join on the Job Task Table with the Job Ledger entry table so that it pulls the Description AS [Task Description].
Avatar of CalebP

ASKER

Thank you both so much.  I added an additional INNER JOIN to get it to know what the Job Task table was.

FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry] JLE
                    INNER JOIN dbo.vwJobInfo ON JLE.[Job No_] = dbo.vwJobInfo.[No_]
                              INNER JOIN dbo.[NAV Corporate - Setup$Job Task].[Job Task No_] ON dbo.[NAV Corporate - Setup$Job Task].[Job Task No_] = JLE.[Job Task No_]

Now when I run the Stored Procedure I get this message:

Invalid object name 'dbo.NAV Corporate - Setup$Job Task.Job Task No_'.
Avatar of CalebP

ASKER

Here is the latest code.  Thank you both for your assistance!  
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Just to emphasize what Dale stated, this query is in urgent need of aliases and you also do not need to preface the column if there is only one table involved.
Avatar of CalebP

ASKER

I agree with the aliases part.  I have hated working with this database because of the Name they gave it.  They never went back in and changed it.  I am very new to SQL since our last developer quit and am learning as I go.  I really appreciate learninga bout aliases from you guys.  
Would mind ellaborating on preface the column if there is only one table involved?
Avatar of CalebP

ASKER

I feel like it is so close to being there.  Attached is the latest code and this is the error message:

Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange, Line 14
The multi-part identifier "dbo.NAV Corporate - Setup$Job Ledger Entry.Job Task No_" could not be bound.
Msg 4104, Level 16, State 1, Procedure spPMAnalysisByDateRange, Line 14
The multi-part identifier "dbo.NAV Corporate - Setup$Job Ledger Entry.Job Task No_" could not be bound.
USE [ACT_NAV]
GO
/****** Object:  StoredProcedure [dbo].[spPMAnalysisByDateRange]    Script Date: 01/03/2011 10:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPMAnalysisByDateRange] 
	-- Add the parameters for the stored procedure here
    (@BeginDate datetime,
     @EndDate datetime,
     @PM varchar(50),
     @Job varchar(100),
     @Company varchar(50))
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
    SET NOCOUNT ON ;
 
    SELECT  Q0.[Job No_],
            Q0.[Old No.],
            Q0.[Bill Type],
            Q0.Description,
            Q0.PM,
            Q0.Company,
            Q0.[Job Task No_],
            Q0.[Task Description],
            COALESCE(Q1.[Labor and Burden], 0) AS [Labor and Burden],
			SUM(COALESCE(Q2.[Total Cost], 0)) AS [Total Cost],
            COALESCE(Q3.Hours, 0) AS Hours,
            COALESCE(Q4.Material, 0) AS Material,
            COALESCE(Q5.Equipment, 0) AS Equipment,
            COALESCE(Q6.Travel, 0) AS Travel,
            COALESCE(Q7.Subcontract, 0) AS Subcontract,
            COALESCE(Q8.[Per Diem], 0) AS [Per Diem],
            COALESCE(Q9.[Other Non Labor], 0) AS [Other Non Labor],
            COALESCE(Q10.[Committed Cost], 0) AS [Committed Cost],
            COALESCE(Q11.[Labor and Burden Budget], 0) AS [Labor and Burden Budget],
			COALESCE(Q12.[Total Budget], 0) AS [Total Budget],
            COALESCE(Q13.[Hours Budget], 0) AS [Hours Budget],
            COALESCE(Q14.[Material Budget], 0) AS [Material Budget],
            COALESCE(Q15.[Per Diem Budget], 0) AS [Per Diem Budget],
            COALESCE(Q16.[Travel Budget], 0) AS [Travel Budget],
            COALESCE(Q17.[Subcontract Budget], 0) AS [SubContract Budget],
            COALESCE(Q18.[Equipment Budget], 0) AS [Equipment Budget],
            COALESCE(Q19.[Other Non Labor Budget], 0) AS [Other Non Labor Budget] -- put your other columns from each Q#-Q# query here in the order you want
    FROM    (SELECT DISTINCT
                    JLE.[Job No_],
                    JLE.[Job Task No_],
                    JT.Description AS [Task Description],
                    dbo.vwJobInfo.Description,
                    dbo.vwJobInfo.Name AS Company,
                    dbo.vwJobInfo.[Old No.],
                    dbo.vwJobInfo.PM,
                    dbo.vwJobInfo.[Bill Type]
             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry] JLE
                    INNER JOIN dbo.vwJobInfo ON JLE.[Job No_] = dbo.vwJobInfo.[No_]
					INNER JOIN dbo.[NAV Corporate - Setup$Job Task] JT ON JT.[Job Task No_] = JLE.[Job Task No_]
             WHERE  ([Posting Date] BETWEEN @BeginDate AND @EndDate)
                    AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_] LIKE '%999'))
                    AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_] LIKE '%888'))
            ) Q0
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(Quantity * [Unit Cost (LCY)]) AS [Labor and Burden],
                                    [Entry Type],
                                    [Cost Category],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  ([Cost Category] = 0)
                                    AND ([Line Type] = 0)
                                    AND ([Entry Type] = 0)
                                    AND ([Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY [Entry Type],
                                    [Cost Category],
                                    [Line Type],
                                    [Job No_],
                                    [Job Task No_]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q1 ON Q0.[Job No_] = Q1.[Job No_]
                                    AND Q0.[Job Task No_] = Q1.[Job Task No_] --End of Q1.  Q1 is Actual Labor + Burden by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Total Cost],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q2 ON Q0.[Job No_] = Q2.[Job No_]
                                    AND Q0.[Job Task No_] = Q2.[Job Task No_] --end of Q2.  Q2 is Total Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(Quantity) AS Hours,
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q3 ON Q0.[Job No_] = Q3.[Job No_]
                                    AND Q0.[Job Task No_] = Q3.[Job Task No_] --end of Q3.  Q3 is Total Hours by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Material],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'MATERIALS - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             --ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q4 ON Q0.[Job No_] = Q4.[Job No_]
                                    AND Q0.[Job Task No_] = Q4.[Job Task No_] --end of Q4.  Q4 is Total MATERIALS - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Equipment],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'EQUIPMENT - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q5 ON Q0.[Job No_] = Q5.[Job No_]
                                    AND Q0.[Job Task No_] = Q5.[Job Task No_] --end of Q5.  Q5 is Total EQUIPMENT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Travel],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'TRAVEL - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q6 ON Q0.[Job No_] = Q6.[Job No_]
                                    AND Q0.[Job Task No_] = Q6.[Job Task No_] --end of Q6.  Q6 is Total TRAVEL - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Subcontract],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'SUBCONTRACT - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q7 ON Q0.[Job No_] = Q7.[Job No_]
                                    AND Q0.[Job Task No_] = Q7.[Job Task No_] --end of Q7.  Q7 is Total SUBCONTRACT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Per Diem],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ = 'PER DIEM - JOB')
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q8 ON Q0.[Job No_] = Q8.[Job No_]
                                    AND Q0.[Job Task No_] = Q8.[Job Task No_] --end of Q8.  Q8 is Total PER DIEM - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Total Cost (LCY)]) AS [Other Non Labor],
                                    MIN(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [Begin Date],
                                    MAX(dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type] = 0)
                                    AND (NOT (dbo.[NAV Corporate - Setup$Job Ledger Entry].No_ LIKE '% - JOB'))
                                    AND (dbo.[NAV Corporate - Setup$Job Ledger Entry].[Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Entry Type],
                                    dbo.[NAV Corporate - Setup$Job Ledger Entry].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q9 ON Q0.[Job No_] = Q9.[Job No_]
                                    AND Q0.[Job Task No_] = Q9.[Job Task No_] --end of Q9.  Q9 is Total OTHER NON LABOR - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Task].[Job No_],
                                    SUM(dbo.[NAV Corporate - Setup$Purchase Line].[Outstanding Amount]) AS [Committed Cost],
                                    dbo.[NAV Corporate - Setup$Job Task].[Job Task No_]
                             FROM   dbo.[NAV Corporate - Setup$Job Task]
                                    INNER JOIN dbo.[NAV Corporate - Setup$Purchase Line] ON dbo.[NAV Corporate - Setup$Job Task].[Job Task No_] = dbo.[NAV Corporate - Setup$Purchase Line].[Job Task No_]
                                                                                            AND dbo.[NAV Corporate - Setup$Job Task].[Job No_] = dbo.[NAV Corporate - Setup$Purchase Line].[Job No_]
                             WHERE	(dbo.[NAV Corporate - Setup$Purchase Line].[Document Type] = 1)
                                    AND (dbo.[NAV Corporate - Setup$Job Task].[Job Task Type] = 0)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Task].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Task].[Job Task Type],
                                    dbo.[NAV Corporate - Setup$Purchase Line].[Document Type],
                                    dbo.[NAV Corporate - Setup$Job Task].[Job Task No_]
                             -- HAVING (dbo.[NAV Corporate - Setup$Purchase Line].[Document Type] = 1)
                             --       AND (dbo.[NAV Corporate - Setup$Job Task].[Job Task Type] = 0)
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Task].[Job No_]
                            ) Q10 ON Q0.[Job No_] = Q10.[Job No_]
                                     AND Q0.[Job Task No_] = Q10.[Job Task No_] --end of Q10.  Q10 is Committed Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(Quantity * [Unit Cost (LCY)]) AS [Labor and Burden Budget],
                                    [Cost Category]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  ([Cost Category] = 0)
                                    AND ([Line Type] = 0)
                             GROUP BY [Cost Category],
                                    [Line Type],
                                    [Job No_],
                                    [Job Task No_]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q11 ON Q0.[Job No_] = Q11.[Job No_]
                                     AND Q0.[Job Task No_] = Q11.[Job Task No_] --End of Q11.  Q11 is Actual Labor + Burden Budget by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Total Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q12 ON Q0.[Job No_] = Q12.[Job No_]
                                     AND Q0.[Job Task No_] = Q12.[Job Task No_] --end of Q12.  Q12 is Total Budget by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(Quantity) AS [Hours Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q13 ON Q0.[Job No_] = Q13.[Job No_]
                                     AND Q0.[Job Task No_] = Q13.[Job Task No_] --end of Q13.  Q13 is Total Budgeted Hours by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Material Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'MATERIALS - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q14 ON Q0.[Job No_] = Q14.[Job No_]
                                     AND Q0.[Job Task No_] = Q14.[Job Task No_] --end of Q14.  Q14 is Total Budgeted Materials - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Per Diem Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'PER DIEM - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q15 ON Q0.[Job No_] = Q15.[Job No_]
                                     AND Q0.[Job Task No_] = Q15.[Job Task No_] --end of Q15. Q15 is Total Budgeted Per Diem - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT --TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Travel Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'TRAVEL - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q16 ON Q0.[Job No_] = Q16.[Job No_]
                                     AND Q0.[Job Task No_] = Q16.[Job Task No_] --end of Q16. Q16 is Total Budgeted Travel - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Subcontract Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'SUBCONTRACT - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q17 ON Q0.[Job No_] = Q17.[Job No_]
                                     AND Q0.[Job Task No_] = Q17.[Job Task No_] --end of Q17.  Q17 is Total Budgeted SUBCONTRACT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Equipment Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].No_ = 'Equipment - JOB')
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q18 ON Q0.[Job No_] = Q18.[Job No_]
                                     AND Q0.[Job Task No_] = Q18.[Job Task No_] --end of Q18.  Q18 is Total Budgeted Equipment - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    [Job Task No_],
                                    SUM(dbo.[NAV Corporate - Setup$Job Planning Line].[Total Cost (LCY)]) AS [Other Non Labor Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  (dbo.[NAV Corporate - Setup$Job Planning Line].[Cost Category] <> 0)
                                    AND (dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type] = 0)
                                    AND (NOT (dbo.[NAV Corporate - Setup$Job Planning Line].No_ LIKE '% - JOB')
                                        )
                             GROUP BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_],
                                    dbo.[NAV Corporate - Setup$Job Planning Line].[Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q19 ON Q0.[Job No_] = Q19.[Job No_]
                                     AND Q0.[Job Task No_] = Q19.[Job Task No_] --end of Q19.  Q19 is Total Budgeted OTHER NON LABOR - JOB by Task Code.
    WHERE   (Company IN (@Company))
            AND (PM IN (@PM))
            AND (Q0.[Job No_] IN (@Job))
    GROUP BY Q0.[Job No_],
            Q0.[Old No.],
            Q0.[Bill Type],
            Q0.Description,
            Q0.PM,
            Q0.Company,
            Q0.[Job Task No_],
            Q0.[Task Description],
            Q1.[Labor and Burden],
            Q3.Hours,
            Q4.Material,
            Q5.Equipment,
            Q6.Travel,
            Q7.Subcontract,
            Q8.[Per Diem],
            Q9.[Other Non Labor],
            Q10.[Committed Cost],
            Q11.[Labor and Burden Budget],
            Q13.[Hours Budget],
            Q14.[Material Budget],
            Q12.[Total Budget],
            Q15.[Per Diem Budget],
            Q16.[Travel Budget],
            Q17.[Subcontract Budget],
            Q18.[Equipment Budget],
            Q19.[Other Non Labor Budget]
    ORDER BY Q0.[Job No_],
            Q0.[Job Task No_]
END

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Many of your subqueries only contain one table so there is no need to prefix the columns.  Take a look at this:
USE [ACT_NAV]
GO
/****** Object:  StoredProcedure [dbo].[spPMAnalysisByDateRange2]    Script Date: 01/03/2011 10:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPMAnalysisByDateRange2] 
	-- Add the parameters for the stored procedure here
    (@BeginDate datetime,
     @EndDate datetime,
     @PM varchar(50),
     @Job varchar(100),
     @Company varchar(50))
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
    SET NOCOUNT ON ;
 
    SELECT  Q0.[Job No_],
            Q0.[Old No.],
            Q0.[Bill Type],
            Q0.Description,
            Q0.PM,
            Q0.Company,
            Q0.[Job Task No_],
            Q0.[Task Description],
            COALESCE(Q1.[Labor and Burden], 0) AS [Labor and Burden],
            COALESCE(Q3.Hours, 0) AS Hours,
            COALESCE(Q4.Material, 0) AS Material,
            COALESCE(Q5.Equipment, 0) AS Equipment,
            COALESCE(Q6.Travel, 0) AS Travel,
            COALESCE(Q7.Subcontract, 0) AS Subcontract,
            COALESCE(Q8.[Per Diem], 0) AS [Per Diem],
            COALESCE(Q9.[Other Non Labor], 0) AS [Other Non Labor],
            COALESCE(Q10.[Committed Cost], 0) AS [Committed Cost],
            SUM(COALESCE(Q2.[Total Cost], 0)) AS [Total Cost],
            COALESCE(Q11.[Labor and Burden Budget], 0) AS [Labor and Burden Budget],
            COALESCE(Q13.[Hours Budget], 0) AS [Hours Budget],
            COALESCE(Q14.[Material Budget], 0) AS [Material Budget],
            COALESCE(Q12.[Total Budget], 0) AS [Total Budget],
            COALESCE(Q15.[Per Diem Budget], 0) AS [Per Diem Budget],
            COALESCE(Q16.[Travel Budget], 0) AS [Travel Budget],
            COALESCE(Q17.[Subcontract Budget], 0) AS [SubContract Budget],
            COALESCE(Q18.[Equipment Budget], 0) AS [Equipment Budget],
            COALESCE(Q19.[Other Non Labor Budget], 0) AS [Other Non Labor Budget] -- put your other columns from each Q1-Q4 query here in the order you want
    FROM    (SELECT DISTINCT
                    [Job No_],
                    [Job Task No_],
                    dbo.[NAV Corporate Setup$Job Task].Description AS [Task Description],
                    dbo.vwJobInfo.Description,
                    dbo.vwJobInfo.Name AS Company,
                    dbo.vwJobInfo.[Old No.],
                    dbo.vwJobInfo.PM,
                    dbo.vwJobInfo.[Bill Type]
             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry] jle
					INNER JOIN dbo.[NAV Corporate - Setup$Job Task] jt ON JT.[Job Task No_] = jle.[Job Task No_] 
                    INNER JOIN dbo.vwJobInfo vj ON jle.[Job No_] = vj.[No_]
                                                AND jle.[Job Task No_] = jt.[Job Task No_]
                                                AND jle.[Job No_] = jt.[Job No_]
             WHERE  ([Posting Date] BETWEEN @BeginDate AND @EndDate)
                    AND (NOT (jle.[Job Task No_] LIKE '%999'))
                    AND (NOT (jle.[Job Task No_] LIKE '%888'))
            ) Q0
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM(Quantity * [Unit Cost (LCY)]) AS [Labor and Burden],
                                    [Entry Type],
                                    [Cost Category],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  ([Cost Category] = 0)
                                    AND ([Line Type] = 0)
                                    AND ([Entry Type] = 0)
                                    AND ([Posting Date] BETWEEN @BeginDate AND @EndDate)
                             GROUP BY [Entry Type],
                                    [Cost Category],
                                    [Line Type],
                                    [Job No_],
                                    [Job Task No_]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q1 ON Q0.[Job No_] = Q1.[Job No_]
                                    AND Q0.[Job Task No_] = Q1.[Job Task No_] --End of Q1.  Q1 is Actual Labor + Burden by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Total Cost],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  [Line Type] = 0
                                    AND [Entry Type] = 0
                                    AND [Posting Date] BETWEEN @BeginDate AND @EndDate
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Entry Type],
                                    [Cost Category],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q2 ON Q0.[Job No_] = Q2.[Job No_]
                                    AND Q0.[Job Task No_] = Q2.[Job Task No_] --end of Q2.  Q2 is Total Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM(Quantity) AS Hours,
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  [Cost Category] = 0
                                    AND [Line Type] = 0
                                    AND [Entry Type] = 0
                                    AND [Posting Date] BETWEEN @BeginDate AND @EndDate
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Entry Type],
                                    [Cost Category],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q3 ON Q0.[Job No_] = Q3.[Job No_]
                                    AND Q0.[Job Task No_] = Q3.[Job Task No_] --end of Q3.  Q3 is Total Hours by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Material],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND [Entry Type] = 0
                                    AND No_ = 'MATERIALS - JOB'
                                    AND [Posting Date] BETWEEN @BeginDate AND @EndDate
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Entry Type],
                                    [Line Type]
                             --ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q4 ON Q0.[Job No_] = Q4.[Job No_]
                                    AND Q0.[Job Task No_] = Q4.[Job Task No_] --end of Q4.  Q4 is Total MATERIALS - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Equipment],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND [Entry Type] = 0
                                    AND No_ = 'EQUIPMENT - JOB'
                                    AND [Posting Date] BETWEEN @BeginDate AND @EndDate
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Entry Type],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q5 ON Q0.[Job No_] = Q5.[Job No_]
                                    AND Q0.[Job Task No_] = Q5.[Job Task No_] --end of Q5.  Q5 is Total EQUIPMENT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Travel],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND [Entry Type] = 0
                                    AND No_ = 'TRAVEL - JOB'
                                    AND [Posting Date] BETWEEN @BeginDate AND @EndDate
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Entry Type],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q6 ON Q0.[Job No_] = Q6.[Job No_]
                                    AND Q0.[Job Task No_] = Q6.[Job Task No_] --end of Q6.  Q6 is Total TRAVEL - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Subcontract],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND [Entry Type] = 0
                                    AND No_ = 'SUBCONTRACT - JOB'
                                    AND [Posting Date] BETWEEN @BeginDate AND @EndDate
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Entry Type],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q7 ON Q0.[Job No_] = Q7.[Job No_]
                                    AND Q0.[Job Task No_] = Q7.[Job Task No_] --end of Q7.  Q7 is Total SUBCONTRACT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Per Diem],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND [Entry Type] = 0
                                    AND No_ = 'PER DIEM - JOB'
                                    AND [Posting Date] BETWEEN @BeginDate AND @EndDate
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Entry Type],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q8 ON Q0.[Job No_] = Q8.[Job No_]
                                    AND Q0.[Job Task No_] = Q8.[Job Task No_] --end of Q8.  Q8 is Total PER DIEM - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Other Non Labor],
                                    MIN([Posting Date]) AS [Begin Date],
                                    MAX([Posting Date]) AS [End Date]
                             FROM   dbo.[NAV Corporate - Setup$Job Ledger Entry]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND [Entry Type] = 0
                                    AND NOT No_ LIKE '% - JOB'
                                    AND [Posting Date] BETWEEN @BeginDate AND @EndDate
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Entry Type],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Ledger Entry].[Job No_]
                            ) Q9 ON Q0.[Job No_] = Q9.[Job No_]
                                    AND Q0.[Job Task No_] = Q9.[Job Task No_] --end of Q9.  Q9 is Total OTHER NON LABOR - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    jt.[Job No_],
                                    SUM(pl.[Outstanding Amount]) AS [Committed Cost],
                                    jt.[Job Task No_]
                             FROM   dbo.[NAV Corporate - Setup$Job Task] jt
                                    INNER JOIN dbo.[NAV Corporate - Setup$Purchase Line] ON jt.[Job Task No_] = pl.[Job Task No_]
                                                                                            AND jt.[Job No_] = pl.[Job No_]
                             WHERE	(pl.[Document Type] = 1)
                                    AND (jt.[Job Task Type] = 0)
                             GROUP BY jt.[Job No_],
                                    jt.[Job Task Type],
                                    pl.[Document Type],
                                    jt.[Job Task No_]
                             -- HAVING (dbo.[NAV Corporate - Setup$Purchase Line].[Document Type] = 1)
                             --       AND (dbo.[NAV Corporate - Setup$Job Task].[Job Task Type] = 0)
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Task].[Job No_]
                            ) Q10 ON Q0.[Job No_] = Q10.[Job No_]
                                     AND Q0.[Job Task No_] = Q10.[Job Task No_] --end of Q10.  Q10 is Committed Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM(Quantity * [Unit Cost (LCY)]) AS [Labor and Burden Budget],
                                    [Cost Category]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  [Cost Category] = 0
                                    AND [Line Type] = 0
                             GROUP BY [Cost Category],
                                    [Line Type],
                                    [Job No_],
                                    [Job Task No_]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q11 ON Q0.[Job No_] = Q11.[Job No_]
                                     AND Q0.[Job Task No_] = Q11.[Job Task No_] --End of Q11.  Q11 is Actual Labor + Burden Budget by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Total Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Cost Category],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q12 ON Q0.[Job No_] = Q12.[Job No_]
                                     AND Q0.[Job Task No_] = Q12.[Job Task No_] --end of Q12.  Q12 is Total Budget by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM(Quantity) AS [Hours Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  [Cost Category] = 0
                                    AND [Line Type] = 0
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Cost Category],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q13 ON Q0.[Job No_] = Q13.[Job No_]
                                     AND Q0.[Job Task No_] = Q13.[Job Task No_] --end of Q13.  Q13 is Total Budgeted Hours by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Material Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  [Cost Category] <> 0
                                    AND No_ = 'MATERIALS - JOB'
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q14 ON Q0.[Job No_] = Q14.[Job No_]
                                     AND Q0.[Job Task No_] = Q14.[Job Task No_] --end of Q14.  Q14 is Total Budgeted Materials - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Per Diem Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  [Cost Category] <> 0
                                    AND No_ = 'PER DIEM - JOB'
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q15 ON Q0.[Job No_] = Q15.[Job No_]
                                     AND Q0.[Job Task No_] = Q15.[Job Task No_] --end of Q15. Q15 is Total Budgeted Per Diem - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT --TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Travel Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  [Cost Category] <> 0
                                    AND No_ = 'TRAVEL - JOB'
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_],
                             --       dbo.[NAV Corporate - Setup$Job Planning Line].[Job Task No_]
                            ) Q16 ON Q0.[Job No_] = Q16.[Job No_]
                                     AND Q0.[Job Task No_] = Q16.[Job Task No_] --end of Q16. Q16 is Total Budgeted Travel - Job Cost by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Subcontract Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND No_ = 'SUBCONTRACT - JOB'
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q17 ON Q0.[Job No_] = Q17.[Job No_]
                                     AND Q0.[Job Task No_] = Q17.[Job Task No_] --end of Q17.  Q17 is Total Budgeted SUBCONTRACT - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Equipment Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND No_ = 'Equipment - JOB'
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q18 ON Q0.[Job No_] = Q18.[Job No_]
                                     AND Q0.[Job Task No_] = Q18.[Job Task No_] --end of Q18.  Q18 is Total Budgeted Equipment - JOB by Task Code.
            LEFT OUTER JOIN (SELECT -- TOP (100) PERCENT
                                    [Job No_],
                                    [Job Task No_],
                                    SUM([Total Cost (LCY)]) AS [Other Non Labor Budget]
                             FROM   dbo.[NAV Corporate - Setup$Job Planning Line]
                             WHERE  [Cost Category] <> 0
                                    AND [Line Type] = 0
                                    AND NOT No_ LIKE '% - JOB'
                             GROUP BY [Job No_],
                                    [Job Task No_],
                                    [Line Type]
                             -- ORDER BY dbo.[NAV Corporate - Setup$Job Planning Line].[Job No_]
                            ) Q19 ON Q0.[Job No_] = Q19.[Job No_]
                                     AND Q0.[Job Task No_] = Q19.[Job Task No_] --end of Q19.  Q19 is Total Budgeted OTHER NON LABOR - JOB by Task Code.
    WHERE   (Company IN (@Company))
            AND (PM IN (@PM))
            AND (Q0.[Job No_] IN (@Job))
    GROUP BY Q0.[Job No_],
            Q0.[Old No.],
            Q0.[Bill Type],
            Q0.Description,
            Q0.PM,
            Q0.Company,
            Q0.[Job Task No_],
            Q0.[Task Description],
            Q1.[Labor and Burden],
            Q3.Hours,
            Q4.Material,
            Q5.Equipment,
            Q6.Travel,
            Q7.Subcontract,
            Q8.[Per Diem],
            Q9.[Other Non Labor],
            Q10.[Committed Cost],
            Q11.[Labor and Burden Budget],
            Q13.[Hours Budget],
            Q14.[Material Budget],
            Q12.[Total Budget],
            Q15.[Per Diem Budget],
            Q16.[Travel Budget],
            Q17.[Subcontract Budget],
            Q18.[Equipment Budget],
            Q19.[Other Non Labor Budget]
    ORDER BY Q0.[Job No_],
            Q0.[Job Task No_]
END

Open in new window

Sorry, that is in reference to your earlier question, but it should also apply.
Avatar of CalebP

ASKER

I see what you're saying on prefixing the columns now.  Thanks so much to both of you!!!
Avatar of CalebP

ASKER

acperkins and Dale Burrell both helped me tremendously with this problem!  I learned some good tricks of the trade as well.