Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    9

    Unanswered: Help needed with Joins

    Hi ...
    I have a table which has the following contents
    Dept actual_month_of_joining Expected_Month_Of_Joining
    IT jan09 jan 09
    BPO feb 09 jan 09
    HR feb 09 jan 09
    HR jan 09 jan 09

    Now i want to find the actual no.of people joined in a month say jan to the expected no.of people to join in that month(jan) ,grouped by Dept. Can someone please tell me how to do this..
    The sql query that i wrote doesnt seem to work :
    SELECT s.dept as Department,
    case when (count(d.actual_month_of_joining))>= 0 and (count(e.Expected_Month_Of_Joining)) >= 0
    then ((count(d.actual_month_of_joining))*100)/(count(e.Expected_Month_Of_Joining)) else
    0 end as actual_offer_to_join
    FROM (
    SELECT DISTINCT dept
    FROM table1
    ) AS s
    LEFT OUTER
    JOIN table1 as d
    ON d.dept = s.dept
    AND d.actual_month_of_joining = 'jan 09'
    LEFT OUTER
    JOIN table1 as e
    ON e.dept = s.dept
    and e.Expected_Month_Of_Joining = 'jan 09'
    GROUP
    BY s.Dept;
    Please help me resolve this.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    niths_86, I think this might get you what you want without even doing a join:
    Code:
    SELECT DEPT
         , SUM(CASE ACTUAL_MONTH_OF_JOINING    WHEN 'JAN 09' THEN 1 
                                                             ELSE NULL
               END) AS ACTUAL
         , SUM(CASE EXPECTEDL_MONTH_OF_JOINING WHEN 'JAN 09' THEN 1 
                                                             ELSE NULL
               END) AS EXPECTED
    FROM table-name
    GROUP BY DEPT
    If it is not exactly what you need (I am not using *100) it might be close enough for you to started.

  3. #3
    Join Date
    Jul 2009
    Posts
    9
    Thanks for replying ..will try tomorrow and let you know

  4. #4
    Join Date
    Jul 2009
    Posts
    9
    Thanks a lot .. it works perfectly fine !!!

Posting Permissions

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