How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ATLPROG Your Own Question
ATLPROG
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
44910485
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

How do I calculate overlapping times using an MSSQL Query? I

Customer Question

How do I calculate overlapping times using an MSSQL Query? I have a table that has a row for each rider on a bus. That table has In and Out punches stored. I need to write a query that calculates the amount of time an individual was alone on a bus, verses when others were present based on the In and Out punches.
Submitted: 1 year ago.
Category: Programming
Expert:  Satchi replied 1 year ago.

Hi , Welcome to justanswer.com

Can you share me sample table data ?

Customer: replied 1 year ago.
Please see the attached Sample Data CSV.
Expert:  Satchi replied 1 year ago.

What is the PID ?

Customer: replied 1 year ago.
It is an ID number for the person who the pass belongs to.
Expert:  Satchi replied 1 year ago.

statusDate pid inPunch outPunch scanID sn

What are the above fields ?

Customer: replied 1 year ago.
statusDate = datetime of the ride (shoud mirror inPunch) (this links some of the data on the backend.
pid = person Identifier
inPunch = time of the punch in
outPunch = time of the punch out
scanID = the scanner of where the punch took place (one scanner per vehicle)
sn = another identifier for the vehicle they were on.
Expert:  Satchi replied 1 year ago.

statusDate = datetime of the ride this is in which format ?

Customer: replied 1 year ago.
I just realized there is a missing field. I also have a pre-calculated rideTime. Not sure if you want that. Its just a datediff on inPunch and outPunch. I am attaching an updated spreadsheet.statusDate =smalldatetime
Customer: replied 1 year ago.
Sorry was missing a header. Here it is correct with rideLength which is minutes of the ride. I need to know how long that individual was on the bus alone (aloneTime) and how long they were with other (groupTime).
Expert:  Satchi replied 1 year ago.

Ok

Can you make a sample output data for a test PersonID

Customer: replied 1 year ago.
I am sorry I dont understand the question.
Expert:  Satchi replied 1 year ago.

Yes ..

Can you please send me the required output for one individual ?

Customer: replied 1 year ago.
Let me clarify, I need to know for that specific ride how long the individual was alone or in a group. I can link it back up to the person late.
Expert:  Satchi replied 1 year ago.

Yes thats what I am saying please provide me a just test data output for a specific ride ?

Customer: replied 1 year ago.
That output is for the rides. One row=one ride. the scanID should link them to an individual vehicle.
Expert:  Satchi replied 1 year ago.

It seems the logic will take some time for me .

In the mean time I will open the question to other experts to help you more on this .

Thanks

Customer: replied 1 year ago.
Thats fine. This was somewhat urgent and I needed a rapid answer. I went ahead and found help outside here. How do I close this question?

Related Programming Questions