Help with calculation and query

  • 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

    ---------

    CodeSub_codeName Amounts

    Dir20 Main_dir 100

    Rei20 Main_Rei 50

    Dir50 Withhold_dir 10

    Rei50 Withhold_Rei 10

    Output

    CodeSub_codeNameAmounts

    Dir20 Main_dir90

    Rei20 Main_Rei40

  • 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

    FROMCTE_Mainm

    INNER JOINCTE_Withholdw 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

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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