Solved

Need help modifying a complex query

Posted on 2014-02-03
4
236 Views
Last Modified: 2014-02-04
I have a report that displays budget and spending information for a given project.  I have a dataset dsBudgetvsActual that pulls from the Budget table (CltBudget) and the table that labour and expenses are stored in (WIP) and groups the data in the following result:

Example resultsmore Examples
Now I need to break that down by employee for the Bill categories so I would get something that looks like this:

Example with budget amounts
I built this code off an example I found so I'm not good at explaining it but I'm grabbing all the stuff I need from the Cltbudgets table and attaching it to ClientBudgets and grabbing the stuff I need from the WIP table (where actual expenses and hours are saved) and attaching it to an ActualSpent table then grabbing my fields/columns from each of these two tables.

SELECT     ClientBudgets.CBudCltID AS Client_ID, 
           ClientBudgets.CBudCltName AS ClientName, 
           ClientBudgets.CBudCltNum AS ClientNumber, 
           ClientBudgets.CBudEng AS EngagementCode, 
           ClientBudgets.CBudCodeCat AS ServiceCategory, 
           ClientBudgets.CBudCodeSub AS ServiceSubcategory, 
           ISNULL(ClientBudgets.Hours, 0) AS BudgetedHours, 
           ISNULL(ActualSpent.WipHours, 0) AS ActualHours, 
           ISNULL(ClientBudgets.Hours, 0) - ISNULL(ActualSpent.WipHours, 0) AS VarHours, 
           ISNULL(ClientBudgets.Cost, 0) AS BudgetedCost, 
           ISNULL(ActualSpent.WipCost, 0) AS ActualCost, 
           ISNULL(ClientBudgets.Cost, 0) - ISNULL(ActualSpent.WipCost, 0) AS VarCost, 
           ISNULL(ClientBudgets.Expenses, 0) AS BudgetedExpenses, 
           ISNULL(ActualSpent.WipExpenses, 0) AS ActualExpenses, 
           ISNULL(ClientBudgets.Expenses, 0) - ISNULL(ActualSpent.WipExpenses, 0) AS VarExpenses
FROM         (SELECT     CBudCltID, 
                         CBudCltName, 
			 CBudCltNum, 
			 CBudEng, 
			 CBudCodeCat, 
			 CBudCodeSub, 
			 ISNULL(SUM(CBudhours), 0) AS Hours, 
			 ISNULL(SUM(CBudfee), 0) AS Cost,
                         ISNULL(SUM(CBudexp), 0) AS Expenses
              FROM          CltBudget
              GROUP BY CBudCodeCat, CBudCodeSub, CBudCltID, CBudCltName, CBudCltNum, CBudEng) 
AS ClientBudgets LEFT OUTER JOIN
              (SELECT     WCltID, 
              		  WCltName, 
              		  WCltNum, 
              		  WEng, 
              		  WCodeCat, 
              		  WCodeSub, 
              		  ISNULL(SUM(Whours), 0) AS WipHours, 
              		  ISNULL(SUM(Wfee), 0) + ISNULL(SUM(Wwrupdn),0) AS WipCost, 
              		  ISNULL(SUM(Wexp), 0) + ISNULL(SUM(Wwrupdn), 0) AS WipExpenses
                FROM          WIP
                WHERE      (Windicator <> 'D')
                GROUP BY WCodeCat, WCodeSub, WCltID, WCltName, WCltNum, WEng) 
AS ActualSpent 
ON ClientBudgets.CBudCltID = ActualSpent.WCltID AND 
   ClientBudgets.CBudCodeSub = ActualSpent.WCodeSub INNER JOIN
   Clients ON ClientBudgets.CBudCltID = Clients.ID
WHERE     (ClientBudgets.CBudCltNum = @Client_Code) AND (ClientBudgets.CBudEng = @Client_EngCode)
ORDER BY ClientName, ServiceCategory, ServiceSubcategory

Open in new window


I tried taking out the nested Select statements and putting them into separate datasets to modify them to get the desired result thinking then I could just paste them back in with little trouble but it's not working as I hoped.

The ClientBudgets part went well returning the results shown in the image above 'Example with budget amounts'.
SELECT     CBudCltID, CBudCltName, CBudCltNum, CBudEng, CASE WHEN CBudCodeCat = '' THEN 'Bill' ELSE CBudCodeCat END AS CBudCodeCat, 
                      CASE WHEN CBudCodeSub = '' THEN 'WBS' ELSE CBudCodeSub END AS CBudCodeSub, CBudempID AS Emp_ID, SUM(ISNULL(CBudhours, 0)) AS Hours, 
                      SUM(ISNULL(CBudfee, 0)) AS Cost, SUM(ISNULL(CBudexp, 0)) AS Expenses, CBudEmpFName, CBudEmpLName
FROM         CltBudget
WHERE     (CBudCltNum = @Client_Code) AND (CBudEng = @Client_EngCode)
GROUP BY CBudCltID, CBudempID, CBudCodeCat, CBudCltNum, CBudEng, CBudCltName, CBudEmpFName, CBudEmpLName, CBudCodeSub
HAVING      (CBudempID <> 0)

Open in new window


When I add replace the code in the larger query I get this result:
Halfway there

Then I modified the ActualSpent Select with
SELECT     WCltID, WCltName, WCltNum, WEng, WCodeCat, WCodeSub, ISNULL(SUM(Whours), 0) AS WipHours, ISNULL(SUM(Wfee), 0) + ISNULL(SUM(Wwrupdn), 0) 
                      AS WipCost, ISNULL(SUM(Wexp), 0) + ISNULL(SUM(Wwrupdn), 0) AS WipExpenses, WempID
FROM         WIP
WHERE     (Windicator <> 'D')
GROUP BY WCodeCat, WCodeSub, WCltID, WCltName, WCltNum, WEng, WempID
HAVING      (WCltNum = @Client_Code) AND (WEng = @Client_EngCode)

Open in new window


which got me (when added to the larger query without the ClientBudgets update)
Actual spent results  which is good except that I have 10 results instead of 7 which means that 3 employees worked on the project that weren't budgeted to work on it (which happens and I need to capture these).  Plus one EmployeeID comes up as null which is surprising that it allowed that since I'm doing a 'ClientBudgets.Emp_ID = ActualSpent.WempID'.  

When I put both in the large query I get 42 results.  If I haven't lost anyone at this point, can someone help me figure out what I am doing wrong?  Maybe I should scrap the whole thing and start from scratch but it was working so well up until now I hate to abandon it but if that is what I need to do then I'll do it.

Any help is greatly appreciated!!

Here is an example of the final report with arrows point to the tables/charts using the dataset.
Example of what the report looks like
0
Comment
Question by:HSI_guelph
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39831484
Ok, a few points off the bat. First of all, it's not entirely clear what your problem is from your post, but based on what you say in the end I'm guessing you're getting too many records returned.
Secondly, you're linking your main query (the large one) to a table called "Clients" but you're not selecting any fields from this table. As such, I would leave it out.
That would leave 2 sub queries that combined should give you your overall data set. One part that retrieves the Client budget, and another part that retrieves the actual spends.
I've done a bit of rewriting on your original query just to give it a bit more structure, and this is what I have now:

