Help on Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help on Query

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-05-03 : 04:06:59

Hi Team,

Following is a schema for a ticketing machine in a bus service.

Could you please help to write a query that will find the following ?

Find the durations, between which the number of passengers is more than 2.

[In the example below, the expected result is (L1 – L2) and (L2-L3)]

Note: Suppose there are more than two travellers between L7 and L10, we need to display as L7-L8, L8-L9 and L9-L10

CREATE TABLE #Location(LocID INT, LocName VARCHAR(50))
CREATE TABLE #Trip(TripID INT, BusID INT, RunDate DateTime)
CREATE TABLE #Route_TripStops (RouteID INT,TripID INT, LocID INT,IsStartPoint BIT, IsEndPoint BIT,RelativePostionFromStart INT)
CREATE TABLE #Tickets(TicketID INT, Passenger VARCHAR(50), TripID INT, BoardingLocationID INT, DestinationLocationID INT)

INSERT INTO #Location VALUES (1, 'L1')
INSERT INTO #Location VALUES (2, 'L2')
INSERT INTO #Location VALUES (3, 'L3')
INSERT INTO #Location VALUES (4, 'L4')

INSERT INTO #Trip VALUES (101,555,'1/1/2010')

INSERT INTO #Route_TripStops VALUES (1,101,1,'TRUE','FALSE',1)
INSERT INTO #Route_TripStops VALUES (2,101,2,'FALSE','FALSE',2)
INSERT INTO #Route_TripStops VALUES (3,101,3,'FALSE','FALSE',3)
INSERT INTO #Route_TripStops VALUES (4,101,4,'FALSE','TRUE',4)

INSERT INTO #Tickets VALUES (1,'P1',101,1,2)
INSERT INTO #Tickets VALUES (2,'P2',101,1,3)
INSERT INTO #Tickets VALUES (3,'P3',101,2,4)
INSERT INTO #Tickets VALUES (4,'P4',101,1,4)

Thanks
Lijo Cheeran Joseph

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-03 : 05:16:20

