CODEWITHSUNDEEP
sqlsql-servert-sql
Hendrik Kleine
Hendrik Kleine

Reputation: 105

SQL - Value difference between specific rows

My query is as follows

SELECT 
    LEFT(TimePeriod,6) Period, -- string field with YYYYMMDD
    SUM(Value) Value
FROM 
    f_Trans_GL
WHERE 
    Account = 228
GROUP BY 
    TimePeriod

And it returns

Period   Value
---------------
201412    80
201501    20
201502    30
201506    50
201509   100
201509   100

I'd like to know the Value difference between rows where the period is 1 month apart. The calculation being [value period] - [value period-1].

The desired output being;

Period   Value   Calculated
-----------------------------------
201412    80      80 - null = 80
201501    20      20 - 80 = -60
201502    30      30 - 20 = 10
201506    50      50 - null = 50
201509   100     (100 + 100) - null = 200

This illustrates a second challenge, as the period needs to be evaluated if the year changes (the difference between 201501 and 201412 is one month).

And the third challenge being a duplicate Period (201509), in which case the sum of that period needs to be evaluated.

Any indicators on where to begin, if this is possible, would be great!

Thanks in advance

===============================

After I accepted the answer, I tailored this a little to suit my needs, the end result is:

WITH cte
AS (SELECT

  ISNULL(CAST(TransactionID AS nvarchar), '_nullTransactionId_') + ISNULL(Description, '_nullDescription_') + CAST(Account AS nvarchar) + Category + Currency + Entity + Scenario AS UID,

  LEFT(TimePeriod, 6) Period,
  SUM(Value1) Value1,
  CAST(LEFT(TimePeriod, 6) + '01' AS date) ord_date
FROM MyTestTable
GROUP BY LEFT(TimePeriod, 6),
         TransactionID,
         Description,
         Account,
         Category,
         Currency,
         Entity,
         Scenario,
         TimePeriod)
SELECT
  a.UID,
  a.Period,
  --a.Value1,
  ISNULL(a.Value1, 0) - ISNULL(b.Value1, 0) Periodic
FROM cte a
LEFT JOIN cte b
  ON a.ord_date = DATEADD(MONTH, 1, b.ord_date)
ORDER BY a.UID

I have to get the new value (Periodic) for each UID. This UID must be determined as done here because the PK on the table won't work.

But the issue is that this will return many more rows than I actually have to begin with in my table. If I don't add a GROUP BY and ORDER by UID (as done above), I can tell that the first result for each combination of UID and Period is actually correct, the subsequent rows for that combination, are not.

I'm not sure where to look for a solution, my guess is that the UID is the issue here, and that it will somehow iterate over the field... any direction appreciated.

Upvotes: 1

Views: 87

Answers (3)

JayValkyr
JayValkyr

Reputation: 131

Without cte, you can also try something like this

SELECT A.Period,A.Value,A.Value-ISNULL(B.Value) Calculated
FROM
(
    SELECT LEFT(TimePeriod,6) Period
    DATEADD(M,-1,(CONVERT(date,LEFT(TimePeriod,6)+'01'))) PeriodDatePrev,SUM(Value) Value
    FROM f_Trans_GL
    WHERE Account = 228
    GROUP BY LEFT(TimePeriod,6)
) AS A
LEFT OUTER JOIN
(
    SELECT LEFT(TimePeriod,6) Period
    (CONVERT(date,LEFT(TimePeriod,6)+'01')) PeriodDate,SUM(Value) Value
    FROM f_Trans_GL
    WHERE Account = 228
    GROUP BY LEFT(TimePeriod,6)
) AS B
ON (A.PeriodDatePrev = B.PeriodDate)
ORDER BY 1

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

As pointed by other, first mistake is in Group by you need to Left(timeperiod, 6) instead of timeperiod.

For remaining calculation try something like this

;WITH cte 
     AS (SELECT LEFT(timeperiod, 6)                      Period, 
                Sum(value)                               Value, 
                Cast(LEFT(timeperiod, 6) + '01' AS DATE) ord_date 
         FROM   f_trans_gl 
         WHERE  account = 228 
         GROUP  BY LEFT(timeperiod, 6)) 
SELECT a.period, 
       a.value, 
       a.value - Isnull(b.value, 0) 
FROM   cte a 
       LEFT JOIN cte b 
              ON a.ord_date = Dateadd(month, 1, b.ord_date)

If you are using SQL SERVER 2012 then this can be easily done using LAG analytic function

Upvotes: 1

iamdave
iamdave

Reputation: 12243

Using a derived table, you can join the data to itself to find rows that are in the preceding period. I have converted your Period to a Date value so you can use SQL Server's dateadd function to check for rows in the previous month:

;WITH cte AS
(
SELECT 
    LEFT(TimePeriod,6) Period, -- string field with YYYYMMDD
    CAST(TimePeriod + '01' AS DATE) PeriodDate
    SUM(Value) Value
FROM f_Trans_GL
WHERE Account = 228
GROUP BY LEFT(TimePeriod,6)
)
SELECT c1.Period,
    c1.Value,
    c1.Value - ISNULL(c2.Value,0) AS Calculation
FROM cte c1
    LEFT JOIN cte c2
        ON c1.PeriodDate = DATEADD(m,1,c2.PeriodDate)

Upvotes: 1

Related Questions