Post reply

Simple Subtraction using subquery?

  • March 13, 2018 at 4:00 pm

    #333932

    I have a table T1 for simplicity.
    Within this table I have 3 fields:
     

    • Line(Int),
    • ServiceDay (int), and
    • Value (float)

    I want to subtract the Value of Line 21 from the Value of Line 20, for the correct Day.  The table holds the last 8 days (rolling)
    To reiterate, Value from Line 20 is @Minuend and Value from Line 21 is @subtrahend.

    I want the difference for each of the 8 days.

    thanks in advance:ermm::ermm::ermm:

  • March 14, 2018 at 8:29 am

    #1983165

    Use LAG() 
    https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql

  • March 14, 2018 at 8:51 am

    #1983174

    pietlinden - Wednesday, March 14, 2018 8:29 AM

    Use LAG() 
    https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql

    With the link of PietLinden you can investigate the possibility's. Just try and build samples with LAG and LEAD gives you the best insight.
    But for completeness here's a sample you can use as a base for your own query 😉
    create table #test (
                    Line int,
                    ServiceDay int,
                    Value float)
    insert into #test values
        (1, 20180306, 30)
        , (2, 20180307, 9)
        , (3, 20180308, 38)
        , (4, 20180309, 12)
        , (5, 20180310, 6)
        , (6, 20180311, 24)
        , (7, 20180312, 2)
        , (8, 20180313, 0)

    select Line
        , ServiceDay
        , LAG(Value, 1) over (order by ServiceDay) as previous
        , Value
        , LAG(Value, 1) over (order by ServiceDay) - Value as rolling_substraction
    from #test

    drop table #test

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • March 14, 2018 at 10:23 am

    #1983217

    Thank you. I learned a new function 🙂. 
    But the results from this test are not quite what I need.  In my case, I am doing simple subtraction for each day. I don't need to look back to previous days. What I want is subtraction for the values as they appear that day.  Below is my grid....I seek the difference only between lines 20 and 21. I'm not getting how LAG would work. 

    Thanks in advance

    Line3/63/73/83/93/103/113/123/13
    20377331299269277275276344
    2171401209264185
    difference306291287269268249235259

  • March 14, 2018 at 10:44 am

    #1983231

    Then you need ORDER BY Line, not ServiceDay, in your OVER clause.

    John

  • March 14, 2018 at 11:54 am

    #1983250

    Thank you, but I gave up on LAG, LAG does not work in this case. I created a Derived table as the solution.

    SELECT

    SUM(B.Value1) as Minuend,

    SUM(B.Value2) as Subtrahend,

    B.serviceday

    INTO #subtractStaff

    from

    (

    select Line,

    Value as Value1,

    null as Value2,

    serviceday

    from T1

    where line = 20

    UNION

    select Line,

    null as Value1,

    Value as Value2,

    serviceday

    from T1

    where line = 21

    )B

    GROUP BY

    ServiceDay

    MinuendSubtrahendservicedaydiff
    316245292
    312266286
    301567245
    347808267
    350869264
    3096010249
    2904711243
    2951312282
    3456313282


Viewing 6 posts - 1 through 5 (of 5 total)

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