Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2006
    Posts
    2

    Red face Unanswered: chronological events

    Hi,

    I 've been trying for a week now to sort records in a chronological way by taking into account two fields. Just cant get that done can anyone help ?

    The sample is as below

    DATE_In DATE_Out
    4/8/2006 26/7/2006
    26/7/2006 25/7/2006
    26/7/2006 25/7/2006
    26/7/2006 27/7/2006
    24/7/2006 27/7/2006
    26/7/2006 26/7/2006
    26/7/2006 25/7/2006
    26/7/2006 26/7/2006
    26/7/2006 27/7/2006
    1/8/2006 1/8/2006
    26/7/2006 1/8/2006
    26/7/2006 26/7/2006
    28/7/2006 28/7/2006
    24/7/2006 4/8/2006
    1/8/2006 4/8/2006
    26/7/2006 1/8/2006
    31/7/2006 1/8/2006
    24/7/2006 26/7/2006
    31/7/2006 28/7/2006
    4/8/2006 31/7/2006
    2/8/2006 27/7/2006
    4/8/2006 3/8/2006
    30/7/2006 26/7/2006

    Just to do this ? Please help

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what result do you want?

    why doesn't
    ORDER BY Date_in, Date_out
    solve it?

    izy


    ? are these datetimes or ????????
    if you have decided to use string, you have some additional work to do
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2006
    Posts
    2

    Unhappy Choronological

    The format is date/time
    When i run the statement :

    SELECT TempImport.DATE_In, TempImport.DATE_out
    FROM TempImport
    ORDER BY TempImport.DATE_In, TempImport.DATE_out;

    I get these result... which is actually the wrong result....
    DATE_In DATE_out
    24/7/2006 26/7/2006
    24/7/2006 27/7/2006
    24/7/2006 4/8/2006
    26/7/2006 25/7/2006
    26/7/2006 25/7/2006
    26/7/2006 25/7/2006
    26/7/2006 26/7/2006
    26/7/2006 26/7/2006
    26/7/2006 26/7/2006
    26/7/2006 27/7/2006
    26/7/2006 27/7/2006
    26/7/2006 1/8/2006
    26/7/2006 1/8/2006
    28/7/2006 28/7/2006
    30/7/2006 26/7/2006
    31/7/2006 28/7/2006
    31/7/2006 1/8/2006
    1/8/2006 1/8/2006
    1/8/2006 4/8/2006
    2/8/2006 27/7/2006
    4/8/2006 26/7/2006
    4/8/2006 31/7/2006
    4/8/2006 3/8/2006


    It's suppose to sort according to dates in a choronologicaly. The bolded one is wrong results. its suppose to be this way :

    DATE_In DATE_out
    24/7/2006 26/7/2006
    24/7/2006 27/7/2006
    24/7/2006 4/8/2006
    26/7/2006 25/7/2006
    26/7/2006 25/7/2006
    26/7/2006 25/7/2006
    26/7/2006 26/7/2006
    26/7/2006 26/7/2006
    26/7/2006 26/7/2006
    26/7/2006 27/7/2006
    26/7/2006 27/7/2006
    26/7/2006 1/8/2006
    26/7/2006 1/8/2006
    4/8/2006 26/7/2006
    30/7/2006 26/7/2006
    28/7/2006 28/7/2006
    31/7/2006 28/7/2006
    31/7/2006 1/8/2006
    1/8/2006 1/8/2006
    1/8/2006 4/8/2006
    2/8/2006 27/7/2006
    4/8/2006 31/7/2006
    4/8/2006 3/8/2006

    The dates are in two different fields. I am supposed to sort them in choronologically.

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I can't see any logic that would sort them exactly as you show. If you can explain your logic then maybe there is a way.

    Here's what I think...

    Code:
    SELECT TempImport.DATE_In, TempImport.DATE_out
    FROM TempImport
    ORDER BY IIf([date_In]<[Date_Out],[Date_In],[Date_out]), IIf([date_In]<[Date_Out],[Date_out],[Date_In])
    Produces...

    24/07/2006 26/07/2006
    24/07/2006 27/07/2006
    24/07/2006 04/08/2006
    26/07/2006 25/07/2006
    26/07/2006 25/07/2006
    26/07/2006 25/07/2006
    26/07/2006 26/07/2006
    26/07/2006 26/07/2006
    26/07/2006 26/07/2006
    26/07/2006 27/07/2006
    26/07/2006 27/07/2006
    30/07/2006 26/07/2006
    26/07/2006 01/08/2006
    26/07/2006 01/08/2006
    04/08/2006 26/07/2006
    02/08/2006 27/07/2006
    28/07/2006 28/07/2006
    31/07/2006 28/07/2006
    31/07/2006 01/08/2006
    04/08/2006 31/07/2006
    01/08/2006 01/08/2006
    01/08/2006 04/08/2006
    04/08/2006 03/08/2006

    Note that 30/07/2006 26/07/2006 appears in 12th position in my book.
    hth
    Chris

  5. #5
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by mBRaj
    It's suppose to sort according to dates in a choronologicaly. The bolded one is wrong results. its suppose to be this way :

    DATE_In DATE_out
    24/7/2006 26/7/2006
    24/7/2006 27/7/2006
    24/7/2006 4/8/2006
    26/7/2006 25/7/2006
    26/7/2006 25/7/2006
    26/7/2006 25/7/2006
    26/7/2006 26/7/2006
    26/7/2006 26/7/2006
    26/7/2006 26/7/2006
    26/7/2006 27/7/2006
    26/7/2006 27/7/2006
    26/7/2006 1/8/2006
    26/7/2006 1/8/2006
    4/8/2006 26/7/2006
    30/7/2006 26/7/2006
    28/7/2006 28/7/2006
    31/7/2006 28/7/2006
    31/7/2006 1/8/2006
    1/8/2006 1/8/2006
    1/8/2006 4/8/2006
    2/8/2006 27/7/2006
    4/8/2006 31/7/2006
    4/8/2006 3/8/2006

    The dates are in two different fields. I am supposed to sort them in choronologically.
    How does the line in red get there ??? Sorry but I just can't see any consistent logic to your list. If you want to include the 2nd column in the chronology then the line in red should appear after 26/7/2006 not after 1/8/2006 4/8/2006

    Chris

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Dump question here

    is the Date_in, Date_out stored in the database as datevalue or text

    Don't know all the data value inthe database

    but If Date_in, Date_out are in the same record

    24/7/2006 26/7/2006 a record
    24/7/2006 27/7/2006 a record
    24/7/2006 4/8/2006 a record
    26/7/2006 25/7/2006 a record
    26/7/2006 25/7/2006 a record

    Just sorting by the date_out wouldn't that fix it ???
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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