Find most recent record of consecutive dates

• robert.wiglesworth

  SSCommitted

  Points: 1790

  More actions

  February 23, 2015 at 11:25 am

  #315667

  I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.

  I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:

  FromDate ToDate Customer Number Contract Number

  02/01/2015 07/31/2015 2555 456

  01/15/2015 01/31/2015 2555 456

  04/01/2013 09/30/2015 2555 123

  03/13/2013 03/31/2013 2555 123

  From this table, I would like a query that would give me this result:

  01/15/2015 07/31/2015 2555 456

  03/13/2013 09/30/2015 2555 123

  There will likely be more than just 2 consecutive records per contract number.

  Thanks for any help!

• imex

  Ten Centuries

  Points: 1234

  More actions

  February 23, 2015 at 12:50 pm

  #1779391

  Try something like this:

  with CTE_RN as

  (

  select

  FromDate,

  ToDate,

  CustomerNumber,

  ContractNumber,

  ROW_NUMBER() OVER(PARTITION BY ContractNumber ORDER BY FromDate) as RN

  from MyTable

  )

  select * from CTE_RN

  where RN = 1

  Hope this helps.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• LinksUp

  SSCertifiable

  Points: 6526

  More actions

  February 23, 2015 at 1:18 pm

  #1779402

  robert.wiglesworth (2/23/2015)

  ---

  From this table, I would like a query that would give me this result:

  01/15/2015 07/31/2015 2555 456

  03/13/2013 09/30/2015 2555 123

  The solution provided by imex is on the right track. It just needs to go a little further to provide the requested output.

  declare @t table

  (

  FromDate Date

  ,ToDate Date

  ,Customer_Number int

  ,Contract_Number int

  )

  insert @t values

  ('02/01/2015','07/31/2015',2555, 456)

  ,('01/15/2015','01/31/2015',2555, 456)

  ,('04/01/2013','09/30/2015',2555, 123)

  ,('03/13/2013','03/31/2013',2555, 123)

  ,('02/01/2015','07/31/2015',2556, 456)

  ,('01/15/2015','01/31/2015',2556, 456)

  ,('04/01/2013','09/30/2015',2556, 123)

  ,('03/13/2013','03/31/2013',2556, 123)

  ;with cte1 as

  (

  Select FromDate, ToDate, Customer_Number, Contract_Number,

  Row_Number() Over(Partition By Customer_number, Contract_Number Order by FromDate) RowNum

  from @t

  ),

  cte2 as

  (

  Select FromDate, ToDate, Customer_Number, Contract_Number,

  Row_Number() Over(Partition By Customer_number, Contract_Number Order by ToDate desc) RowNum

  from @t

  )

  select c1.fromdate, c2.todate, c1.customer_number, c1.contract_number

  from cte1 c1

  inner join cte2 c2 on c2.Customer_Number = c1.Customer_Number

  and c2.Contract_Number = c1.Contract_Number

  where c1.RowNum = 1 and c2.RowNum = 1

  The partition by clause needs to include the customer_number as well as the contract_number or it will fail when you have more than one customer number.

  The second cte reorders the original table and gets the last row for the given customer and contract.

  The inner join with the WHEN matches up correct rows and displays them.

  __________________________________________________________________________________________________________
  How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  February 23, 2015 at 4:31 pm

  #1779446

  LinksUp (2/23/2015)

  ---

  robert.wiglesworth (2/23/2015)

  ---

  From this table, I would like a query that would give me this result:

  01/15/2015 07/31/2015 2555 456

  03/13/2013 09/30/2015 2555 123

  The solution provided by imex is on the right track. It just needs to go a little further to provide the requested output.

  declare @t table

  (

  FromDate Date

  ,ToDate Date

  ,Customer_Number int

  ,Contract_Number int

  )

  insert @t values

  ('02/01/2015','07/31/2015',2555, 456)

  ,('01/15/2015','01/31/2015',2555, 456)

  ,('04/01/2013','09/30/2015',2555, 123)

  ,('03/13/2013','03/31/2013',2555, 123)

  ,('02/01/2015','07/31/2015',2556, 456)

  ,('01/15/2015','01/31/2015',2556, 456)

  ,('04/01/2013','09/30/2015',2556, 123)

  ,('03/13/2013','03/31/2013',2556, 123)

  ;with cte1 as

  (

  Select FromDate, ToDate, Customer_Number, Contract_Number,

  Row_Number() Over(Partition By Customer_number, Contract_Number Order by FromDate) RowNum

  from @t

  ),

  cte2 as

  (

  Select FromDate, ToDate, Customer_Number, Contract_Number,

  Row_Number() Over(Partition By Customer_number, Contract_Number Order by ToDate desc) RowNum

  from @t

  )

  select c1.fromdate, c2.todate, c1.customer_number, c1.contract_number

  from cte1 c1

  inner join cte2 c2 on c2.Customer_Number = c1.Customer_Number

  and c2.Contract_Number = c1.Contract_Number

  where c1.RowNum = 1 and c2.RowNum = 1

  The partition by clause needs to include the customer_number as well as the contract_number or it will fail when you have more than one customer number.

  The second cte reorders the original table and gets the last row for the given customer and contract.

  The inner join with the WHEN matches up correct rows and displays them.

  Isn't that just a mighty complicated way of saying:

  select min(fromdate) as fromdate, max(todate) as todate, Customer_Number, Contract_Number

  from @t

  group by Customer_Number, Contract_Number

  order by Customer_Number, Contract_Number;

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]