I am focusing on a question that must possess some data rows combined according to date ranges. These rows are copied in most the information values, except the date ranges are split. For instance the table data may seem like
StudentID StartDate EndDate Field1 Field2 1 9/3/2007 10/20/2007 3 True 1 10/21/2007 6/12/2008 3 True 2 10/10/2007 3/20/2008 4 False 3 9/3/2007 11/3/2007 8 True 3 12/15/2007 6/12/2008 8 True
Caused by the query must have the split date ranges combined. The query should mix date ranges having a gap of just eventually. If there's greater than a eventually gap, then your rows should not be combined. The rows that do not possess a split time frame should come through unchanged. The end result would seem like
StudentID StartDate EndDate Field1 Field2 1 9/3/2007 6/12/2008 3 True 2 10/10/2007 3/20/2008 4 False 3 9/3/2007 11/3/2007 8 True 3 12/15/2007 6/12/2008 8 True
What will be the Choose statement with this query?
The next code should work. I have designed a couple of presumptions the following: you will find no overlaps of date ranges, you will find no NULL values most of the fields, and also the start date for any given row is definitely under the finish date. In case your data does not fit these criteria, you will need to adjust this process, however it should point you within the right direction.
You should use subqueries rather than the sights, but that may be cumbersome and so i used the sights to create the code clearer.
CREATE VIEW dbo.StudentStartDates AS SELECT S.StudentID, S.StartDate, S.Field1, S.Field2 FROM dbo.Students S LEFT OUTER JOIN dbo.Students PREV ON PREV.StudentID = S.StudentID AND PREV.Field1 = S.Field1 AND PREV.Field2 = S.Field2 AND PREV.EndDate = DATEADD(dy, -1, S.StartDate) WHERE PREV.StudentID IS NULL GO CREATE VIEW dbo.StudentEndDates AS SELECT S.StudentID, S.EndDate, S.Field1, S.Field2 FROM dbo.Students S LEFT OUTER JOIN dbo.Students NEXT ON NEXT.StudentID = S.StudentID AND NEXT.Field1 = S.Field1 AND NEXT.Field2 = S.Field2 AND NEXT.StartDate = DATEADD(dy, 1, S.EndDate) WHERE NEXT.StudentID IS NULL GO SELECT SD.StudentID, SD.StartDate, ED.EndDate, SD.Field1, SD.Field2 FROM dbo.StudentStartDates SD INNER JOIN dbo.StudentEndDates ED ON ED.StudentID = SD.StudentID AND ED.Field1 = SD.Field1 AND ED.Field2 = SD.Field2 AND ED.EndDate > SD.StartDate AND NOT EXISTS (SELECT * FROM dbo.StudentEndDates ED2 WHERE ED2.StudentID = SD.StudentID AND ED2.Field1 = SD.Field1 AND ED2.Field2 = SD.Field2 AND ED2.EndDate < ED.EndDate AND ED2.EndDate > SD.StartDate) GO
In my opinion, I must mix the ranges in publish-processing (not in SQL however in my script). I am unsure that the SQL can perform this, particularly since you can don't know exactly the number of date ranges have to be chained in almost any particular situation. If you can do this though, I'd like to know too.
EDIT: My response is presuming you have several selection of dates per student, not only a start as well as an finish. Should you have only the main one time frame without any gaps, then your other pointed out solutions are what you want.
Choose StudentID, min(startdate) as startdate, max(enddate), field1, field2 from tablex group by StudentID, field1, field2
That will yield the result presuming the wasn't a gap between on student's time range.
select StudentID, min(StartDate) StartDate, max(EndDate) EndDate, Field1, Field2 from table group by StudentID, Field1, Field2
When the min()/max() solutions are not adequate enough (e.g. when the dates aren't contiguous and you need to group separate date ranges individually), I question if something using Oracle's Begin With and fasten BY clauses works. Which, obviously, wouldn't focus on every database.