Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 9:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
Hello Everyone

I have one task

I need to do pivoting

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')


desired output


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.000
2012 2013-01-30 00:00:00.000 2013-04-08 00:00:00.000
2012 2013-04-09 00:00:00.000 2013-06-13 00:00:00.000
Post #1422157
Posted Wednesday, February 20, 2013 10:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:41 PM
Points: 541, Visits: 1,045
Why does this look like homework?
Post #1422174
Posted Wednesday, February 20, 2013 10:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
you can do this using CASE also..


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1422176
Posted Wednesday, February 20, 2013 10:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
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
Post #1422178
Posted Wednesday, February 20, 2013 10:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1422180
Posted Wednesday, February 20, 2013 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
kapil_kk (2/20/2013)
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


Kapil I already try this

Here what i got


iSchoolYearCode QuarterStartDay QuarterGradeClosing
2012 2012-08-30 00:00:00.000 1900-01-01 00:00:00.000
2012 1900-01-01 00:00:00.000 2012-11-07 00:00:00.000
2012 2012-11-08 00:00:00.000 1900-01-01 00:00:00.000
2012 1900-01-01 00:00:00.000 2013-01-25 00:00:00.000
2012 2013-01-30 00:00:00.000 1900-01-01 00:00:00.000
2012 1900-01-01 00:00:00.000 2013-04-08 00:00:00.000
2012 2013-04-09 00:00:00.000 1900-01-01 00:00:00.000
2012 1900-01-01 00:00:00.000 2013-06-13 00:00:00.000


but desired output Like as below

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.000
2012 2013-01-30 00:00:00.000 2013-04-08 00:00:00.000
2012 2013-04-09 00:00:00.000 2013-06-13 00:00:00.000
Post #1422183
Posted Wednesday, February 20, 2013 10:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
ok i didnt ran that query..

lemme try again



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1422202
Posted Wednesday, February 20, 2013 12:12 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:43 PM
Points: 982, Visits: 2,974
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

Post #1422240
Posted Wednesday, February 20, 2013 11:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
you 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 cte
Group By RowNumber, iSchoolYearCode



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1422404
Posted Wednesday, February 20, 2013 11:13 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1422406
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse