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 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,EmpDivisionNameFROM CCDM.dbo.EmployeeArchive eaINNER JOIN (SELECT MAX(ARCHIVE_DT) ARCHIVE_DTFROM CCDM.dbo.EmployeeArchiveWHERE ARCHIVE_DT between @enddate and DATEADD(s,-1,@enddate+1)) adON ea.ARCHIVE_DT = ad.ARCHIVE_DTWHERE TERM_DATE_DATE9 is nulland [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,COMPFROM (SELECT RIGHT(LTRIM(RTRIM(EMP_ID)),7) EMP_ID,SUM(SCHEDULED) SCHED,SUM(COMPLIANCE) COMPFROM CCDM.dbo.adhereWHERE 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 |
|