CODEWITHSUNDEEP
sqlsql-server
woggles
woggles

Reputation: 7444

How to find the difference of 2 values for the 2 latest dates

Say I have the following data: 

Client                                  Date                        Money
31CDAAFE-93BA-4666-AE6D-2253059990BB    2011-08-31          200000000.00    
31CDAAFE-93BA-4666-AE6D-2253059990BB    2011-07-31          198000000.00
31CDAAFE-93BA-4666-AE6D-2253059990BB    2011-04-31          108000000.00

Is there an easy way to find the difference between the amounts (Money) for the latest and second latest record?

So for this data set the answer would be: 

31CDAAFE-93BA-4666-AE6D-2253059990BB    2011-08-31           2000000.00

I basically need to return the 1st record with the client and date and the difference between the 2. Note there are multiple clients, dates and money and if there is only one date for a client I can just return the amount.

I've got as far as ranking the data but can't for the life of me figure out how to end up the result in one row...am I missing something obvious?

SELECT     
      Client,
      Date,  
      Money,
      ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC) AS Sequence
FROM
      MyTable

Upvotes: 1

Views: 96

Answers (4)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

You have most of the work done.

All that's left to do is to wrap your statement into a WITH statement, JOIN with itself and retain all rows where Sequence = 1.

Note that clients with only 1 record will not be shown. I assume that is the most logical thing to do. You should change the INNER JOIN to a LEFT OUTER JOIN if my assumption is wrong.

Using WITH

;WITH q AS (
  SELECT     
        Client,
        Date,  
        Money,
        ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC) AS Sequence
  FROM
        MyTable
)
SELECT  q1.Client
        , q1.Date
        , q1.Money - q2.Money
FROM    q q1
        INNER JOIN q q2 ON q2.Client = q1.Client
                           AND q2.Sequence = q1.Sequence + 1
WHERE   q1.Sequence = 1

Equivalent statement without WITH

As you indicate in comments that you should read up on using WITH statements, in essence, the WITH statement in the example

  • shortens the statement
  • makes it more readable
  • makes it more maintainable.

Note that in this example, there is no recursion involved (usually associated with WITH statements)

SELECT  q1.Client
        , q1.Date
        , q1.Money - q2.Money
FROM    (
          SELECT     
                Client,
                Date,  
                Money,
                ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC) AS Sequence
          FROM
                MyTable
        ) q1
        INNER JOIN (
          SELECT     
                Client,
                Date,  
                Money,
                ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC) AS Sequence
          FROM
                MyTable
        ) q2 ON q2.Client = q1.Client
                AND q2.Sequence = q1.Sequence + 1
WHERE   q1.Sequence = 1

Upvotes: 1

Mark Jones
Mark Jones

Reputation: 402

I would break it down into two embedded sub-queries: One to find the row with the maximum date, the other to find the row with the maximum date not equaling the date of the first query, the join those queries on 1=1 (since you're getting one row from each), then get the difference in the overall query.

Upvotes: 0

gbn
gbn

Reputation: 432672

;WITH aCTE AS
(
SELECT
   Client, Date, Money,
   ROW_NUMBER() OVER (PARTITION BY Client ORDER BY [Date] DESC) AS rn
FROM
   MyTable
)
SELECT
   T1.Client, T1.Money - ISNULL(T2.Money, 0)
FROM
   aCTE T1
   LEFT JOIN
   aCTE T2 ON T1.Client = T2.Client AND T2.rn = 2
WHERE
   T1.rn = 1

Note: this covers where there is no "previous" row

Upvotes: 3

Lamak
Lamak

Reputation: 70678

You can try this:

WITH CTE AS
(
    SELECT     
          Client,
          Date,  
          Money,
          ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC) AS Sequence
    FROM MyTable
)

SELECT A.Client, A.[Money] - B.[Money] MoneyDifference
FROM (SELECT * FROM CTE WHERE Sequence = 1) A
INNER JOIN (SELECT * FROM CTE WHERE Sequence = 2) B
ON A.Client = B.Client

Upvotes: 1

Related Questions