CODEWITHSUNDEEP
sqlsql-serversql-server-2008sequencegaps-and-islands
Ricardo Castro
Ricardo Castro

Reputation: 157

SQL Server : aggregate by sequenced number

I need some help to aggregate some data. Imagine I have the following table:

number  Name    From Date   To Date     Sequency No_
----------------------------------------------------
123440  MJCL    03/12/2014  02/09/2015  1
123440  MJCL    07/09/2015  06/03/2016  1
123440  MJCL    07/03/2016  06/09/2016  2
123440  MJCL    07/09/2016  06/03/2017  3
123440  MJCL    07/03/2017  31/12/9999  4
123442  GJSVF   15/12/2014  14/06/2015  1
123442  GJSVF   15/06/2015  14/12/2015  2
123442  GJSVF   15/12/2015  14/06/2016  3
123442  GJSVF   27/03/2017  26/03/2018  1

I need a SQL query to output the following:

number  Name    From Date   To Date
--------------------------------------
123440  MJCL    03/12/2014  02/09/2015
123440  MJCL    07/09/2015  31/12/9999
123442  GJSVF   15/12/2014  14/06/2016
123442  GJSVF   27/03/2017  26/03/2018

Basically what I need is to join the rows by [Sequency No_] and return the [From Date] of [Sequency No_] = 1 and the [To Date] from last [Sequency No_] of the sequence. Any ideas?

Note that the database is SQL Server 2008.

Upvotes: 0

Views: 88

Answers (2)

DeadCat
DeadCat

Reputation: 192

Try this..this will work for you :)

SELECT number,Name,MAX(From_Date) From_Date,MAX(To_Date) To_Date
FROM   Trans_Tab
GROUP BY number,Name

UNION ALL

SELECT number,Name,MIN(From_Date) From_Date,MIN(To_Date) To_Date
FROM   Trans_Tab
GROUP BY number,Name
ORDER BY number,Name

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

This is called as gaps and islands problem.

Here is one trick using Row_Number window function

;WITH cte
     AS (SELECT *,
                Grp = Row_number()OVER(partition BY number ORDER BY [From Date],[Sequency No_]) - [Sequency No_]
         FROM   Yourtable)
SELECT number,
       NAME,
       [From Date] = Min([From Date]),
       [To Date] = Max([To Date])
FROM   cte
GROUP  BY number,
          NAME,
          Grp

Upvotes: 1

Related Questions