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
 General SQL Server Forums
 New to SQL Server Programming
 New to Pivot - Please help		  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
52326 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

549 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  
 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.04 seconds. Powered By: Snitz Forums 2000