Professional Web Applications Themes

NEWBIE NEED HELP - Microsoft SQL / MS SQL Server

I got a huge table which store horse information, the table look like this: Date Horse Weight Distance Point 20030529 B 115 1800 99 20030601 A 130 1600 95 20030601 B 121 1200 100 20030605 A 115 1200 100 20030609 B 111 1600 101 20030610 A 105 1800 102 I want to search a pattern about if a horse got 100 point and NEXT race is with a LIGHTER weight, what the point it will gain? I tried hours with Cursors, but nothing I can get, can anyone show me some example code? I am a SQL newbie Thanks very ...

  1. #1

    Default NEWBIE NEED HELP

    I got a huge table which store horse information, the
    table look like this:


    Date Horse Weight Distance Point
    20030529 B 115 1800 99
    20030601 A 130 1600 95
    20030601 B 121 1200 100
    20030605 A 115 1200 100
    20030609 B 111 1600 101
    20030610 A 105 1800 102

    I want to search a pattern about if a horse got 100 point
    and NEXT race is with a LIGHTER weight, what the point it
    will gain?

    I tried hours with Cursors, but nothing I can get, can
    anyone show me some example code? I am a SQL newbie

    Thanks very much


    kelvinfun Guest

  2. #2

    Default NEWBIE NEED HELP

    I got a huge table which store horse information, the
    table look like this:


    Date Horse Weight Distance Point
    20030529 B 115 1800 99
    20030601 A 130 1600 95
    20030601 B 121 1200 100
    20030605 A 115 1200 100
    20030609 B 111 1600 101
    20030610 A 105 1800 102

    I want to search a pattern about if a horse got 100 point
    and NEXT race is with a LIGHTER weight, what the point it
    will gain?

    I tried hours with Cursors, but nothing I can get, can
    anyone show me some example code? I am a SQL newbie

    Thanks very much
    KELVINFUN Guest

  3. #3

    Default Re: NEWBIE NEED HELP

    CREATE TABLE HorseTable
    (
    [Date] DATETIME,
    Horse CHAR(1),
    Weight TINYINT,
    Distance SMALLINT,
    Point TINYINT
    )

    INSERT HorseTable VALUES('20030529','B',115,1800, 99)
    INSERT HorseTable VALUES('20030601','A',130,1600, 95)
    INSERT HorseTable VALUES('20030601','B',121,1200,100)
    INSERT HorseTable VALUES('20030605','A',115,1200,100)
    INSERT HorseTable VALUES('20030609','B',111,1600,101)
    INSERT HorseTable VALUES('20030610','A',105,1800,102)

    SELECT * FROM HorseTable a
    INNER JOIN
    HorseTable b
    ON a.horse = b.horse
    AND a.date > b.date
    WHERE b.point >= 100

    DROP TABLE HorseTable


    --
    Aaron Bertrand, SQL Server MVP
    [url]http://www.aspfaq.com/[/url]

    Please reply in the newsgroups, but if you absolutely
    must reply via e-mail, please take out the TRASH.


    "kelvinfun" <kelvinfunhotmail.com> wrote in message
    news:030b01c3436f$37662de0$a501280aphx.gbl...
    > I got a huge table which store horse information, the
    > table look like this:
    >
    >
    > Date Horse Weight Distance Point
    > 20030529 B 115 1800 99
    > 20030601 A 130 1600 95
    > 20030601 B 121 1200 100
    > 20030605 A 115 1200 100
    > 20030609 B 111 1600 101
    > 20030610 A 105 1800 102
    >
    > I want to search a pattern about if a horse got 100 point
    > and NEXT race is with a LIGHTER weight, what the point it
    > will gain?
    >
    > I tried hours with Cursors, but nothing I can get, can
    > anyone show me some example code? I am a SQL newbie
    >
    > Thanks very much
    >
    >

    Aaron Bertrand [MVP] Guest

  4. #4

    Default Re: NEWBIE NEED HELP

    Sorry, missed the weight bit


    SELECT * FROM HorseTable a
    INNER JOIN
    HorseTable b
    ON a.horse = b.horse
    AND a.date > b.date
    AND a.weight < b.weight
    WHERE b.point >= 100



    "kelvinfun" <kelvinfunhotmail.com> wrote in message
    news:030b01c3436f$37662de0$a501280aphx.gbl...
    > I got a huge table which store horse information, the
    > table look like this:
    >
    >
    > Date Horse Weight Distance Point
    > 20030529 B 115 1800 99
    > 20030601 A 130 1600 95
    > 20030601 B 121 1200 100
    > 20030605 A 115 1200 100
    > 20030609 B 111 1600 101
    > 20030610 A 105 1800 102
    >
    > I want to search a pattern about if a horse got 100 point
    > and NEXT race is with a LIGHTER weight, what the point it
    > will gain?
    >
    > I tried hours with Cursors, but nothing I can get, can
    > anyone show me some example code? I am a SQL newbie
    >
    > Thanks very much
    >
    >

    Aaron Bertrand [MVP] Guest

  5. #5

    Default Re: NEWBIE NEED HELP

    Hi

    If you as a question then it is always better to post DDL (create table
    statements) and example data (as Insert Statements), with an example of the
    required output. This way there will be no ambiguities in the question and
    it makes it easier for anyone replying to check their answer.

    CREATE TABLE HorseInfo ( [Date] DateTime, Horse Char(1), Weight INT,
    Distance INT , Point int )

    INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
    '20030529','B' , 115, 1800, 99 )
    INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
    '20030601','A', 130, 1600, 95 )
    INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
    '20030601','B', 121, 1200, 100 )
    INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
    '20030605','A', 115, 1200, 100 )
    INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
    '20030609','B', 111, 1600, 101 )
    INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
    '20030610','A', 105, 1800, 102 )

    The following will give a solution with the data you have provided, but I
    suspect that the data is not truely representative.

    SELECT I.[Date], I.Horse, I.Weight, I.Distance, I.Point
    FROM HorseInfo I
    JOIN ( SELECT [Date], Horse, Weight, Distance, Point
    FROM HorseInfo H
    WHERE H.Point = 100 ) O
    ON I.date > O.date
    AND I.Weight < O.Weight
    AND I.Horse = O.Horse

    John

    "KELVINFUN" <kelvinfunhotmail.com> wrote in message
    news:68c001c3436f$556d4080$a401280aphx.gbl...
    > I got a huge table which store horse information, the
    > table look like this:
    >
    >
    > Date Horse Weight Distance Point
    > 20030529 B 115 1800 99
    > 20030601 A 130 1600 95
    > 20030601 B 121 1200 100
    > 20030605 A 115 1200 100
    > 20030609 B 111 1600 101
    > 20030610 A 105 1800 102
    >
    > I want to search a pattern about if a horse got 100 point
    > and NEXT race is with a LIGHTER weight, what the point it
    > will gain?
    >
    > I tried hours with Cursors, but nothing I can get, can
    > anyone show me some example code? I am a SQL newbie
    >
    > Thanks very much

    John Bell Guest

  6. #6

    Default Re: NEWBIE NEED HELP

    Assuming that (horse,date) is the primary key, try this:

    SELECT I.*
    FROM HorseTable AS H
    JOIN
    (SELECT A.horse, A.[date] AS hdate, MIN(B.[date]) AS idate
    FROM HorseTable AS A
    JOIN HorseTable AS B
    ON A.horse = B.horse
    AND B.[date]>A.[date]
    GROUP BY A.horse, A.[date]) AS J
    ON H.horse = J.horse AND H.[date] = J.hdate
    JOIN HorseTable AS I
    ON.I.horse = J.horse AND I.[date] = J.idate
    WHERE H.point = 100 AND I.weight < H.weight

    (thanks Aaron, for the DDL and sample data)

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Portas Guest

  7. #7

    Default Re: NEWBIE NEED HELP

    Hi

    Maybe something like:

    SELECT I.[Date], I.Horse, I.Weight, I.Distance, I.Point
    FROM HorseInfo I
    JOIN ( SELECT [Date], Horse, Weight, Distance, Point
    FROM HorseInfo H
    WHERE H.Point = 100 ) O
    ON I.date > O.date
    AND I.Weight < O.Weight
    AND I.Horse = O.Horse
    WHERE NOT EXISTS
    ( SELECT *
    FROM HorseInfo H
    WHERE I.date > H.date
    AND O.date < H.date
    AND I.Horse = H.Horse )

    John

    "kelvinfun" <kelvinfunhotmail.com> wrote in message
    news:6a0e01c3438f$a1536f90$a401280aphx.gbl...
    > Thank you very much, it looks work, if I want only to find
    > out the NEXT race only NOT the all races after the horse
    > made 100 point
    >
    > any hints ?
    >
    > Thanks
    >
    >
    > >-----Original Message-----
    > >Hi
    > >
    > >If you as a question then it is always better to post DDL
    > (create table
    > >statements) and example data (as Insert Statements), with
    > an example of the
    > >required output. This way there will be no ambiguities in
    > the question and
    > >it makes it easier for anyone replying to check their
    > answer.
    > >
    > >CREATE TABLE HorseInfo ( [Date] DateTime, Horse Char(1),
    > Weight INT,
    > >Distance INT , Point int )
    > >
    > >INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance,
    > Point ) values (
    > >'20030529','B' , 115, 1800, 99 )
    > >INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance,
    > Point ) values (
    > >'20030601','A', 130, 1600, 95 )
    > >INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance,
    > Point ) values (
    > >'20030601','B', 121, 1200, 100 )
    > >INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance,
    > Point ) values (
    > >'20030605','A', 115, 1200, 100 )
    > >INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance,
    > Point ) values (
    > >'20030609','B', 111, 1600, 101 )
    > >INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance,
    > Point ) values (
    > >'20030610','A', 105, 1800, 102 )
    > >
    > >The following will give a solution with the data you have
    > provided, but I
    > >suspect that the data is not truely representative.
    > >
    > >SELECT I.[Date], I.Horse, I.Weight, I.Distance, I.Point
    > >FROM HorseInfo I
    > >JOIN ( SELECT [Date], Horse, Weight, Distance, Point
    > > FROM HorseInfo H
    > > WHERE H.Point = 100 ) O
    > >ON I.date > O.date
    > >AND I.Weight < O.Weight
    > >AND I.Horse = O.Horse
    > >
    > >John
    > >
    > >"KELVINFUN" <kelvinfunhotmail.com> wrote in message
    > >news:68c001c3436f$556d4080$a401280aphx.gbl...
    > >> I got a huge table which store horse information, the
    > >> table look like this:
    > >>
    > >>
    > >> Date Horse Weight Distance Point
    > >> 20030529 B 115 1800 99
    > >> 20030601 A 130 1600 95
    > >> 20030601 B 121 1200 100
    > >> 20030605 A 115 1200 100
    > >> 20030609 B 111 1600 101
    > >> 20030610 A 105 1800 102
    > >>
    > >> I want to search a pattern about if a horse got 100
    > point
    > >> and NEXT race is with a LIGHTER weight, what the point
    > it
    > >> will gain?
    > >>
    > >> I tried hours with Cursors, but nothing I can get, can
    > >> anyone show me some example code? I am a SQL newbie
    > >>
    > >> Thanks very much
    > >
    > >
    > >.
    > >

    John Bell Guest

Similar Threads

  1. A newbie with a newbie question
    By dusty_davis@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 5
    Last Post: October 14th, 07:54 AM
  2. Newbie FMS help please!
    By G1FEF in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: February 26th, 10:17 PM
  3. Newbie Needs Help!
    By starfishbaby in forum Macromedia Flash Player
    Replies: 1
    Last Post: September 14th, 06:40 PM
  4. newbie ill q
    By Curt Laven in forum Adobe Illustrator Windows
    Replies: 0
    Last Post: July 14th, 01:40 AM
  5. Please help the Newbie
    By Stingray in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 0
    Last Post: July 18th, 06:50 PM

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