Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2011
    Posts
    8

    Unanswered: Parsing Data into Additional Columns

    I have the following query:

    Code:
    SELECT
    [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 
    FROM ScratchPad5
    GROUP BY 
    [ScratchPad5].EmployeeNumber, 
    sumhours
    
    union 
    select employeenumber, null, null
    from scratchpad3 where code in ('Vacation','Holiday','Sicktime', 'ETO')
    
    order by employeenumber asc
    and what I'm trying to do is that if I have any values in my query that match Vacation, Holiday, Sicktime or ETO, that this will show in a seperate columns that are designated as such. If there are no values in my query that match those, then the columns would just show 0.0.

    right now what I'm seeing is this:

    Code:
    EmployeeNumber      TotalRegHours        TotalOT
        8244	           NULL	           NULL
        8247	           12.99	   .00
        8330	            7.64	   .00
        8389	           18.67	   .00
        8433	            9.74	   .00
        8442	           17.91	   .00
        8451	            3.72	   .00
        8455                    4.72           .00
        8467	           37.48	   .00
        8471	           40.00	  8.68 
        8472	           27.07	   .00
        8475	           25.55	   .00
        8477	           28.74	   .00
        8482	            6.69	   .00
    and what I need to see is this:

    Code:
    EmployeeNumber          TotalRegHours    TotalOT       Vacation      Holiday    Sicktime    ETO
        8244	            NULL	   NULL         .00          .00         .00       .00
        8247	           12.99	   .00           .00          .00         .00       .00
        8330	            7.64	   .00           .00          .00         .00       .00
        8389	           18.67	   .00           .00          .00         .00       .00
        8433	            9.74	   .00           .00          .00         .00       .00
        8442	           17.91	   .00           .00          .00         .00       .00
        8451	            3.72	   .00           .00          .00         .00       .00
        8455                    4.72           .00           .00          .00         .00       .00
        8467	           37.48	   .00           .00          .00         .00       .00
        8471	           40.00	  8.68           .00          .00         .00       .00
        8472	           27.07	   .00           .00          .00         .00       .00
        8475	           25.55	   .00           .00          .00         .00       .00
        8477	           28.74	   .00           .00          .00         .00       .00
        8482	            6.69	   .00           .00          .00         .00       .00
    can someone please assist with my query.

    Thank you

    Doug

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Just add more case statements
    sum(case when code='Vacation' then sumhours else 0 end.....

  3. #3
    Join Date
    Feb 2011
    Posts
    8
    Pdreyer,

    Ok here's my attempt but I'm getting incorrect syntax.

    Code:
    SELECT
    [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,
    SUM( case when code='Vacation' 
    then cast([sumhours]<40 as numeric(12,2))
    Else 0
    End ) as Vacation
    
    FROM ScratchPad5
    GROUP BY 
    [ScratchPad5].EmployeeNumber, 
    sumhours
    
    union 
    select employeenumber, null, null
    from scratchpad3 where code in ('Vacation','Holiday','Sicktime', 'ETO')
    
    order by employeenumber asc
    Server: Msg 170, Level 15, State 1, Line 12
    Line 12: Incorrect syntax near '<'.

    what am I missing?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT[ScratchPad5].EmployeeNumber
    ,  SUM CASE WHEN code in ('Vacation','Holiday','Sicktime', 'ETO') THEN 0
                ELSE CASE WHEN code < 40 THEN code ELSE 40 END
          END) AS TotalRegHours
    ,  SUM CASE WHEN code in ('Vacation','Holiday','Sicktime', 'ETO') THEN 0
                ELSE CASE WHEN code < 40 THEN 0    ELSE code - 40 END
          END) AS TotalOT
    ,  Sum(CASE WHEN 'Vacation' = code THEN [sumhours] END) AS Vacation
    ,  Sum(CASE WHEN 'Holiday' = code THEN [sumhours] END)  AS Holiday
    ,  Sum(CASE WHEN 'Sicktime' = code THEN [sumhours] END) AS Sicktime
    ,  Sum(CASE WHEN 'ETO' = code THEN [sumhours] END)      AS ETO
       FROM ScratchPad5
       GROUP BY [ScratchPad5].EmployeeNumber
       ORDER BY [ScratchPad5].EmployeeNumber
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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