Post reply

Subtract values from records within the same column

  • December 19, 2019 at 7:27 pm

    #3709222

    Hello All,

    I have a table where users upload expenses data.

    The Expenses column contains expenses amounts, but also credited amounts (negative values).

    The data usually is uploaded on weekly basis.

    This is how the data looks like for one customer.

    ID | Expenses | Vendor | UploadDate

    1 | 750.5 | Vendor1 | 9/17/2019

    2 | 575.5 | Vendor2 | 9/17/2019

    3 | 309.7 | Vendor2 | 10/2/2019

    4 | -575.5 | Vendor2 | 10/2/2019

    5 | -750.5 | Vendor1 | 10/2/2019

    6 | 309.7 | Vendor1 | 10/2/2019

    And this is my desired output

    Expenses | Vendor | UploadDate

    309.7 | Vendor2 | 10/2/2019

    309.7 | Vendor1 | 10/2/2019

    This is something that might require me to add an additional column to the table to separate credited amounts from expenses, so it can be queried more efficient.

    Any help is greatly appreciated.

    Thanks and Merry Christmas!

    Thanks in advance.

     

     

  • December 19, 2019 at 7:30 pm

    #3709223 

    SELECT SUM(Expenses) AS Expense, Vendor, MAX(UploadDate) AS UploadDate
    FROM dbo.a_table
    GROUP BY Vendor

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply