InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 20:20:31 GMT20http://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspxtry this too....;WITH mycte AS ( SELECT iCalendarDatCategoryID, vcCalendarCategoryCode, iSchoolYearCode,dtCalendarDay, ROW_NUMBER() OVER(PARTITION BY iCalendarDatCategoryID ORDER BY dtCalendarDay) AS rn FROM #Temp1 ) SELECT iSchoolYearCode, min(dtCalendarDay) as [Quarter Start Day], max(dtCalendarDay) as [Quarter Grade Closing] FROM myctegroup by iSchoolYearCode, rn Order by min(dtCalendarDay)it will also work :)Wed, 20 Feb 2013 23:13:06 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspxyou can try this one now...;with cte as( Select iSchoolYearCode ,dtCalendarDay ,iCalendarDatCategoryID ,vcCalendarCategoryCode ,ROW_NUMBER() OVER(Partition By iSchoolYearCode, iCalendarDatCategoryID Order by dtCalendarDay) RowNumber From #Temp1)Select iSchoolYearCode ,Max(Case When vcCalendarCategoryCode = 'Quarter Start Day' Then dtCalendarDay End) As [Quarter Start] ,Max(Case When vcCalendarCategoryCode = 'Quarter Grade Closing' Then dtCalendarDay End) As [Quarter end]From cteGroup By RowNumber, iSchoolYearCodeWed, 20 Feb 2013 23:05:28 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspxHiI 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 [url]http://www.sqlservercentral.com/articles/T-SQL/63681/[/url]Here is a method using NTILE to number the quarters. [b]NOTE:[/b] This assumes that you have complete data for the year. (2 dates per quarter)[code="sql"];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 numberQuartersgroup by iSchoolYearCode, QuarterNum[/code]Wed, 20 Feb 2013 12:12:10 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspxok i didnt ran that query..lemme try againWed, 20 Feb 2013 10:35:40 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspx[quote][b]kapil_kk (2/20/2013)[/b][hr]try this.. hope it will workSELECT iSchoolYearCode, CASE WHEN vcCalendarCategoryCode = 'Quarter Start Day' THEN dtCalendarDay ELSE 0 END AS QuarterStartDay, CASE WHEN vcCalendarCategoryCode = 'Quarter Grade Closing' THEN dtCalendarDay ELSE 0 END AS QuarterGradeClosing FROM #Temp1[/quote]Kapil I already try thisHere what i got [code="plain"]iSchoolYearCode QuarterStartDay QuarterGradeClosing2012 2012-08-30 00:00:00.000 1900-01-01 00:00:00.0002012 1900-01-01 00:00:00.000 2012-11-07 00:00:00.0002012 2012-11-08 00:00:00.000 1900-01-01 00:00:00.0002012 1900-01-01 00:00:00.000 2013-01-25 00:00:00.0002012 2013-01-30 00:00:00.000 1900-01-01 00:00:00.0002012 1900-01-01 00:00:00.000 2013-04-08 00:00:00.0002012 2013-04-09 00:00:00.000 1900-01-01 00:00:00.0002012 1900-01-01 00:00:00.000 2013-06-13 00:00:00.000[/code]but desired output Like as below[code="plain"]iSchoolYearCode Quarter Start Day Quarter Grade Closing 2012 2012-08-30 00:00:00.000 2012-11-07 00:00:00.000 2012 2012-11-08 00:00:00.000 2013-01-25 00:00:00.0002012 2013-01-30 00:00:00.000 2013-04-08 00:00:00.0002012 2013-04-09 00:00:00.000 2013-06-13 00:00:00.000 [/code]Wed, 20 Feb 2013 10:15:04 GMTyogi123http://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspxtry this.. hope it will workSELECT iSchoolYearCode, CASE WHEN vcCalendarCategoryCode = 'Quarter Start Day' THEN dtCalendarDay ELSE 0 END AS QuarterStartDay, CASE WHEN vcCalendarCategoryCode = 'Quarter Grade Closing' THEN dtCalendarDay ELSE 0 END AS QuarterGradeClosing FROM #Temp1Wed, 20 Feb 2013 10:12:00 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspx[quote][b]kapil_kk (2/20/2013)[/b][hr]you can do this using CASE also..[/quote]I try this[code="sql"]select iSchoolYearCode, CASE WHEN iCalendarDatCategoryID = 15 THEN dtCalendarDay ELSE '' End, CASE WHEN iCalendarDatCategoryID = 89 THEN dtCalendarDay ELSE '' Endfrom #Temp1[/code]but didn't workWed, 20 Feb 2013 10:08:19 GMTyogi123http://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspxyou can do this using CASE also..Wed, 20 Feb 2013 10:05:46 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspxWhy does this look like homework?Wed, 20 Feb 2013 10:04:55 GMTErin Ramsayhttp://www.sqlservercentral.com/Forums/Topic1422157-391-1.aspxHello EveryoneI have one task I need to do pivoting [code="sql"]create table #Temp1(iSchoolYearCode int,dtCalendarDay datetime,iCalendarDatCategoryID int,vcCalendarCategoryCode varchar(25))insert into #Temp1 values (2012,'2012-08-30 00:00:00.000',15,'Quarter Start Day')insert into #Temp1 values (2012,'2012-11-07 00:00:00.000',89,'Quarter Grade Closing')insert into #Temp1 values (2012,'2012-11-08 00:00:00.000',15,'Quarter Start Day')insert into #Temp1 values (2012,'2013-01-25 00:00:00.000',89,'Quarter Grade Closing')insert into #Temp1 values (2012,'2013-01-30 00:00:00.000',15,'Quarter Start Day')insert into #Temp1 values (2012,'2013-04-08 00:00:00.000',89,'Quarter Grade Closing')insert into #Temp1 values (2012,'2013-04-09 00:00:00.000',15,'Quarter Start Day')insert into #Temp1 values (2012,'2013-06-13 00:00:00.000',89,'Quarter Grade Closing')[/code]desired output [code="plain"]iSchoolYearCode Quarter Start Day Quarter Grade Closing 2012 2012-08-30 00:00:00.000 2012-11-07 00:00:00.000 2012 2012-11-08 00:00:00.000 2013-01-25 00:00:00.0002012 2013-01-30 00:00:00.000 2013-04-08 00:00:00.0002012 2013-04-09 00:00:00.000 2013-06-13 00:00:00.000 [/code]Wed, 20 Feb 2013 09:41:53 GMTyogi123