none
display / insert values by comparing 2 columns of a table RRS feed

  • Question

  • hi

    i have a table which has the following columns 

    A_ID, A_Value,A_Date,B_ID,B_Value_B_Date

     

    i have another table as follows

    A_ID,B_ID,A_Value,B_Value, Date

     

    this is to be done 

    * if A_date and B date are equal, one record shoould be entered to the TABLE 02. the values should be A_ID,B_ID,A_Value_B_Value and A_Date (since the date is equal, no problem; even can insert B_Date instead of A_Date)

    * if A_Date and B_Date are different, then the following should happen

    insert one record to TABLE02 as A_ID,B_ID, A_Value, 0 , A_Date

    insert another record to TABLE02 as A_ID,B_ID, 0 , B_Value, B_Date

     

    (As you can see, when the date differs we enter the single record of Table 01 into 2 recoerds in table 02. the Value column and Date column are related in such a way that when A's date is entered, the value for B should be 0 and vise versa..)

     

    i tried having a UDF but in halfway remembered that we cannot include INSERTS in UDFs..

    can anyone please explain me how can i solve this problem ??

     

    PS: For your reference , i've sketched a diagram. please click on this link to view it for further clarifications

    http://i54.tinypic.com/eq6p7t.png

     

    thanks in advance

    -novicedba :)

    • Edited by novicedba Monday, October 11, 2010 9:35 AM changed the URL
    Monday, October 11, 2010 9:30 AM

All replies

  • can u give your tables script and some insert statements and what is the output required with that input data???
    Monday, October 11, 2010 9:42 AM
  • First insert all data into the tbl1.After that execute the below statements for inserting the data into the tbl2

     

    INSERT

     

    INTO tbl2(A_ID,B_ID,A_Value,B_Value,Date) SELECT A_ID,B_ID,A_Value,B_Value,A_Date FROM tbl1 WHERE A_Date = B_Date

    INSERT

     

    INTO tbl2(A_ID,B_ID,A_Value,B_Value,Date) SELECT A_ID,B_ID,A_Value,0,A_Date FROM tbl1 WHERE A_Date <> B_Date

    INSERT

     

    INTO tbl2(A_ID,B_ID,A_Value,B_Value,Date) SELECT A_ID,B_ID,0,B_Value,B_Date FROM tbl1 WHERE A_Date <> B_Date


    " Education is the beginning of transformation. Dedicate yourself to daily learning via Blogs/Forums/books and coaching "
    Click here to read my blog
    Monday, October 11, 2010 9:55 AM