Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with Pivot, please!
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

robwork
Starting Member

USA
3 Posts

Posted - 08/15/2013 :  16:10:17  Show Profile  Reply with Quote
I am trying to pivot this information. think i am close but cant quite get it worked out. something simple?

data looks like this:

BegDate EndDate EmpDivisionName EmpGroupName EmpTeamName Employee1 CODE hrs
7/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee1 *FLEX OFF AWARD 2
7/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee2 *FLEX OFF CODES 2
7/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee2 *FLEX OFF CODES 2
7/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee3 *FLEX OFF PAID 2
7/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee3 *FLEX.ON>Flex Off Paid (Empowe 2

want it to be:
BegDate EndDate EmpDivisionName EmpGroupName EmpTeamName Employee1 *FLEX OFF AWARD *FLEX OFF PAID (etc) with hrs summed up underneath each code


declare @startpass datetime
declare @endpass datetime
set @startpass = DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) -- use for previous month's startdate
set @endpass = DATEADD(mm, DATEDIFF(m,0,GETDATE()),-1) -- use for previous month's enddate
--set @startpass = '03/08/2013' -- get other month's startdate
--set @endpass = '03/08/2013' -- get other month's enddate

declare @startdate datetime
declare @enddate datetime
set @startdate = @startpass
set @enddate = @endpass

;WITH wadb as (
SELECT DISTINCT
PP_NUMBER EmpID,
RIGHT(LTRIM(RTRIM(Lucent_Log_new)),7) LucID,
CCDM.dbo.ProperCase(LAST_NAME)+', '+CCDM.dbo.ProperCase(FIRST_NAME) Employee,
EmpTeamID,
EmpTeamName,
EmpGroupID,
EmpGroupName,
EmpDivisionID,
EmpDivisionName
FROM CCDM.dbo.EmployeeArchive ea

INNER JOIN (
SELECT MAX(ARCHIVE_DT) ARCHIVE_DT
FROM CCDM.dbo.EmployeeArchive
WHERE ARCHIVE_DT between @enddate and DATEADD(s,-1,@enddate+1)
) ad
ON ea.ARCHIVE_DT = ad.ARCHIVE_DT

WHERE TERM_DATE_DATE9 is null
and [Is Lead?] = 'No'
and IS_MANAGER = 0
and PP_NUMBER like '[A-Z]%'
and UPPER(EmpGroupName) not like ('%ADMIN%')
and UPPER(EmpTeamName) not like '%ADMIN%'
and LTRIM(RTRIM(Lucent_Log_new)) != ''
and Lucent_Log_new is not null

)

, sched as(
SELECT EMP_ID
,SCHED
,COMP
FROM (
SELECT RIGHT(LTRIM(RTRIM(EMP_ID)),7) EMP_ID,
SUM(SCHEDULED) SCHED,
SUM(COMPLIANCE) COMP
FROM CCDM.dbo.adhere
WHERE NOM_DATE between @startdate and @enddate


GROUP BY RIGHT(LTRIM(RTRIM(EMP_ID)),7)
) a
)

, seg as (

select RIGHT(LTRIM(RTRIM(EMP_ID)),7)EMP_ID
,CODE
,MI
from
CCDM.dbo.FlashSched

where NOM_DATE between @startdate and @enddate )
--------------- end of CTE ------------------------

SELECT @startdate BegDate
,@enddate EndDate
,wadb.EmpDivisionName
,wadb.EmpGroupName
,wadb.EmpTeamName
,wadb.Employee
,Cast(ISNULL(['*FLEX'],0) as decimal(18,2)) FLEX
,Cast(ISNULL([*FLEX OFF],0) as decimal(18,2)) FLEXOFF
,Cast(ISNULL([*FLEX OFF ABS],0) as decimal(18,2)) FLEXOFFABS
--,Cast(ISNULL([FLEX OFF FHO],0) as decimal(18,2)) FLEXOFFFHO
--,Cast(ISNULL([FLEX OFF PER],0) as decimal(18,2)) FLEXOFFPER
--,Cast(ISNULL([FLEX OFF VAC],0) as decimal(18,2)) FLEXOFFVAC
--,Cast(ISNULL([FLEX OFF PAID],0) as decimal(18,2)) FLEXOFFPAID
--,Cast(ISNULL([FLEX OFF UNPAID],0) as decimal(18,2)) FLEXOFFUNPAID
--,Cast(ISNULL([FLEX ON],0) as decimal(18,2)) FLEXON
,cast(cast(sum(seg.MI) as decimal (10,2))/60 as decimal (10,2)) hrs

from wadb
left outer join
seg
ON seg.EMP_ID = wadb.LucID

--where CODE in ('*UNPLANNED ABSENCES','*FLEX OFF CODES')
where CODE in (
'*FLEX'
,'*FLEX OFF'
,'*FLEX OFF ABS'
,'*FLEX OFF AWARD'
,'*FLEX OFF CODES'
,'*FLEX OFF FHOFF'
,'*FLEX OFF PAID'
,'*FLEX OFF PERSONAL DAY'
,'*FLEX OFF UNPAID'
,'*FLEX OFF VAC'
,'*FLEX.OFF PAID>Flex On (Empowe'
,'*FLEX.ON>Flex Off Paid (Empowe'
)

PIVOT (
SUM(hrs)
FOR CODE IN
'*FLEX'
,'*FLEX OFF'
,'*FLEX OFF ABS'
,'*FLEX OFF AWARD'
,'*FLEX OFF CODES'
,'*FLEX OFF FHOFF'
,'*FLEX OFF PAID'
,'*FLEX OFF PERSONAL DAY'
,'*FLEX OFF UNPAID'
,'*FLEX OFF VAC'
,'*FLEX.OFF PAID>Flex On (Empowe'
,'*FLEX.ON>Flex Off Paid (Empowe'
) pvt

GO

bandi
Flowing Fount of Yak Knowledge

India
2241 Posts

Posted - 08/16/2013 :  07:20:01  Show Profile  Reply with Quote
You do not required to write each column name in PIVOT... Use Dynamic PIVOT query...
[link]http://stackoverflow.com/questions/7882466/sql-pivot-data-with-dynamic-list-of-columns[/link]
[link]http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/[/link]

Get list of Columns and then write dynamic query for SUM(hrs) for each code. Finally exec that query...


--
Chandu

Edited by - bandi on 08/16/2013 07:20:20
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000