Member Avatar for jtok

I'm working with MS SQL Server 2005. I am trying to add two columns together, and I have more or less succeeded. However, it obviously adds the entirety of each column, rather than calculating for each row.

I can't just include each column in the original SELECT statement, because it causes some of the charges to be duplicated.
(The reason for this is that if multiple services or parts are purchased, the results include every combination of parts and service. This, of course, results in a repetition of charges.)
I am open to completely changing the way this search is done, or even using a stored procedure to accomplish it. I merely would like it to work.

Here is basically what I a working with. I simplified it as much as I felt safe doing.

SELECT CUSTOMER.*, PROBLEM.ID, PROBLEM.STORE_ID,
 
((SELECT SUM(PROBLEM_SERVICE_CHARGE.ChargeAmount) 
	FROM PROBLEM_SERVICE_CHARGE 
	INNER JOIN PROBLEM ON PROBLEM_SERVICE_CHARGE.PROBLEM_ID = PROBLEM.ID
	INNER JOIN [SYSTEM] ON PROBLEM.SYSTEM_ID = SYSTEM.ID
	INNER JOIN CUSTOMER_SYSTEM ON SYSTEM.ID = CUSTOMER_SYSTEM.SYSTEM_ID
	INNER JOIN CUSTOMER ON CUSTOMER_SYSTEM.CUSTOMER_ID = CUSTOMER.ID
	WHERE PROBLEM.PickedUp = 0)

 + --Note the addition of these two SELECT statements.

(SELECT SUM(PART.PartCharge*1.0625) --The 1.0625 just takes care of the tax on the parts
	FROM PART
	INNER JOIN PROBLEM_PART ON PART.ID = PROBLEM_PART.PART_ID
	INNER JOIN PROBLEM ON PROBLEM_PART.PROBLEM_ID = PROBLEM.ID
	INNER JOIN [SYSTEM] ON PROBLEM.SYSTEM_ID = SYSTEM.ID
	INNER JOIN CUSTOMER_SYSTEM ON SYSTEM.ID = CUSTOMER_SYSTEM.SYSTEM_ID
	INNER JOIN CUSTOMER ON CUSTOMER_SYSTEM.CUSTOMER_ID = CUSTOMER.ID
	WHERE PROBLEM.PickedUp = 0)) 
AS Charge --The two added select statements become the Charge column

FROM [CUSTOMER] 
INNER JOIN CUSTOMER_SYSTEM ON CUSTOMER.ID = CUSTOMER_SYSTEM.CUSTOMER_ID 
INNER JOIN [SYSTEM] ON CUSTOMER_SYSTEM.SYSTEM_ID = SYSTEM.ID 
INNER JOIN PROBLEM ON SYSTEM.ID = PROBLEM.SYSTEM_ID 
INNER JOIN PROBLEM_SERVICE_CHARGE ON PROBLEM.ID = PROBLEM_SERVICE_CHARGE.PROBLEM_ID 
LEFT JOIN PROBLEM_PART ON PROBLEM.ID = PROBLEM_PART.PROBLEM_ID 
LEFT JOIN PART ON PROBLEM_PART.PART_ID = PART.ID 

WHERE PROBLEM.PickedUp = 0

ORDER BY [LastName], [FirstName]
  • 1 Contributor
  • 1 Reply
  • 103 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by jtok

Dani AI

Generated

Nice, — creating small aggregated views for the service and part totals is a solid, maintainable fix. The root issue is the join explosion that happens when two child collections (services and parts) are joined together: each part gets paired with each service, so sums over the joined rows double-count. Your approach isolates each aggregation and then joins the single-row-per-customer totals back, which avoids that multiplication.

If a permanent view feels heavy, the same pattern can be done without creating schema objects: use a CTE or inline derived tables to compute per-customer (or per-problem) sums and then join them. For more granular control you can aggregate at the problem level first and then roll those up to the customer level; alternatively, OUTER APPLY lets you run a correlated aggregation per row without materializing a view. For reuse across many queries, an inline table-valued function is another option.

A few practical tips to keep things correct and fast:

  • Avoid SELECT * in the final result; list needed columns so the optimizer and consumers are clearer.
  • Don’t use DISTINCT to hide duplicates caused by joins — fix the aggregation/join shape instead.
  • Choose where to apply tax carefully (per-part vs. on the summed parts) and use DECIMAL with sufficient scale to avoid rounding drift.
  • Check the execution plan and use SET STATISTICS IO/TIME to measure changes after rewriting.
  • Make sure there are useful indexes on the join and filter columns (for example the problem foreign keys and your PickedUp filter). For heavy, read-mostly workloads, consider an indexed view (with its schema-binding rules) as a next step.

For details on grouping and per-row correlated aggregation see the GROUP BY guidance and the FROM/APPLY notes in the SQL Server docs:

FROM / APPLY (Transact-SQL)
Indexing and indexed views

Your view-based solution is perfectly fine for clarity and maintenance; the refinements above help when you need better performance or different result shapes.

Member Avatar for jtok

I finally came up with a solution to this problem, and thought I'd share it.
I created a view for each of the subqueries like so:

CREATE VIEW Pickup_ServiceCharge
AS
SELECT DISTINCT CUSTOMER.ID, SUM(isnull(PROBLEM_SERVICE_CHARGE.ChargeAmount,0)) AS ServiceCharge
	FROM PROBLEM_SERVICE_CHARGE 
	INNER JOIN PROBLEM ON PROBLEM_SERVICE_CHARGE.PROBLEM_ID = PROBLEM.ID
	INNER JOIN [SYSTEM] ON PROBLEM.SYSTEM_ID = SYSTEM.ID
	INNER JOIN CUSTOMER_SYSTEM ON SYSTEM.ID = CUSTOMER_SYSTEM.SYSTEM_ID
	INNER JOIN CUSTOMER ON CUSTOMER_SYSTEM.CUSTOMER_ID = CUSTOMER.ID
	WHERE PROBLEM.PickedUp = 0
	GROUP BY CUSTOMER.ID;
GO

CREATE VIEW Pickup_PartCharge
AS
SELECT DISTINCT CUSTOMER.ID, SUM(isnull((PART.PartCharge*1.0625),0)) AS PartCharge
	FROM PART
	INNER JOIN PROBLEM_PART ON PART.ID = PROBLEM_PART.PART_ID
	INNER JOIN PROBLEM ON PROBLEM_PART.PROBLEM_ID = PROBLEM.ID
	INNER JOIN [SYSTEM] ON PROBLEM.SYSTEM_ID = SYSTEM.ID
	INNER JOIN CUSTOMER_SYSTEM ON SYSTEM.ID = CUSTOMER_SYSTEM.SYSTEM_ID
	INNER JOIN CUSTOMER ON CUSTOMER_SYSTEM.CUSTOMER_ID = CUSTOMER.ID
	WHERE PROBLEM.PickedUp = 0
	GROUP BY CUSTOMER.ID;
GO

Then I LEFT JOINed the views in the existing query ON the CUSTOMER.ID, making sure to use isnull() to give a default value of 0.

SELECT DISTINCT CUSTOMER.*, PROBLEM.ID, PROBLEM.STORE_ID, (isnull(Pickup_ServiceCharge.ServiceCharge,0) + isnull(Pickup_PartCharge.PartCharge,0)) AS Charge
FROM [CUSTOMER] 
INNER JOIN CUSTOMER_SYSTEM ON CUSTOMER.ID = CUSTOMER_SYSTEM.CUSTOMER_ID 
INNER JOIN [SYSTEM] ON CUSTOMER_SYSTEM.SYSTEM_ID = SYSTEM.ID 
INNER JOIN PROBLEM ON SYSTEM.ID = PROBLEM.SYSTEM_ID 
LEFT JOIN Pickup_ServiceCharge ON CUSTOMER.ID = Pickup_ServiceCharge.ID
LEFT JOIN Pickup_PartCharge ON CUSTOMER.ID = Pickup_PartCharge.ID
WHERE PROBLEM.PickedUp = 0
ORDER BY [LastName], [FirstName]

Viola! Problem solved. If there are any questions, just post them in this thread and I'll get them.

commented: Thank you for sharing +10
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.