Results 1 to 4 of 4

Thread: sql headach

  1. #1
    Join Date
    Aug 2004
    Posts
    77

    Unanswered: sql headach

    Hi everybody !! I have problem again.
    This is my database(Microsoft Access)

    dat ti price
    9/15/2004 10:17:20 12
    9/15/2004 10:17:28 32
    9/15/2004 10:17:31 11
    9/16/2004 10:17:40 12
    9/16/2004 10:17:45 32

    I want to write sql that show the result of campare the last two price. For example, 32-12=20 I want the result to show 20.

  2. #2
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Cool

    Quote Originally Posted by gop373
    Hi everybody !! I have problem again.
    This is my database(Microsoft Access)

    dat ti price
    9/15/2004 10:17:20 12
    9/15/2004 10:17:28 32
    9/15/2004 10:17:31 11
    9/16/2004 10:17:40 12
    9/16/2004 10:17:45 32

    I want to write sql that show the result of campare the last two price. For example, 32-12=20 I want the result to show 20.
    I can only assume that you want to create something that takes the last two values and shows the difference.

    In short. SQL can't but your chosen DB might have other tricks.

    Your best bet would be to sort by date and "SELECT TOP 2 * from tablename..." and then read the values out into your program and compair them before showing the results. What your asking for is not really an agragate function that is it is not a summary, statistic or conditional data set of the whole... mmm that's not a very good explination, sorry...

    A DB is a set of data and everything you return is either a sub-set of data or data about data. What you want is a-b=c and that is a function. While SQL has some support for functions it is quite limited and what you want sounds like it might need to itterate (loop).

    However there is hope for you.

    If your are useing SQL server a stored procdure will do the job but you will need a fresh post over in the right forum
    If you are useing Access then VBa can handle the work at "run time" and you will need a fresh post in the VBa / Access section.
    If you are useing MySQL or some other DB you can probably use one or both answers and you will need to ask the relivant platform specifc question in the forum of your choice.

    Sorry that I couldn't answer more short windedly or give the finnished code or anything but I'm just in the mood to pepper my answer with answers to sub questions that I might not be arround to answer.
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    I don't know if this will work in ACCESS (or any other DBMS), but could you do it something like this? (I don't know the datatype of the dat and ti columns but you would have to concatentate them somehow to compare the date/time combination.)

    Code:
    select max(price) - min(price)
    from (select price from table1 t1
          where 2 > (select count(*)
                     from table1 t2
                     where date(t1.dat||t1.ti) < date(t2.dat||t2.ti)))

  4. #4
    Join Date
    Aug 2004
    Posts
    42
    In Sybase syntax:

    create table #p (d datetime, p int)

    insert #p (d, p) values ( '9/15/2004 10:17:20', 12)
    insert #p (d, p) values ( '9/15/2004 10:17:28', 32)
    insert #p (d, p) values ( '9/15/2004 10:17:31', 11)
    insert #p (d, p) values ( '9/16/2004 10:17:40', 12)
    insert #p (d, p) values ( '9/16/2004 10:17:45', 32)

    select * from #p

    select
    (select p from #p where d = (select max(d) from #p))
    - (select p from #p where d = (select max(d) from #p where d < (select max (d) from #p)))

Posting Permissions

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