Calculate total between two columns

  • I have update schema and i upload image with my desire result.

    CREATE TABLE #NONAMETABLE(

    sinGroup NVARCHAR(10)

    ,column1 INT

    ,column2 int

    );

    INSERT INTO #NONAMETABLE(sinGroup,column1,column2) VALUES

    ('y1',100,0),

    ('y2',0,60),

    ('z1',150,0),

    ('z2',0,50)

    ;

    drop table #NONAMETABLE

  • Are you doing a running total? Is this in SSRS that you need to do this? Or in T-SQL?

  • T- SQL

  • a starter for you to consider

    CREATE TABLE #NONAMETABLE(

    ID INT

    ,column1 INT

    );

    INSERT INTO #NONAMETABLE(ID,column1) VALUES

    (1,100.00)

    ,(2,60.00);

    SELECT column1, column1 - LEAD(column1,1,NULL) OVER(order by ID) as column2

    FROM #NONAMETABLE

    DROP TABLE #NONAMETABLE;

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Slightly different approach from JLS's

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    GO

    DECLARE @SAMPLE_DATA TABLE

    (

    column1 NUMERIC(12,2) NOT NULL

    ,column2 NUMERIC(12,2) NOT NULL

    );

    INSERT INTO @SAMPLE_DATA(column1,column2)

    VALUES ( 100.00, 40.00)

    ,( 90.00, 70.00)

    ,( 200.00, 140.00)

    ;

    ;WITH BASE_DATA AS

    (

    SELECT

    ROW_NUMBER() OVER

    (

    ORDER BY (SELECT NULL)

    ) AS RID

    ,SD.column1

    ,SD.column2

    FROM @SAMPLE_DATA SD

    )

    SELECT

    BD.RID

    ,BD.column1

    ,BD.column2

    FROM BASE_DATA BD

    UNION ALL

    SELECT

    BD.RID

    ,BD.column1 - BD.column2

    ,0

    FROM BASE_DATA BD

    ORDER BY RID ASC

    ,BD.column2 DESC

    ;

    Results

    RID column1 column2

    ---- -------- --------

    1 100.00 40.00

    1 60.00 0.00

    2 90.00 70.00

    2 20.00 0.00

    3 200.00 140.00

    3 60.00 0.00

  • No, this is my starting point:

    CREATE TABLE #NONAMETABLE(

    ID INT

    ,column1 INT

    ,column2 int

    );

    INSERT INTO #NONAMETABLE(ID,column1,column2) VALUES

    (1,100.00,40)

    ;

    drop table #NONAMETABLE

  • kdejan87 (9/14/2015)


    No, this is my starting point:

    CREATE TABLE #NONAMETABLE(

    ID INT

    ,column1 INT

    ,column2 int

    );

    INSERT INTO #NONAMETABLE(ID,column1,column2) VALUES

    (1,100.00,40)

    ;

    drop table #NONAMETABLE

    Quick question, can you post a more complete sample and result set, single row sample leaves quiet few questions unanswered.

    😎

  • I have update schema and i upload image with my desire result. I realized I need this result.

    CREATE TABLE #NONAMETABLE(

    sinGroup NVARCHAR(10)

    ,column1 INT

    ,column2 int

    );

    INSERT INTO #NONAMETABLE(sinGroup,column1,column2) VALUES

    ('y1',100,0),

    ('y2',0,60),

    ('z1',150,0),

    ('z2',0,50)

    ;

    drop table #NONAMETABLE

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

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