Author |
Topic  |
|
robwork
Starting Member
USA
3 Posts |
Posted - 08/14/2013 : 14:26:37
|
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
|
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
|
 |
|
robwork
Starting Member
USA
3 Posts |
Posted - 08/16/2013 : 16:27:20
|
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 |
 |
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 08/16/2013 : 20:41:22
|
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
|
 |
|
|
Topic  |
|
|
|