Results 1 to 2 of 2

Thread: Pivoting data

  1. #1
    Join Date
    Feb 2011
    Posts
    8

    Unanswered: Pivoting data

    I am trying to do two things ...I have a query that sums up all of an agent "exceptions" as special minutes and then presents them later as part of a sum.

    What I need to do is to show all of the data as follows:

    Agent Number Regular Time Total O/T ETO Sick Time Vacation Holiday Misc
    8245 0 0 0 0 0 0 30
    Currently how I'm seeing my end result is as follows:

    Agent Number Regular Time Total O/T
    8245 18.01 0

    My two queries are as follows:

    SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, code, s2.totalminutes into scratchpad4
    FROM
    (select distinct Employeenumber,[Name] from Scratchpad1) AS s1
    inner JOIN
    (select employeenumber, exceptiondate, code, sum(duration) as totalminutes
    from scratchpad3
    where exceptiondate between @payrollstartdate And @payrollenddate
    group by employeenumber, exceptiondate, code) as s2
    ON s1.Employeenumber = s2.Employeenumber
    order by exceptiondate asc

    and

    SELECT [EmployeeNumber],[Name],SUM([Minutes]) AS summinutes,SUM([Minutes])/60 AS sumhours,
    SUM(CASE WHEN Cat=2 THEN [Minutes] ELSE 0 END) AS specminutes

    FROM
    (
    SELECT [EmployeeNumber],[Name],[Dateonly],[Minutes],1 AS Cat
    FROM Scratchpad2
    where dateonly between @payrollstartdate And @payrollenddate
    UNION ALL
    SELECT [EmployeeNumber],[Name],[ExceptionDate],[TotalMinutes],2
    FROM ScratchPad4
    )t
    GROUP BY [EmployeeNumber],[Name]

    and the only aggregate query I have is this (which is my last run query)

    SELECT DISTINCT
    [ScratchPad5].EmployeeNumber,
    SUM( case when [sumhours]>40
    THEN 40
    ELSE cast([sumhours] as numeric(12,2))
    END ) AS TotalRegHours,
    SUM( case when [sumhours]>40
    THEN cast([sumhours]-40 as numeric(12,2))
    ELSE 0
    END ) AS TotalOT

    into scratchpad7

    FROM
    ScratchPad5
    GROUP BY
    [ScratchPad5].EmployeeNumber,
    sumhours
    order by employeenumber asc

    The codes that are needing to be sorted are:

    ETO
    Sick Time
    Vacation
    Holiday

    and then if the code in my first query is any other value besides those, they are summed and labeled as "Misc"

    I know that I need to pivot my data and also use CASE but I have no idea where.
    Last edited by dougancil; 02-22-11 at 16:57.

  2. #2
    Join Date
    Feb 2011
    Location
    Miami, Florida
    Posts
    10
    Hi there...


    I don't know so much about Pivoting data..But as I understand, Pivoting data is a technique that rotates data from a state of rows to a state of columns, possibly aggregating multiple source values into the same target row and column intersection. Some practical reasons to pivot data include formatting data for reporting purposes, calculating custom aggregations, and solving relational division problems.

    So, am I right ???

    Regards
    Manish

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •