Professional Web Applications Themes

Execute UPDATE from CURSOR and better way??? - Microsoft SQL / MS SQL Server

Hi! There are two tables. Table A has 3 columns: begindate, enddate and rate. Table B has 2 columns: personID and employDate. The objective is to get the correct rate from Table A based on the date in 'employDate' column from table B when it is between 'begindate' and 'enddate' in Table A. I use the CURSOR function to get each 'employDate' and generate a SELECT statement like: SELECT rate FROM tableA WHERE begindate<='10/31/2002'(<-employeDate) and enddate>='10/31/2002'. Instead of using PRINT to generate this SELECT statement, how can I actually make it run within FETCH? I tried EXEC (SELECT...) but it ...

  1. #1

    Default Execute UPDATE from CURSOR and better way???

    Hi!
    There are two tables. Table A has 3 columns: begindate,
    enddate and rate. Table B has 2 columns: personID and
    employDate. The objective is to get the correct rate from
    Table A based on the date in 'employDate' column from
    table B when it is between 'begindate' and 'enddate' in
    Table A.

    I use the CURSOR function to get each 'employDate' and
    generate a SELECT statement like: SELECT rate FROM tableA
    WHERE begindate<='10/31/2002'(<-employeDate) and
    enddate>='10/31/2002'. Instead of using PRINT to generate
    this SELECT statement, how can I actually make it run
    within FETCH? I tried EXEC (SELECT...) but it still
    doesn't work.

    Greatly appreciate for your help in advance!




    Jane Guest

  2. #2

    Default Re: Thank you but why?

    Tom,

    It works, thank you very much. I thought Table A and Table
    B should have a common key for JOIN. In your solution, it
    looks like the common key is 'employDate' which is also
    used for 'between'. I have never seen it using like that,
    thanks!

    Jane 
    If what you want is the rate for the person's employDate,
    try: 
    b.EndDate 
    $8e1b10e0$gbl... [/ref]
    from [/ref]
    tableA [/ref]
    generate 
    >[/ref]
    Jane Guest

  3. #3

    Default Re: Thank you but why?

    You can join anything to anything else - provided that they have meaning.

    --
    Tom
    --------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com.sql
    ..

    "Jane" <com> wrote in message news:05ba01c367eb$3cfd13e0$gbl... 
    > If what you want is the rate for the person's employDate,
    > try: 
    > b.EndDate 
    > $8e1b10e0$gbl... [/ref]
    > from  [/ref]
    > tableA  [/ref]
    > generate  
    > >[/ref][/ref]
    Tom Guest

Similar Threads

  1. To Use Update Cursor or something else
    By Colin in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 4th, 05:42 PM
  2. Replies: 0
    Last Post: July 16th, 07:00 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139