Results 1 to 5 of 5

Thread: Please Advice !

  1. #1
    Join Date
    Feb 2002
    Posts
    17

    Unanswered: Please Advice !

    Hi,

    I am trying to solve this procedure.

    Let me try to explain it...I am getting DEGREEID from one of the SELECT query . I want to OUTPUT (ie , COUNT) from procedure,the number of departments with the degreeid, got from the above query.

    With below procedure, Since an employee can have multiple DEGREEID , the cursor is giving OUPTUT ie, COUNT for the LAST Degreeid. Eventhough the previous DEGREEID dont have any DEPARTMENT...but only for the LAST DEGREEID...!

    How can I solve this..... whether I can solve this with CURSOR or I have to use someother way...Please advice me !



    DATA
    -------
    DEGREE_EARNED
    ---------------------
    EMPID DEGREEID
    ------ ------------
    201 12
    201 3
    201 250
    202 3
    202 10
    203 17

    DEPARTMENT
    ---------------
    DEPID DEGREEID
    ------ ------------
    10 1 12
    111 250
    111 12
    121 3
    121 12
    121 250
    ------------------------------------




    --------------------------------------------------------------------
    DECLARE @vchid int
    DECLARE testcursor CURSOR FOR

    SELECT degree_id
    FROM degree_earned WHERE emp_id= @empid

    OPEN testcursor
    FETCH NEXT FROM testcursor INTO @vchid

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN
    Select @outresult = COUNT(*)
    from
    department
    where degree_id = @vchid

    FETCH NEXT FROM testcursor INTO @vchid
    END
    --------------------------------------------------------------------

  2. #2
    Join Date
    Feb 2002
    Posts
    17

    Unhappy

    Can somebody help me,please (:

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So is what you want something like:
    PHP Code:
    EMPID DEGREEID DEPT_COUNT
    ----- -------- ----------
    201   12       2
    201   3        1
    201   250      2
    202   3        1
    202   10       0
    203   17       0 
    I don't understand exactly what you want, so I can't be much help until I do. Sorry!

    -PatP

  4. #4
    Join Date
    Feb 2002
    Posts
    17
    Hey Pat,

    Thats what i wanted...Can u please show me , how to solve this...Thanks!

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    CREATE TABLE DEGREE_EARNED(EMPID int, DEGREEID int)
    CREATE TABLE DEPARTMENT(DEPID int, DEGREEID int)
    GO
    INSERT INTO DEGREE_EARNED(EMPID, DEGREEID)
    SELECT 201, 12  UNION ALL
    SELECT 201, 3   UNION ALL
    SELECT 201, 250 UNION ALL
    SELECT 202, 3   UNION ALL
    SELECT 202, 10  UNION ALL
    SELECT 203, 17
    
    INSERT INTO DEPARTMENT(DEPID, DEGREEID)
    SELECT 101, 12  UNION ALL
    SELECT 111, 250 UNION ALL
    SELECT 111, 12  UNION ALL
    SELECT 121, 3   UNION ALL
    SELECT 121, 12  UNION ALL
    SELECT 121, 250
    GO
    
    
       SELECT A.DEGREEID, ISNULL(ROW_OCCURS,0)
         FROM ( SELECT DISTINCT DEGREEID FROM DEGREE_EARNED) AS A
    LEFT JOIN ( SELECT DEGREEID, COUNT(*) AS ROW_OCCURS 
    	      FROM DEPARTMENT GROUP BY DEGREEID) AS B
    	ON A.DEGREEID = B.DEGREEID
    GO
    
    --DROP TABLE DEGREE_EARNED
    --DROP TABLE DEPARTMENT
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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