Professional Web Applications Themes

match query questions - Microsoft SQL / MS SQL Server

select foo1.myid, foo1.MyDate, MAX(foo2.subDate) from foo1 join foo2 on foo1.myid = foo2.myid where foo1.MyDate in ( foo2.subDate , DATEADD(mm,-1,foo2.subDate), DATEADD(mm,+1,foo2.subDate)) group by foo1.myid, foo1.MyDate Result set is: MyId MyDate subDate 2 2003-03-15 00:00:00.000 2003-04-15 00:00:00.000 1 2003-04-15 00:00:00.000 2003-03-15 00:00:00.000 3 2003-04-15 00:00:00.000 2003-03-15 00:00:00.000 Carl Federl Please post DDL (create table) with datatypes, primary and foreign keys. *** Sent via Developersdex [url]http://www.developersdex.com[/url] *** Don't just participate in USENET...get rewarded for it!...

  1. #1

    Default Re: match query questions


    select foo1.myid, foo1.MyDate, MAX(foo2.subDate)
    from foo1
    join foo2
    on foo1.myid = foo2.myid
    where foo1.MyDate
    in ( foo2.subDate , DATEADD(mm,-1,foo2.subDate),
    DATEADD(mm,+1,foo2.subDate))
    group by foo1.myid, foo1.MyDate

    Result set is:
    MyId MyDate subDate
    2 2003-03-15 00:00:00.000 2003-04-15 00:00:00.000
    1 2003-04-15 00:00:00.000 2003-03-15 00:00:00.000
    3 2003-04-15 00:00:00.000 2003-03-15 00:00:00.000


    Carl Federl
    Please post DDL (create table) with datatypes, primary and foreign keys.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    Carl Federl Guest

  2. #2

    Default Re: match query questions

    >> ... The tables are huge. <<

    So what? You can still do the GROUP BY & it works well even on properly
    indexed billion row tables.

    Other options are a correlation subquery with a MIN/MIN aggregate or a TOP 1
    with an ORDER BY clause.

    SELECT *, ( SELECT MAX(foo2.subDate)
    FROM foo2
    WHERE foo2.MYID = foo1.MYID )
    FROM foo1 ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

Similar Threads

  1. Query to match date -- ODBD -- Access database
    By dwf in forum Coldfusion Database Access
    Replies: 1
    Last Post: December 5th, 03:39 AM
  2. query questions
    By mxuser in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 22nd, 06:13 PM
  3. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  4. query match for phone number
    By pflynn02 in forum Coldfusion - Getting Started
    Replies: 0
    Last Post: March 13th, 03:22 AM
  5. Replies: 1
    Last Post: July 2nd, 09:09 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