Results 1 to 4 of 4

Thread: SQL How to..??

  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Thumbs up Unanswered: SQL How to..??

    I have the ff: records:

    Code:
    RECORD#01 EMP1    WEEK1   10
    RECORD#02 EMP1    WEEK2   10
    RECORD#03 EMP1    WEEK3   10
    RECORD#04 EMP1    WEEK4   10
    
    RECORD#05 EMP2    WEEK1   12
    RECORD#06 EMP2    WEEK2   12
    RECORD#07 EMP2    WEEK3   12
    RECORD#08 EMP2    WEEK4   12
    
    RECORD#09 EMP1    WEEK1   10
    RECORD#10 EMP1    WEEK2   10
    RECORD#11 EMP1    WEEK3   10
    RECORD#12 EMP1    WEEK4   10
    I can run a total for all the hours done by employee in my SQL statement by grouping via employee but is what I have below possible? Thanks

    Code:
    EMP   WK1   WK2   WK3   WK4  TOTAL
    EMP1  20    20    20    20    80
    EMP2  12    12    12    12    48

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's possible --
    Code:
    select EMP
         , sum(case when wk='WEEK1'
                then hours else 0 end) as WK1
         , sum(case when wk='WEEK2'
                then hours else 0 end) as WK2
         , sum(case when wk='WEEK3'
                then hours else 0 end) as WK3
         , sum(case when wk='WEEK4'
                then hours else 0 end) as WK4
         , sum(hours) as TOTAL
      from yourtable
    group
        by EMP
    rudy
    http://r937.com/

  3. #3
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Thumbs up

    It worked great!!!

    I thought I was going to end up coding it to get the same results (I use VB front end). You have save me a lot of time.

    Thank you so much!!!

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yup,

    Just have to join the table to itself

    SELECT EMP, Week1, Week2, ...
    FROM (SELECT EMP, SUM(AMT) AS Week1 FROM myTable Where col1='WEEK1' GROUP BY EMP)
    (SELECT EMP, SUM(AMT) AS Week2 FROM myTable Where col1='WEEK2' GROUP BY EMP)
    ect


    You might need to do a list of all distinct EMPIDs as a driver and left join to the derived tables using ISNULL on the amount....

    HTH

    Brett

    8-)

Posting Permissions

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