March 7, 2007 at 5:42 am
I'm curious about whether anyone can show a good way to deal with the following scenario: Start with a TABLE variable, defined as follows:
DECLARE @MyTable (StartDate DATETIME, EndDate DATETIME)
This table is populated with several rows of data, all within a specific "window" of time (often a few months). The periods of time these rows cover may overlap in every conceivable manner. In the end, I want the following:
SELECT @result = SUM(DATEDIFF(dd, StartDate, EndDate)) FROM @MyTable
BUT, I don't want to count overlapping periods more than once. Basically, I want the number of days during that window that any of the rows cover.
Any suggestions?
March 7, 2007 at 6:23 am
Maybe:
SELECT @result = DATEDIFF(day, MIN(StartDATE), MAX(EndDate))
FROM @MyTable
March 7, 2007 at 7:13 am
Create a calendar table containing one [date] per day indexed on [Date]
Join this table to the one specified (where [date] between Start and End dates)
GROUP BY [date] HAVING COUNT(*) = 1
COUNT the result of the query
Far away is close at hand in the images of elsewhere.
Anon.
March 7, 2007 at 7:17 am
p.s.
SELECT @result = COUNT(*)
FROM (SELECT c.[Date]
FROM @MyTable a
INNER JOIN [Calendar] c
ON c.[Date] >= a.StartDate AND c.[Date] <= a.EndDate
GROUP BY c.[Date]
HAVING COUNT(*) = 1) x
Far away is close at hand in the images of elsewhere.
Anon.
March 8, 2007 at 6:38 am
Ken, that doesn't account for gaps in the set. And David, thanks, that's what I was interested in.
J
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply