Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Query Help
Add to briefcase

Query Help Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
yogi123
Posted Wednesday, February 20, 2013 9:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:47 PM
Points: 83, Visits: 244
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
Erin Ramsay
Posted Wednesday, February 20, 2013 10:04 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:14 AM
Points: 420, Visits: 774
Why does this look like homework?
Post #1422174
kapil_kk
Posted Wednesday, February 20, 2013 10:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151
you can do this using CASE also..
Post #1422176
yogi123
Posted Wednesday, February 20, 2013 10:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:47 PM
Points: 83, Visits: 244
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
kapil_kk
Posted Wednesday, February 20, 2013 10:12 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151
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
Post #1422180
yogi123
Posted Wednesday, February 20, 2013 10:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 10, 2013 2:47 PM
Points: 83, Visits: 244
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
kapil_kk
Posted Wednesday, February 20, 2013 10:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151
ok i didnt ran that query..

lemme try again
Post #1422202
mickyT
Posted Wednesday, February 20, 2013 12:12 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 297, Visits: 1,115
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
kapil_kk
Posted Wednesday, February 20, 2013 11:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151
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
Post #1422404
kapil_kk
Posted Wednesday, February 20, 2013 11:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:16 PM
Points: 1,061, Visits: 1,151
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 :)
Post #1422406
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.