Compare two dates from columns SQL

Question

I am having some issues with getting this working. I have a table with this data in it.

|     DateStarted       |     Field9     |     Field2     |      ID      |    Field6   |
----------------------------------------------------------------------------------------
|  2013-04-15 09:23:00  |      TEST1     |      TEST2     |      1       |     2000    |
|  2013-04-08 09:23:00  |      TEST1     |      TEST2     |      2       |      180    |
|  2013-04-15 09:23:00  |      TEST2     |      TEST3     |      3       |     1000    |
|  2013-04-04 09:23:00  |      TEST2     |      TEST3     |      7       |       80    |
|  2013-04-03 09:23:00  |      TEST2     |      TEST4     |      5       |       70    |

What my end goal is was to have the last two dates for the value for Field9 be returned so that I could subtract the value of Field6 for each unique instance of Field9. Below is an example of the return.

|     DateStarted       |     Field1     |     Field2     |      ID      |    SUB      |
----------------------------------------------------------------------------------------
|  2013-04-15 09:23:00  |      TEST1     |      TEST2     |      1       |     1820    |
|  2013-04-15 09:23:00  |      TEST2     |      TEST3     |      3       |      920    |

So for the second row it took the two greatest dates and then took the value of field6 and subtracted them returning just the one row.

Aaron Bertrand · Accepted Answer · 2013-04-15 17:18:33Z

2

You can get the latest row for each unique value of Field1 by using partitioned windowing functions.

;WITH x AS
(
  SELECT DateStarted, Field9, Field2, ID, Field6,
   rn = ROW_NUMBER() OVER (PARTITION BY Field9 ORDER BY DateStarted DESC)
  FROM dbo.your_table_name
),
y AS 
(
  SELECT x.*, [SUB] = x.Field6 - COALESCE(y.Field6, 0)
  FROM x LEFT OUTER JOIN x AS y
  ON x.Field9 = y.Field9
  AND x.rn = 1 AND y.rn = 2
)
SELECT DateStarted, Field1 = Field9, Field2, ID, [SUB]
  FROM y
  WHERE rn = 1
  ORDER BY Field1;

SQL fiddle demo

edited Apr 15, 2013 at 17:18

answered Apr 15, 2013 at 15:58

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

John Woo Over a year ago

hi Aaron, i've seen many sql developers use a syntax like yours: rn = ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY DateStarted DESC), is this new or old syntax than ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY DateStarted DESC) rn because I always use the second one.?

Aaron Bertrand Over a year ago

@JW there are many ways to express aliases, and it is mostly just style. You can read why I like alias = expression syntax and dislike expression alias and would prefer expression AS alias instead.

thedemon Over a year ago

@AB I have altered what you you provided to ;WITH x AS ( SELECT DateStarted, Field9, Field2, fileid, Field3, rn = ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY DateStarted DESC) FROM files where Status=1 and ProjectID=21 and Field10='EMAIL' ) SELECT DateStarted, Field9, Field2, fileid, Field3 FROM x WHERE rn = 1 ORDER BY Field9; but it just returns the greatest date for one value of Field9 where as it should give each of the greatest dates for each of the values in Field9

Aaron Bertrand Over a year ago

@thedernon I can't read that comment, but it seems like your query is working against different data and with different requirements than your actual question. Can you please edit your question so I'm solving the right problem?

Aaron Bertrand Over a year ago

@thedemon still don't understand the problem, sorry. You changed the column names, and you added a description of exactly what my code does. Instead of adding word problems and obsessing over column names, can you explain how my query doesn't produce the desired results your after? Once I updated your fictional column names (please don't do that), this SQL fiddle seems to suggest my code will work just fine for what you seem to be after.

|

Gordon Linoff · Accepted Answer · 2013-04-15 16:06:04Z

0

One way to get the difference is to identify the two rows and then aggregate them together:

select MAX(case when seqnum = 1 then DateStarted end), Field1,
       max(case when seqnum = 1 then Field2 end) as Field2
       MAX(case when seqnum = 1 then id end) as Id,
       MAX(case when seqnum = 1 then field3 end) - MAX(case when seqnum = 2 then field 3 end) as sub
from (SELECT DateStarted, Field1, Field2, ID, Field3,
             ROW_NUMBER() OVER (PARTITION BY Field1 ORDER BY DateStarted DESC) as seqnum
      FROM t
     ) t
group by Field1

This uses conditional aggregation to get the difference.

answered Apr 15, 2013 at 16:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Compare two dates from columns SQL

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related