February 20, 2013 at 9:41 am
Hello Everyone
I have one task
I need to do pivoting
create table #Temp1
(
iSchoolYearCode int,
dtCalendarDay datetime,
iCalendarDatCategoryID int,
vcCalendarCategoryCodevarchar(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')
desired output
iSchoolYearCodeQuarter Start DayQuarter Grade Closing
20122012-08-30 00:00:00.0002012-11-07 00:00:00.000
20122012-11-08 00:00:00.0002013-01-25 00:00:00.000
20122013-01-30 00:00:00.0002013-04-08 00:00:00.000
20122013-04-09 00:00:00.0002013-06-13 00:00:00.000
February 20, 2013 at 10:04 am
Why does this look like homework?
February 20, 2013 at 10:05 am
you can do this using CASE also..
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 20, 2013 at 10:08 am
kapil_kk (2/20/2013)
you can do this using CASE also..
I try this
select iSchoolYearCode,
CASE WHEN iCalendarDatCategoryID = 15 THEN dtCalendarDay
ELSE '' End,
CASE WHEN iCalendarDatCategoryID = 89 THEN dtCalendarDay
ELSE '' End
from #Temp1
but didn't work
February 20, 2013 at 10:12 am
try this.. hope it will work
SELECT
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
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 20, 2013 at 10:15 am
kapil_kk (2/20/2013)
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
Kapil I already try this
Here what i got
iSchoolYearCodeQuarterStartDayQuarterGradeClosing
20122012-08-30 00:00:00.0001900-01-01 00:00:00.000
20121900-01-01 00:00:00.0002012-11-07 00:00:00.000
20122012-11-08 00:00:00.0001900-01-01 00:00:00.000
20121900-01-01 00:00:00.0002013-01-25 00:00:00.000
20122013-01-30 00:00:00.0001900-01-01 00:00:00.000
20121900-01-01 00:00:00.0002013-04-08 00:00:00.000
20122013-04-09 00:00:00.0001900-01-01 00:00:00.000
20121900-01-01 00:00:00.0002013-06-13 00:00:00.000
but desired output Like as below
iSchoolYearCodeQuarter Start DayQuarter Grade Closing
20122012-08-30 00:00:00.0002012-11-07 00:00:00.000
20122012-11-08 00:00:00.0002013-01-25 00:00:00.000
20122013-01-30 00:00:00.0002013-04-08 00:00:00.000
20122013-04-09 00:00:00.0002013-06-13 00:00:00.000
February 20, 2013 at 10:35 am
ok i didnt ran that query..
lemme try again
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 20, 2013 at 12:12 pm
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
February 20, 2013 at 11:05 pm
you can try this one now...
;with cte as
(
SelectiSchoolYearCode
,dtCalendarDay
,iCalendarDatCategoryID
,vcCalendarCategoryCode
,ROW_NUMBER() OVER(Partition By iSchoolYearCode, iCalendarDatCategoryID Order by dtCalendarDay) RowNumber
From#Temp1
)
SelectiSchoolYearCode
,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]
Fromcte
Group By RowNumber, iSchoolYearCode
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 20, 2013 at 11:13 pm
try 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 mycte
group by iSchoolYearCode, rn
Order by min(dtCalendarDay)
it will also work 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply