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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 New to Pivot - Please help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

robwork
Starting Member

USA
3 Posts
Posted - 08/14/2013 :  14:26:37  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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts
Posted - 08/16/2013 :  13:43:55  Show Profile  Reply with Quote
the pivot should be like this


...
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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

robwork
Starting Member

USA
3 Posts
Posted - 08/16/2013 :  16:27:20  Show Profile  Reply with Quote
I made the changes and i get an error
(Msg 156, Level 15, State 1, Line 98
Incorrect syntax near the keyword 'PIVOT'.)

it has to be a simple syntax error but i can't see it


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
,seg.CODE
,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 (
'*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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts
Posted - 08/16/2013 :  20:41:22  Show Profile  Reply with Quote
You are missing a parenthesis


...
PIVOT (
SUM(hrs)
FOR 
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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