Sql query to find date period between multiple rows

I have a table with three columns (City_Code | Start_Date | End_Date).

Suppose i have the following data:

New_York|01/01/1985|01/01/1987
Paris|02/01/1987|01/01/1990 
San F开发者_运维问答rancisco|02/01/1990|04/01/1990
Paris|05/01/1990|08/01/1990 
New_York|09/01/1990|11/01/1990 
New_York|12/01/1990|19/01/1990 
New_York|20/01/1990|28/01/1990 

I would like to get the date period for which someone lived in the last city of his residence. In this example that is New_York(09/01/1990-28/01/1990) using only sql. I can get this period by manipulating the data with java , but is it possible to do it with plain sql?

Thanks in advance

You can grab the first and last date of residence by city using this:

 SELECT TOP 1 City_Code, MIN(Start_Date), Max(End_Date)
 FROM Table
 GROUP BY City_Code
 ORDER BY Max(End_Date) desc

but, the problem is that the start date will be the first date of residence in the city in question.

For 10g you don't have the option of SELECT TOP n so you must be a little creative.

WITH last_period
AS
(SELECT city, moved_in, moved_out, NVL(moved_in-LEAD(moved_out, 1) OVER (ORDER BY city), 0) AS lead
FROM periods
WHERE city = (SELECT city FROM periods WHERE moved_out = (SELECT MAX(moved_out) FROM periods)))
SELECT city, MIN(moved_in) AS moved_in, MAX(moved_out) AS moved_out
FROM last_period
WHERE lead >= 0
GROUP BY city;

This works for the example dataset that you have given. It could stand some optimisation for a large dataset but gives you a working example, tested on Oracle 10g.

If it's MySQL, you can easily use

TIME_TO_SEC(TIMEDIFF(end_date, start_date)) AS `diff_in_secs`

Having time difference in seconds you go any further.

On SQL Server, couldn't you use:

SELECT TOP 1 City_Code, Start_Date + "-" + End_Date 
FROM MyTable
ORDER BY enddate DESC

That would get the date period and city with the latest end date. This is assuming you are trying to just find the city where the person most recently lived, formatted with a dash.

Given that this is Oracle, you can simply subtract the end date and start date to get the number of days in between.

Select City_Code, (End_Date - Start_Date) Days
From MyTable
Where Start_Date =  (
                        Select Max( T1.Start_ Date )
                        From MyTable As T1
                        )

If you are using SQL Server you can use the DateDiff() function

DATEDIFF ( datepart , startdate , enddate )

http://msdn.microsoft.com/en-us/library/ms189794.aspx

EDIT

I don't know Oracle but I did find this article

SELECT 
  MAX(t.duration) 
FROM (
       SELECT 
          (End_Date - Start_Date) duration 
       From 
           Table
) as t

I hope this will work.

If you want to calculate only the last period length for the last city of residence, then it's probably something like this:

SELECT TOP 1
  City_Code,
  End_Date - Start_Date AS Days
FROM atable
ORDER BY Start_Date DESC

But if you mean to include all the periods the person has ever lived in a city that happens to be their last city of residence, then it's a bit more complicated, but not too much:

SELECT TOP 1
  City_Code,
  SUM(End_Date - Start_Date) AS Days
FROM atable
GROUP BY City_Code
ORDER BY MAX(Start_Date) DESC

But the above solution most probably returns the last city information only after it calculates the data for all cities. Do we need that? Not necessarily, so maybe we should use another approach. Maybe like this:

SELECT
  City_Code,
  SUM(End_Date - Start_Date) AS Days
FROM atable
WHERE City_Code = (SELECT TOP 1 City_Code FROM atable ORDER BY Start_Date DESC)
GROUP BY City_Code

i'm short on time - but this feels like you could use the window function LAG to compare to the previous row and retain the appropriate begin date from that row when the city changes, and dont change it when the city is the same - this should correctly preserve the range.