Hila Gal Hila Gal - 2 months ago 7
SQL Question

How to use Query with variables

I have a Table with Two columns - Case (string) and DateTime.
I want to use a query that will show Case and number of minutes between last case, for every case.

For example, my table:

Case Date
1/15548569 01/01/2015 10-00-00
1/15548570 01/01/2015 10-15-00
1/15548571 01/01/2015 10-28-00


Query result:

1/15548569 0 Minutes
1/15548570 15 Minutes
1/15548571 13 Minutes


Is it possible?

Answer

Here's one option with lag:

select [case],
  [date],
  cast([date]-lag([date]) over (order by [date]) as time) as diff
from yourtable