Help with Pivot, please! - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with Pivot, please!

Author

Topic

robwork
Starting Member

3 Posts

Posted - 2013-08-15 : 16:10:17

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-16 : 07:20:01

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources