Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005
    Posts
    71

    Unanswered: insert into table, query & union question...

    i am do the following:
    insert into table_a
    ([field1a], [field2a], [field3a])
    select field1b, field2b, field3b from tableb
    where datefield1b >= {variable datefield1} and datefield1b <= {variable datefield2}
    which is working.
    HOWEVER, i need to add/include 1 more record from tableb.
    My first inclination is to do a UPDATE to add that first record BUT i am wondering if there is a way to do a UNION to get that record?
    i need the next 1 RECORD that has a datefield1b value that is < {variable datefield1}
    so if my date range is 01/01/2007 thru 12/31/2007 i need to also include the previous 1 record from tableb that is < 01/01/2007. that date could be from 1 month to 24 months old. I don't want all those records because there could be a whole lot of them in there.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    union
    select top 1 field1b, field2b, field3b from tableb
    where datefield1b < {variable datefield1} order by datefield1b asc

  3. #3
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    ok.. a small correction. if u want to get the row that is immediately before 01/01/2007, u need a derived table. otherwise it may not take the immediate row..

    union [all]
    select * from
    (select top 1 field1b, field2b, field3b from tableb
    where datefield1b < {variable datefield1} order by datefield1b asc ) T

Posting Permissions

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