SELECT     ClientBudgets.CBudCltID AS Client_ID, 
           ClientBudgets.CBudCltName AS ClientName, 
           ClientBudgets.CBudCltNum AS ClientNumber, 
           ClientBudgets.CBudEng AS EngagementCode, 
           ClientBudgets.CBudCodeCat AS ServiceCategory, 
           ClientBudgets.CBudCodeSub AS ServiceSubcategory, 
           ISNULL(ClientBudgets.Hours, 0) AS BudgetedHours, 
           ISNULL(ActualSpent.WipHours, 0) AS ActualHours, 
           ISNULL(ClientBudgets.Hours, 0) - ISNULL(ActualSpent.WipHours, 0) AS VarHours, 
           ISNULL(ClientBudgets.Cost, 0) AS BudgetedCost, 
           ISNULL(ActualSpent.WipCost, 0) AS ActualCost, 
           ISNULL(ClientBudgets.Cost, 0) - ISNULL(ActualSpent.WipCost, 0) AS VarCost, 
           ISNULL(ClientBudgets.Expenses, 0) AS BudgetedExpenses, 
           ISNULL(ActualSpent.WipExpenses, 0) AS ActualExpenses, 
           ISNULL(ClientBudgets.Expenses, 0) - ISNULL(ActualSpent.WipExpenses, 0) AS VarExpenses
From	
		--Client budgets
		(SELECT		CBudCltID as 'ClientID'
				,	CBudCltName as 'ClientName'
				,	CBudCltNum as 'ClientNumber'
				,	CBudEng as 'EngagementCode'
				,	CASE 
						WHEN CBudCodeCat = '' 
						THEN 'Bill' 
						ELSE CBudCodeCat 
					END AS CBudCodeCat as 'ServiceCategory'
				,	CASE 
						WHEN CBudCodeSub = '' 
						THEN 'WBS' 
						ELSE CBudCodeSub 
					END AS CBudCodeSub as 'ServiceSubCategory'
				,	CBudempID AS 'Emp_ID'
				,	SUM(ISNULL(CBudhours, 0)) AS 'Hours'
				,	SUM(ISNULL(CBudfee, 0)) AS 'Cost'
				,	SUM(ISNULL(CBudexp, 0)) AS 'Expenses'
				,	CBudEmpFName
				,	CBudEmpLName
		FROM	CltBudget
		WHERE	(CBudCltNum = @Client_Code) 
		AND		(CBudEng = @Client_EngCode)
		GROUP BY	CBudCltID
				,	CBudempID
				,	CBudCodeCat
				,	CBudCltNum
				,	CBudEng
				,	CBudCltName
				,	CBudEmpFName
				,	CBudEmpLName
				,	CBudCodeSub
		HAVING      (CBudempID <> 0)
		) as 'ClientBudgets'	
		Left outer join
		(
		--Actual spends
		SELECT		WCltID as 'ClientID' 
				,	WCltName
				,	WCltNum
				,	WEng
				,	WCodeCat
				,	WCodeSub as 'ServiceSubCategory'
				,	ISNULL(SUM(Whours), 0) AS WipHours
				,	ISNULL(SUM(Wfee), 0) + ISNULL(SUM(Wwrupdn), 0) AS WipCost
				,	ISNULL(SUM(Wexp), 0) + ISNULL(SUM(Wwrupdn), 0) AS WipExpenses
				,	WempID
		FROM		WIP
		WHERE	(Windicator <> 'D')
		and		WCltNum = @Client_Code
		and		WEng = @Client_EngCode
		GROUP BY	WCodeCat
				,	WCodeSub
				,	WCltID
				,	WCltName
				,	WCltNum
				,	WEng
				,	WempID
		) as 'ActualSpent'
			on	ClientBudgets.ClientID = ActualSpent.ClientID 
			and	ClientBudgets.ServiceSubCategory = ActualSpent.ServiceSubCategory

Open in new window


Now assuming that I understood your problem correctly (too many records returned), you have a "one to many" relationship or a "many to one" relationship between the 2 sub queries. So if you have 1 line for a budget, but 2 for the spends, your budget doubles because it returnes the same budget line twice, once for each record in the actual spends result.
To check this, ideally you should first run and post a result for the client budget, then for the actual spends, and then manually generate a table that shows the correct expected outcome. Run this for one client and project,
I suspect once we have this, the solution should be fairly straightforward.
0
 

