SQL Query

  • Hi,

    I'm struggling to solve this problem....I hope will get a answer here.

    count, for each couple of stations, the number of unique trips on which the two stations in the corresponding couple are visited right after each other. Two stations are visited right after each other on the same trip if the difference between the corresponding stop numbers equals 1.

    thank you,

  • Consider people that have no idea about your data.  We don't know things like how a "unique trip" is identified, what defines a "corresponding couple" of stations, etc.  You'll also find that you'll get much faster and "code complete" answers if you do what is contained in the article at the first link in my signature line below.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thank you for your reply. here, i am explaining the problem.

    This is the Problem: Count, for each couple of stations, the number of unique trips on which the two stations in the corresponding couple are visited right after each other. Two stations are visited right after each other on the same trip if the difference between the corresponding stop numbers equals 1.

    In the result table, we expect three columns with corresponding datatype: station1 (varchar), station2 (varchar) and trips_with_consecutive_visits (integer). Make sure that each couple only appears once in the result table by enforcing that, for each row, the value (i.e. station name) in station1 is sorted alphabetically before the value (i.e. station name) in station2. As such, the count associated with the row of station1 and station2 should include the number of unique trips that (1) have a stop in station2 directly after station1 and (2) have a stop in station1 directly after station2. Do not list couples of stations that are never visited right after each other (i.e. couples with count 0)

    I have a dat set with,  code, stop_number, station_name

     

  • Ok... no interest from the OP.  Unsubscribing.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • OP must not be struggling that badly after all.

    ----------------------------------------------------

  • The OP had a SPAM post.

    @akhil you cannot explain this only in words. You need to provide some DDL of the table(s) structure and a few inserts to show sample data.

    This link explains more: https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply