-4

I want to count the number of days for each ID but the days are overlapping. 

ID  STARTDATE   ENDDATE
1   19/12/2012  29/01/2013
1   30/05/2013  14/07/2013
1   15/02/2013  12/03/2013
1   13/03/2013  18/03/2013
1   19/03/2013  26/03/2013
1   27/01/2013  07/04/2013
1   08/04/2013  09/04/2013
2   08/04/2013  14/07/2013
2   30/05/2013  12/07/2013
3   08/04/2013  10/04/2013
3   23/05/2013  30/06/2013
Andrejs Cainikovs
  • 27,428
  • 2
  • 75
  • 95
user3352629
  • 9
  • 1
  • 3
  • Please provide a clear example of what you would expect to see as a result. Also, what have you tried so far? – PinnyM Feb 25 '14 at 18:56
  • Welcome to SO. What have you tried? This site is for problem solving, not for ready copy-paste solutions from scratch. – Andrejs Cainikovs Feb 25 '14 at 18:56
  • Can you explain your problem more? Why can't you just use `DateDiff()`? – ovaltein Feb 25 '14 at 18:57
  • By your explanation it seems the you do not want to recount overlapping days. So do you want to merge two or more overlapping date ranges into one and then take a date difference? – Dipendu Paul Feb 25 '14 at 19:09

1 Answers1

0
SELECT SUM(DATEDIFF( d,STARTDATE,ENDDATE)) AS [Days],ID
FROM TABLE
GROUP BY ID

OR TO GET THE NUMBER OF DAYS FROM THE MIN/MAX DATE RANGE:

SELECT D.ID, SUM(DATEDIFF( d,D.STARTDATE,D.ENDDATE)) AS [Days]
FROM (
SELECT ID, MIN(STARTDATE) AS [STARTDATE], MAX(ENDDATE) AS [ENDATE]
FROM TABLE
GROUP BY ID , MIN(STARTDATE), MAX(ENDDATE) 
) AS D
ORDER BY D.ID
T McKeown
  • 12,971
  • 1
  • 25
  • 32