Author Comment

by:HSI_guelph
ID: 39832605
@KvwielinkPosted - I had included the Clients table because every client/project (a client is a project) is in the Clients table (with the overall budget) but some where not appearing in the Cltbudgets table where the budget is set for specific categories.  This is probably due to older records but ideally each client will be in the Cltbudgets table so to clean up the coding I'm willing to drop the Clients table.

My results for the subqueries when run separately from the main query are:
ClientBudgets resultsActualSpent results
So for the ActualSpent I have 3 more rows than the ClientBudgets.  This is because 3 employees who were not budgeted billed hours against the project.  I need to capture those and provide a budget for them of 0.  

Here are examples of the results directly from the tables, perhaps that will help clarify things.  So there are 28 records in the budgets table and 248 in the actual table.  
Cltbudgets tableWIP table
My report will show the budgets, actual spent and variance for the labour (by person) and the budgets, actual spent and variance for the expenses.  We don't need to know whether the expense was gas, mileage or hotel expenses, just that they were Travel and Living expenses.  We also don't need to know what labour was spent on planning or trials but how much each person was budgeted and what they actually worked.  It would be easy if I could break them into two separate queries since they are two separate tables but I am using the same query in my two charts.  Though the charts don't require the same level of detail so I could make a query specific for the charts but then that gives me three datasets where I was only using one.  Perhaps that is the smarter way to go, it is less prone to errors and would make updating the queries easier in the future.

I will try your code and let you know how that goes.
0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 39832779
Ok, made a few changes to the original query I put up, but based on your feedback I think you need a full outer join rather than a left outer join. As there are spends booked which are not budgeted, a left join from budget on spends will omit these records. A full join will return all records from the budget, and all records from the spends, for the given criteria. I also found a few syntax errors in my earlier query, sorry for not checking that.
Try this and see what you get:

SELECT     ClientBudgets.CBudCltID AS Client_ID, 
           ClientBudgets.CBudCltName AS ClientName, 
           ClientBudgets.CBudCltNum AS ClientNumber, 
           ClientBudgets.CBudEng AS EngagementCode, 
           ClientBudgets.CBudCodeCat AS ServiceCategory, 
           ClientBudgets.CBudCodeSub AS ServiceSubcategory, 
           ISNULL(ClientBudgets.Hours, 0) AS BudgetedHours, 
           ISNULL(ActualSpent.WipHours, 0) AS ActualHours, 
           ISNULL(ClientBudgets.Hours, 0) - ISNULL(ActualSpent.WipHours, 0) AS VarHours, 
           ISNULL(ClientBudgets.Cost, 0) AS BudgetedCost, 
           ISNULL(ActualSpent.WipCost, 0) AS ActualCost, 
           ISNULL(ClientBudgets.Cost, 0) - ISNULL(ActualSpent.WipCost, 0) AS VarCost, 
           ISNULL(ClientBudgets.Expenses, 0) AS BudgetedExpenses, 
           ISNULL(ActualSpent.WipExpenses, 0) AS ActualExpenses, 
           ISNULL(ClientBudgets.Expenses, 0) - ISNULL(ActualSpent.WipExpenses, 0) AS VarExpenses
