Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Pinoy in NJ
    Posts
    104

    Unanswered: [RESOLVED] How do run this in T-SQL?

    Code:
    DATA
    
    +-------+-------+-------+-------+
    | REPID | DEPT1 | DEPT2 | HOURS |
    +-------+-------+-------+-------+
    | 10000 |  501  |  501  |  100  |  <-- same dept
    | 10000 |  501  |  502  |  100  |
    | 10000 |  501  |  501  |  200  |  <-- same dept
    | 10000 |  501  |  503  |  100  |  
    | 10000 |  501  |  502  |  150  |
    +-------+-------+-------+-------+
    
    
    DESIRED RESULT
    
    +-------+------+------+
    | REPID | SAME | DIFF | 
    +-------+------+------+
    | 10000 | 300  | 350  |
    +-------+------+------+


    I'm planning to use a cursor to build this because I can't figure out how using T-SQL. It basically sums all records with the same department (DEPT1=DEPT2) and ones with different department (DEPT1<>DEPT2) and pivots it grouped by REPID.


    Any inputs would be appreciated.
    Last edited by ARPRINCE; 12-28-06 at 16:54. Reason: resolved

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select REPID
         , sum(case when DEPT1 = DEPT2
                    then HOURS end) as SAME
         , sum(case when DEPT1 <> DEPT2
                    then HOURS end) as DIFF
      from daTable
    group
        by REPID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    I was doing it totally wrong that's why I got stuck.
    Code:
    select REPID, SUM(case DEPT1 when DEPT2......

Posting Permissions

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