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]