From	
		--Client budgets
		(SELECT		CBudCltID as 'ClientID'
				,	CBudCltName as 'ClientName'
				,	CBudCltNum as 'ClientNumber'
				,	CBudEng as 'EngagementCode'
				,	CASE 
						WHEN CBudCodeCat = '' 
						THEN 'Bill' 
						ELSE CBudCodeCat 
					END as 'ServiceCategory'
				,	CASE 
						WHEN CBudCodeSub = '' 
						THEN 'WBS' 
						ELSE CBudCodeSub 
					END as 'ServiceSubCategory'
				,	CBudempID AS 'Emp_ID'
				,	SUM(ISNULL(CBudhours, 0)) AS 'Hours'
				,	SUM(ISNULL(CBudfee, 0)) AS 'Cost'
				,	SUM(ISNULL(CBudexp, 0)) AS 'Expenses'
				,	CBudEmpFName
				,	CBudEmpLName
		FROM	CltBudget
		WHERE	CBudCltNum = @Client_Code
		AND		CBudEng = @Client_EngCode
		AND		CBudempID <> 0
		GROUP BY	CBudCltID
				,	CBudempID
				,	CBudCodeCat
				,	CBudCltNum
				,	CBudEng
				,	CBudCltName
				,	CBudEmpFName
				,	CBudEmpLName
				,	CBudCodeSub
		) ClientBudgets
		
		full outer join
		
		(
		--Actual spends
		SELECT		WCltID as 'ClientID' 
				,	WCltName as 'ClientName'
				,	WCltNum as 'ClientNumber'
				,	WEng as 'EngagementCode'
				,	WCodeCat as 'ServiceCategory'
				,	WCodeSub as 'ServiceSubCategory'
				,	ISNULL(SUM(Whours), 0) AS WipHours
				,	ISNULL(SUM(Wfee), 0) + ISNULL(SUM(Wwrupdn), 0) AS WipCost
				,	ISNULL(SUM(Wexp), 0) + ISNULL(SUM(Wwrupdn), 0) AS WipExpenses
				,	WempID
		FROM		WIP
		WHERE	(Windicator <> 'D')
		and		WCltNum = @Client_Code
		and		WEng = @Client_EngCode
		GROUP BY	WCodeCat
				,	WCodeSub
				,	WCltID
				,	WCltName
				,	WCltNum
				,	WEng
				,	WempID
		)  ActualSpent
			on	ClientBudgets.ClientID = ActualSpent.ClientID 
			and	ClientBudgets.ServiceSubCategory = ActualSpent.ServiceSubCategory

Open in new window

0
 

Author Comment

by:HSI_guelph
ID: 39833900
I ended up making another dataset and using it to populate the labour table since I realized that only one table in the report is going to change.  I tried your code but it still was giving me 7 results.  I figure this is the best way to get it working.  Thanks for replying!

SELECT DISTINCT 
                      WCltID AS Client_ID, WCltName AS ClientName, WCltNum AS ClientNumber, WEng AS EngagementCode, WCodeCat AS ServiceCategory, 
                      WCodeSub AS ServiceSubcategory, ISNULL(SUM(Whours), 0) AS ActualHours, ISNULL(SUM(Wfee), 0) + ISNULL(SUM(Wwrupdn), 0) AS ActualCost, ISNULL(SUM(Wexp), 
                      0) + ISNULL(SUM(Wwrupdn), 0) AS ActualExpenses, WempID AS WipEmp_ID,
                          (SELECT     Empfname
                            FROM          Employee
                            WHERE      (ID = WIP.WempID)) AS EmpFName, WEmpLName AS EmpLName, ISNULL
                          ((SELECT     SUM(ISNULL(CBudhours, 0)) AS hours
                              FROM         CltBudget
                              WHERE     (CBudCltNum = @Client_Code) AND (CBudEng = @Client_EngCode) AND (CBudempID = WIP.WempID)), 0) AS BudgetedHours, ISNULL
                          ((SELECT     SUM(ISNULL(CBudfee, 0)) AS fee
                              FROM         CltBudget AS CltBudget_1
                              WHERE     (CBudCltNum = @Client_Code) AND (CBudEng = @Client_EngCode) AND (CBudempID = WIP.WempID)), 0) AS BudgetedCost
FROM         WIP
WHERE     (ISNULL(Windicator, 0) <> 'D')
GROUP BY WCodeCat, WCodeSub, WCltID, WCltName, WCltNum, WEng, WempID, WEmpLName
HAVING      (WCltNum = @Client_Code) AND (WEng = @Client_EngCode)

Open in new window

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now