Sql query to search for a date period between multiple rows

Question

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 Francisco|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 receive a date during which someone lived in the last city of his residence. In this example, this is New_York (09/01 / 1990-28 / 01/1990), using only sql. I can get this period by manipulating data using java, but is it possible to do this with simple sql?

Thanks in advance

Answer 1

You can get the first and last date of stay in the 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

, , .

Answer 2

10 SELECT TOP n, .

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;

, . , , Oracle 10g.

Answer 3

MySQL,

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

, .

Answer 4

SQL Server, DateDiff()

DATEDIFF ( datepart , startdate , enddate )

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

Oracle,

Answer 5

SQL Server :

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

. , , , .

Answer 6

, Oracle, , .

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

Answer 7

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

, .

Answer 8

, , , :

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

, - , , , :

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

, , , . ? , , , . , :

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

Answer 9

- , LAG , - .