SQL Query

Question

SQL Query

akhil

SSC Veteran

Points: 274
More actions
August 17, 2022 at 3:45 pm

#4077481

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,

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

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 1

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)

akhil SSC Veteran Points: 274 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

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)

MMartin1 One Orange Chip Points: 27764 More actions · Answer 4

OP must not be struggling that badly after all.

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

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 5

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