Calculate start and end dates

All replies

• 

  

  0

  Sign in to vote

  Looks like your grouping values by month as well.

  SELECT aID, DatePart(month, aDate) AS [aMonth], MIN(aDate), MAX(aDate)
   FROM   tbl
   GROUP  BY aID, DatePart(month, aDate)

  Order By aID, [aMonth]

  
  
  

  • Edited by carbonc Sunday, January 20, 2013 9:04 PM

  Sunday, January 20, 2013 8:58 PM
• 

  

  0

  Sign in to vote

  I appologise for giving the impression that the dates are grouped by the month. It is possible that the same ID has more than 1 lot of date sequencial dates in the same month.

  ---

  Jonathan

  Sunday, January 20, 2013 9:03 PM
• 

  

  0

  Sign in to vote

  This is a gaps and islands problem. More precisely, an islands problem.

  This can be solved by numbering the rows with row number, and then take the distance between the value and the numbering. Within each group, the difference is constant.

  ;WITH CTE AS (
     SELECT aID, aDate, convert(int, aDate) AS aDateNum
            row_number() OVER(PARTITION aID ORDER BY aDate) AS rowno
     FROM   tbl
  )
  SELECT aID, MIN(aDate), MAX(aDate)
  FROM   CTE
  GROUP  BY aID, rowno - aDateNum

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • Marked as answer by Kiwi Jonathan Sunday, January 20, 2013 9:14 PM

  Sunday, January 20, 2013 9:04 PM
• 

  

  0

  Sign in to vote

  That's supurb. Really do appreciate your time and expertise.

  ---

  Jonathan

  Sunday, January 20, 2013 9:14 PM
• 

  

  0

  Sign in to vote

  do 1 conversion to get the result.
  

  create table #test1
  (aid int, adate date)
  
  insert into #test1  values
  (1,'2013/01/21'),(1,'2013/01/22'),(1,'2013/01/23')    ,(1,'2013/01/26'),(1,'2013/01/29'),(1,'2013/02/4'),(1,'2013/02/9'),(1,'2013/02/15'),(2,'2013/02/13'),(2,'2013/02/11')    
  
  select * from #test1 order by aid,adate
  aid    adate
  1    2013-01-21
  1    2013-01-22
  1    2013-01-23
  1    2013-01-26
  1    2013-01-29
  1    2013-02-04
  1    2013-02-09
  1    2013-02-15
  2    2013-02-11
  2    2013-02-13
  
  with cte as (
  SELECT aID, adate,
  row_number() OVER(PARTITION by aID ORDER BY adate) AS acheck
    FROM   #test1
  ) select aid,min(adate) StartDate,max(adate) EndDate from cte
  group by aid,ceiling(acheck/2.0)
  order by aid,MIN(adate),MAX(adate)
  
  aid    StartDate    EndDate
  1    2013-01-21    2013-01-22
  1    2013-01-23    2013-01-26
  1    2013-01-29    2013-02-04
  1    2013-02-09    2013-02-15
  2    2013-02-11    2013-02-13

  Monday, January 21, 2013 3:28 AM
• 

  

  0

  Sign in to vote

  Sorry must be missing something obvious but could you clarify your answer. I tried to code the question and your answer as follows:

  USE tempdb;
  GO
  
  DROP TABLE tbl
  GO
  
  CREATE TABLE tbl
  (
  	aID		INT,
  	aDate	DATE
  )
  
  
  INSERT tbl (aID, aDate)
  VALUES  (1, '20130121'), 
  		(1, '20130122'),  
  		(1, '20130123'),
  		(1, '20130204'),
  		(2, '20130210'),
  		(2, '20130211')
  
  
  ;WITH CTE AS 
  (
      SELECT aID, 
  		   aDate, 
  		   CONVERT(INT, aDate) AS 'aDateNum', 
  		   ROW_NUMBER() OVER(PARTITION BY aID ORDER BY aDate) AS 'rowno'
      FROM   tbl
   )
   SELECT aID, MIN(aDate), MAX(aDate)
   FROM   CTE
   GROUP  BY aID, rowno - aDateNum
  
  

  I am getting the following error:

  Msg 529, Level 16, State 2, Line 18
  Explicit conversion from data type date to bigint is not allowed.

  I think the issue is with:

  CONVERT(INT, aDate) AS 'aDateNum'

  Could you please point out what I have got wrong.

  Thanks

  Monday, January 21, 2013 1:19 PM
• 

  

  0

  Sign in to vote

  Lesson 1:

  When you post this kind of question, you do yourself a service if you post:

  1) CREATE TABLE statement for you table.
  2) INSERT statements with sample data.
  3) The desired result given the sample.
  4) Which version of SQL Server you are using.

  This makes it possible to copy and paste into a query window to develop a tested solution.

  Without it, you get what you get.

  In this case, you should be able to get by a two-step conversion:

    convert(int, convert(date, aDate))

  An alternative is to use datediff against an anchor date to produce numbers for the dates:

    datediff(day, '20000101', aDate)

  ---

  Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

  • Proposed as answer by Naomi N Tuesday, January 22, 2013 3:08 AM

  Monday, January 21, 2013 2:05 PM
• 

  

  0

  Sign in to vote

  Apparently you can not convert date to int (this conversion is not allowed), but you can convert datetime to int. So, I changed the above to convert(int, convert(datetime, aDate)) and it worked.

  ---

  For every expert, there is an equal and opposite expert. - Becker's Law
  
  
  My blog

  Tuesday, January 22, 2013 3:10 AM