[code]SELECT *
FROM #Tickets AS t1
INNER JOIN #Tickets AS t2 ON t2.TripID = t1.TripID
AND t2.TicketID <> t1.TicketID
WHERE t1.BoardingLocationID <= t2.DestinationLocationID
AND t1.DestinationLocationID >= t2.BoardingLocationID[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-03 : 05:25:32

[code]DECLARE @Tickets TABLE
(
TicketID INT,
TripID INT,
BoardingLocationID INT,
DestinationLocationID INT
)

INSERT @Tickets
VALUES (1, 101, 1, 2),
(2, 101, 1, 3),
(3, 101, 2, 4),
(4, 101, 1, 4)

SELECT t1.*,
NULL AS [ ],
t2.*
FROM @Tickets AS t1
INNER JOIN @Tickets AS t2 ON t2.TripID = t1.TripID
AND t2.TicketID > t1.TicketID
WHERE t1.BoardingLocationID < t2.DestinationLocationID
AND t1.DestinationLocationID > t2.BoardingLocationID
ORDER BY t1.TicketID,
t2.TicketID

TicketID TripID BoardingLocationID DestinationLocationID TicketID TripID BoardingLocationID DestinationLocationID Comment
1 101 1 2 2 101 1 3 They share Location 1 and 2
1 101 1 2 4 101 1 4 They share Location 1 and 2
2 101 1 3 3 101 2 4 They share Location 2 and 3
2 101 1 3 4 101 1 4 They share Location 1, 2 and 3
3 101 2 4 4 101 1 4 They share Location 2, 3 and 4[/code]

N 56°04'39.26"
E 12°55'05.63"

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-05-03 : 05:25:39

Thanks for your resposne.

However, I am unable to figure out the expected result [(L1 – L2) and (L2-L3)] from the logic.

Could you please provide the query that will give the expected result?

Note: Suppose there are more than two travellers between L7 and L10, we need to display as L7-L8, L8-L9 and L9-L10

Thanks
Lijo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-03 : 08:08:48

[code];WITH cteYak(StartLoc, EndLoc)
AS (
SELECT d.StartLoc,
d.EndLoc
FROM (
SELECT rts1.TripID,
rts1.LocID AS StartLoc,
rts1.RelativePostionFromStart AS Starting,
rts2.LocID AS EndLoc,
rts2.RelativePostionFromStart AS Ending
FROM #Route_TripStops AS rts1
LEFT JOIN #Route_TripStops AS rts2 ON rts2.TripID = rts1.TripID
AND rts2.RelativePostionFromStart = rts1.RelativePostionFromStart + 1
WHERE rts2.TripID IS NOT NULL
) AS d
INNER JOIN #Tickets AS t ON t.TripID = d.TripID
WHERE t.BoardingLocationID = d.StartLoc
OR t.DestinationLocationID = d.EndLoc
GROUP BY d.StartLoc,
d.EndLoc
HAVING COUNT(*) > 1
)
SELECT l1.LocName,
l2.LocName
FROM cteYak AS y
INNER JOIN #Location AS l1 ON l1.LocID = y.StartLoc
INNER JOIN #Location AS l2 ON l2.LocID = y.EndLoc[/code]

N 56°04'39.26"
E 12°55'05.63"

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-05-03 : 11:36:37

Sorry; the query does not give the expected result. It gives an unwanted row also (L3-L4)

Any other thoughts?

Thanks
Lijo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-03 : 12:15:18

How about you try for yourself with the two different suggestions given to you?

N 56°04'39.26"
E 12°55'05.63"

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-03 : 13:10:20

Try this one:

;WITH CTE as (
	SELECT r.TripID, l.LocName, 
		(SELECT COUNT(*)
		FROM #Tickets t
		WHERE t.BoardingLocationID <= l.LocID
			AND t.TripID = r.TripID) -
		(SELECT COUNT(*)
		FROM #Tickets t
		WHERE t.DestinationLocationID <= l.LocID
			AND t.TripID = r.TripID) AS Passengers, 
		ROW_NUMBER() OVER (ORDER BY r.TripID, l.LocID) AS Row
	FROM #Location l
	INNER JOIN #Route_TripStops r
		ON l.LocID = r.LocID
)

SELECT l1.TripID, l1.LocName AS StartLocation, l2.LocName AS DestLocation, l1.Passengers
FROM CTE l1
INNER JOIN CTE l2
	ON l1.TripID = l2.TripID
	AND l1.Row = l2.Row - 1
WHERE l1.Passengers > 2

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-05-03 : 13:11:22

@Peso,
First of all let me thank you for your responses.

I tried the following before you posted the second version. But it did not work.

SELECT S1.LocName [Point1],S2.LocName [Point2]
FROM #Location S1
INNER JOIN #Location S2 ON S2.LocID = (S1.LocID +1)
WHERE S1.LocID IN
(
SELECT t1.BoardingLocationID
FROM #Tickets AS t1
INNER JOIN #Tickets AS t2
ON t2.TripID = t1.TripID
AND t2.TicketID <> t1.TicketID
WHERE t1.BoardingLocationID < t2.DestinationLocationID
GROUP BY t1.BoardingLocationID
HAVING COUNT(*) > 1
)

UNION

SELECT S2.LocName [Point1] ,S1.LocName [Point2]
FROM #Location S1
INNER JOIN #Location S2 ON S2.LocID = (S1.LocID -1)
WHERE S1.LocID IN
(
SELECT t1.DestinationLocationID
FROM #Tickets AS t1
INNER JOIN #Tickets AS t2
ON t2.TripID = t1.TripID
AND t2.TicketID <> t1.TicketID
WHERE t1.DestinationLocationID > t2.BoardingLocationID
GROUP BY t1.DestinationLocationID
HAVING COUNT(*) > 1
)

I think, with the current schema, it is not posible to meet the requirement.

Anyone else thinks differently?

Thanks
Lijo

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-03 : 13:15:51

quote:
Originally posted by Lijo Cheeran Joseph
I think, with the current schema, it is not posible to meet the requirement.

Anyone else thinks differently?

Did you try mine?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-05-03 : 14:24:11

quote:

Did you try mine?

Hi DBA,

Thanks. You proved two things -

If there is a will, there is a way
If there is data in database, there is a query to retrieve it.

I modified the query slighlty. Is there in bug in this?

DECLARE @TmpPassengersInLocations TABLE ( TripID INT,LocName VARCHAR(50), PGotInBeforeOrOnLoc INT, PGotDownBeforeOrOnLoc INT, PassengersCrossingLoc INT,RowNumber INT)
INSERT INTO @TmpPassengersInLocations
SELECT R.TripID,
L.LocName,
( SELECT COUNT(*)
FROM #Tickets t
INNER JOIN #Route_TripStops TL ON TL.LocID = t.BoardingLocationID
WHERE TL.RelativePostionFromStart <= R.RelativePostionFromStart AND t.TripID = r.TripID
)AS [ Passengers Got in before (or on) the location],
( SELECT COUNT(*)
FROM #Tickets t
INNER JOIN #Route_TripStops TL ON TL.LocID = t.DestinationLocationID
WHERE TL.RelativePostionFromStart <= R.RelativePostionFromStart AND t.TripID = r.TripID
) AS [Passengers Got down before (or on) the location],
(SELECT COUNT(*) FROM #Tickets t INNER JOIN #Route_TripStops TL ON TL.LocID = t.BoardingLocationID WHERE TL.RelativePostionFromStart <= R.RelativePostionFromStart AND t.TripID = r.TripID)
-
(SELECT COUNT(*) FROM #Tickets t INNER JOIN #Route_TripStops TL ON TL.LocID = t.DestinationLocationID WHERE TL.RelativePostionFromStart <= R.RelativePostionFromStart AND t.TripID = r.TripID) AS [PassengersCrossingLoc],
ROW_NUMBER() OVER (ORDER BY r.TripID, l.LocID) AS Row
FROM #Location L
INNER JOIN #Route_TripStops R
ON L.LocID = R.LocID

SELECT l1.TripID, l1.LocName AS PointA, l2.LocName AS PointB, l1.PassengersCrossingLoc
FROM @TmpPassengersInLocations l1
INNER JOIN @TmpPassengersInLocations l2
ON l1.TripID = l2.TripID
AND l2.RowNumber = l1.RowNumber + 1
WHERE l1.PassengersCrossingLoc > 2

Thanks
Lijo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-03 : 16:29:35

Why don't you want L3-L4? This distance is shared by Passenger 3 and Passenger 4, according to your sample data.
See sample records P3 and P4.

L1-L2 -> Shared by passengers 1, 2 and 4
L2-L3 -> Shared by passengers 2, 3 and 4
L3-L4 -> Shared by passengers 3 and 4

Or you want distances shared be MORE than 2 passengers (at least 3 passengers)?
Not by two passengers or more ?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-03 : 16:33:19

This is from my response 05/03/2010 : 08:08:48

;WITH cteYak(StartLoc, EndLoc)
AS (
	SELECT		d.StartLoc,
			d.EndLoc
	FROM		(
				SELECT		rts1.TripID,
						rts1.LocID AS StartLoc,
						rts1.RelativePostionFromStart AS Starting,
						rts2.LocID AS EndLoc,
						rts2.RelativePostionFromStart AS Ending
				FROM		#Route_TripStops AS rts1
				LEFT JOIN	#Route_TripStops AS rts2 ON rts2.TripID = rts1.TripID
							AND rts2.RelativePostionFromStart = rts1.RelativePostionFromStart + 1
				WHERE		rts2.TripID IS NOT NULL
			) AS d
	INNER JOIN	#Tickets AS t ON t.TripID = d.TripID
	WHERE		t.BoardingLocationID < d.EndLoc
			AND t.DestinationLocationID > d.StartLoc
	GROUP BY	d.StartLoc,
			d.EndLoc
	HAVING		COUNT(*) > 2	-- Here is were you change the number of concurrent passengers
)
SELECT		l1.LocName,
		l2.LocName
FROM		cteYak AS y
INNER JOIN	#Location AS l1 ON l1.LocID = y.StartLoc
INNER JOIN	#Location AS l2 ON l2.LocID = y.EndLoc

N 56°04'39.26"
E 12°55'05.63"

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-03 : 17:09:07

quote:

Originally posted by Peso

This is from my response 05/03/2010 : 08:08:48

;WITH cteYak(StartLoc, EndLoc)
AS (
	SELECT		d.StartLoc,
			d.EndLoc
	FROM		(
				SELECT		rts1.TripID,
						rts1.LocID AS StartLoc,
						rts1.RelativePostionFromStart AS Starting,
						rts2.LocID AS EndLoc,
						rts2.RelativePostionFromStart AS Ending
				FROM		#Route_TripStops AS rts1
				LEFT JOIN	#Route_TripStops AS rts2 ON rts2.TripID = rts1.TripID
							AND rts2.RelativePostionFromStart = rts1.RelativePostionFromStart + 1
				WHERE		rts2.TripID IS NOT NULL
			) AS d
	INNER JOIN	#Tickets AS t ON t.TripID = d.TripID
	WHERE		t.BoardingLocationID < d.EndLoc
			AND t.DestinationLocationID > d.StartLoc
	GROUP BY	d.StartLoc,
			d.EndLoc
	HAVING		COUNT(*) > 2	-- Here is were you change the number of concurrent passengers
)
SELECT		l1.LocName,
		l2.LocName
FROM		cteYak AS y
INNER JOIN	#Location AS l1 ON l1.LocID = y.StartLoc
INNER JOIN	#Location AS l2 ON l2.LocID = y.EndLoc

N 56°04'39.26"
E 12°55'05.63"

If you want to do it that way, then you'll need to do this to allow for multiple trips.

;WITH cteYak(TripID, StartLoc, EndLoc)
AS (
	SELECT		d.TripID, d.StartLoc,
			d.EndLoc
	FROM		(
				SELECT		rts1.TripID,
						rts1.LocID AS StartLoc,
						rts1.RelativePostionFromStart AS Starting,
						rts2.LocID AS EndLoc,
						rts2.RelativePostionFromStart AS Ending
				FROM		#Route_TripStops AS rts1
				LEFT JOIN	#Route_TripStops AS rts2 ON rts2.TripID = rts1.TripID
							AND rts2.RelativePostionFromStart = rts1.RelativePostionFromStart + 1
				WHERE		rts2.TripID IS NOT NULL
			) AS d
	INNER JOIN	#Tickets AS t ON t.TripID = d.TripID
	WHERE		t.BoardingLocationID < d.EndLoc
			AND t.DestinationLocationID > d.StartLoc
	GROUP BY	d.TripID, d.StartLoc,
			d.EndLoc
	HAVING		COUNT(*) > 2	-- Here is were you change the number of concurrent passengers
)
SELECT		TripID, l1.LocName,
		l2.LocName
FROM		cteYak AS y
INNER JOIN	#Location AS l1 ON l1.LocID = y.StartLoc
INNER JOIN	#Location AS l2 ON l2.LocID = y.EndLoc

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-05-04 : 05:23:39

Good point!

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources