Author |
Topic |
robwork
Starting Member
3 Posts |
Posted - 2013-08-14 : 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 hrs7/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee1 *FLEX OFF AWARD 27/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee2 *FLEX OFF CODES 27/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee2 *FLEX OFF CODES 27/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee3 *FLEX OFF PAID 27/1/2013 7/31/2013 Escalation Branch Support Hotline Escalation Team 3B Employee3 *FLEX.ON>Flex Off Paid (Empowe 2want it to be:BegDate EndDate EmpDivisionName EmpGroupName EmpTeamName Employee1 *FLEX OFF AWARD *FLEX OFF PAID (etc) with hrs summed up underneath each codedeclare @startpass datetime declare @endpass datetime set @startpass = DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) -- use for previous month's startdateset @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 enddatedeclare @startdate datetimedeclare @enddate datetimeset @startdate = @startpassset @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 ,MIfromCCDM.dbo.FlashSchedwhere 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)) hrsfrom wadbleft outer joinsegON 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' ) pvtGO |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-16 : 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]) pvtGO ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
robwork
Starting Member
3 Posts |
Posted - 2013-08-16 : 16:27:20
|
I made the changes and i get an error (Msg 156, Level 15, State 1, Line 98Incorrect syntax near the keyword 'PIVOT'.)it has to be a simple syntax error but i can't see itdeclare @startpass datetime declare @endpass datetime set @startpass = DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) -- use for previous month's startdateset @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 enddatedeclare @startdate datetimedeclare @enddate datetimeset @startdate = @startpassset @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 ,MIfromCCDM.dbo.FlashSchedwhere 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)) hrsfrom wadbleft outer joinsegON seg.EMP_ID = wadb.LucIDwhere 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 - 2013-08-16 : 20:41:22
|
You are missing a parenthesis [CODE]...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 [/CODE] |
|
|
|
|
|