none
need help in sql query RRS feed

  • Question

  • HI,

    I HAVE A TABLE AND A VIEW:
    TABLE A , TABLE B , VIEW B.

    MY "TABLE A" :
    ID  NAME   EMAIL            EMP_USERLEVEL     EMP_SECTION_CODE    EMP_MANAGER
    1   ABC   ABC@YAHOO.COM          I                   LOG             LOG
    2   DEF   DEF@YAHOO.COM          II                  LOG             XYZ
    3   GHI   GHI@YAHOO.COM          I                   LOG             LOG
    4   JKL   JKL@YAHOO.COM          I                   LOG             LOG
    5   MNO   MNO@YAHOO.COM          II                  XYZ             XYZ

    MY TABLE STRUCTURE HAS USERLEVEL , EMPLOYEE SECTION CODE AND EMPLOYEE'S MANAGER CODE.
    EMPLOYEE HAS USERLEVEL 'I' AND
    EMPLOYEE'S MANAGER HAS USERLEVEL 'II' , AS SHOWN IN TABLE.


    NOW MY VIEW FOR TABLE B, "VIEW B":
    ID  NAME   EMAIL            DAYS_DEFINED   NO_OF_DAYS
    1   ABC   ABC@YAHOO.COM         2              2   
    2   DEF   DEF@YAHOO.COM         2              2
    3   GHI   GHI@YAHOO.COM         2              4
    4   JKL   JKL@YAHOO.COM         2              3
    5   MNO   MNO@YAHOO.COM         2              2

    I AM NOT GIVING MY "TABLE B" STRUCTURE SINCE AM SHOWING HOW MY VIEW(VIEW B) LOOKS LIKE...
    HERE I HAVE STARTDATE AND ENDDATE COL(NOT SHOWN IN VIEW B)AND FROM THIS AM CALCULATING NO_OF_DAYS AS SHOWN BELOW,
    "DATEDIFF(DAY, ACTION_DATE, GETDATE()) AS NO_OF_DAYS" ...


    NOW I AM TRYING TO CREATE A STORED PROCEDURE, WHICH WILL CHECK VIEW AND INSERT RECORDS IN ANOTHER TABLE BASED ON CONDITION ., CONDITION IS IF NO_OF_DAYS > DAYS_DEFINED i.e., if (3 > 2) as for ID '4' / (4 > 2) as for ID '3'.

    now this query , select * from VIEW B where NO_OF_DAYS > DAYS_DEFINED , will result in (2 records),as shown below

    ID  NAME   EMAIL            DAYS_DEFINED   NO_OF_DAYS 
    3   GHI   GHI@YAHOO.COM         2              4
    5   JKL   JKL@YAHOO.COM         2              3

    now in the above result , the first record has ID '3' and last one has ID '4'.
    now from Table A, i want to select the (ID,NAME,EMAIL) BASED ON EMP_MANAGERS FOR EACH RECORD.
    i.e., here employee with ID '3', name 'GHI' has EMP_MANAGER(IN TABLE A) AS 'LOG' , I WANT TO SELECT THE
    ID,NAME AND EMAIL FOR THIS MANAGER (WHERE EMP_SECTION_CODE = 'LOG' AND EMP_USERLEVEL = 'II') AS I STATED THAT
    USERLEVEL = 'II' for managers.

    I NEED A QUERY that will return below from "table A", based on output of this query (select * from VIEW B where NO_OF_DAYS > DAYS_DEFINED)... final o/p shd be,

    2   DEF   DEF@YAHOO.COM
    2   DEF   DEF@YAHOO.COM

    Thanks to help.

     

    Tuesday, February 19, 2008 11:45 AM

All replies

  • Hello,

     

    Which kind of database are you using ? ( Access,Sql Server 2000/2005, Oracle, and so on )

     

    I don't think that Access accepts stored procedures ( in 2000 no )

     

    Have a nice day

     

    Tuesday, February 19, 2008 2:27 PM
  •  

    HI,

     

    AM USING SQL SERVER 2005.

     

     

     

    Tuesday, February 19, 2008 2:37 PM
  • Hello,

     

    I will create a database with the tables you has defined , load the tables and try to find a solution for you

     

    Maybe in 24 hours ( sorry for the delay but i have to repair an application )

     

    Have a nice day

    Tuesday, February 19, 2008 3:21 PM
  • How about:

     

    Code Snippet

    Select A.*

    From

      TableA As A,

      TableA As B,

      ViewB  As C

    Where

      A.Emp_Section = B.Emp_Manager AND

      B.Id = C.Id AND

      A.Emp_userLevel = "ii" AND

      C.Days_defined < C.No_of_Days

     

     

     

    Wednesday, February 20, 2008 3:55 PM
  • Your query should look like

     

    SELECT TA2.[ID], TA2.[NAME], TA2.[EMAIL] FROM VIEW_B VB
     INNER JOIN TABLE_A TA ON VB.ID=TA.ID
     INNER JOIN TABLE_A TA2 ON TA2.[EMP_SECTION_CODE]=TA.[EMP_MANAGER]
    WHERE NO_OF_DAYS > DAYS_DEFINED AND TA2.[EMP_SECTION_CODE]='LOG' AND
    TA2.[EMP_USERLEVEL]='II'

    Thursday, February 21, 2008 11:06 AM
    Moderator