Hi

I think you need some way to group the dates into quarters.

I'll let someone else suggest the best method as I don't deal with dates frequently and wouldn't want to put wrong.

Once that is done you should have no problems.

This is a good article by Jeff Moden on pivots http://www.sqlservercentral.com/articles/T-SQL/63681/

Here is a method using NTILE to number the quarters.

NOTE: This assumes that you have complete data for the year. (2 dates per quarter)

;with numberQuarters AS (

select iSchoolYearCode

,NTILE(4) OVER (Partition By iSchoolYearCode ORDER BY dtCalendarDay ) QuarterNum

,dtCalendarDay

,iCalendarDatCategoryID

,vcCalendarCategoryCode

from #temp1

where iCalendarDatCategoryID in (15, 89)

)

select iSchoolYearCode,

MAX(CASE WHEN iCalendarDatCategoryID = 15 THEN dtCalendarDay ELSE NULL END) AS [Quarter Start Day],

MAX(CASE WHEN iCalendarDatCategoryID = 89 THEN dtCalendarDay ELSE NULL END) AS [Quarter Grade Closing]

from numberQuarters

group by iSchoolYearCode, QuarterNum