August 20, 2013 at 1:03 pm
How to calculate the update amount for the example below? Table A has the following rows. I want to get the output only for Main_dir and Main_rei which are reduced by Withhold_dir and Withhold_rei respectively which has sub_code = 50. The common connection between the rows is the Code.
Table A
---------
Code Sub_code Name Amounts
Dir 20 Main_dir 100
Rei 20 Main_Rei 50
Dir 50 Withhold_dir 10
Rei 50 Withhold_Rei 10
Output
Code Sub_code Name Amounts
Dir 20 Main_dir 90
Rei 20 Main_Rei 40
August 20, 2013 at 1:25 pm
I'd do it this way:
WITH CTE_Main AS
(
SELECT Code, Sub_Code, Name, Amounts
FROM TableA
WHERE Sub_Code = 20
)
, CTE_Withhold AS
(
SELECT Code, Amounts
FROM TableA
WHERE Sub_Code = 50
)
SELECT m.Code, m.Sub_Code, m.Name, Amounts = m.Amounts - w.Amounts
FROM CTE_Main m
INNER JOIN CTE_Withhold w ON m.Code = w.Code;
The CTEs aren't necessary, I just included them to make the code more readable.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 20, 2013 at 7:23 pm
Or perhaps like this?
WITH SampleData (Code, Sub_code, Name, Amounts) AS (
SELECT 'Dir',20,'Main_dir',100
UNION ALL SELECT 'Rei',20,'Main_Rei',50
UNION ALL SELECT 'Dir',50,'Withhold_dir',10
UNION ALL SELECT 'Rei',50,'Withhold_Rei',10
)
SELECT Code, Sub_code=MIN(Sub_code)
,Name=MIN(Name)
,Amounts=SUM(CASE Sub_code WHEN 20 THEN Amounts ELSE -Amounts END)
FROM SampleData
GROUP BY Code
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply