Combining two "eek" queries - 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 2008 Forums

Transact-SQL (2008)

Combining two "eek" queries

Author

Topic

simmer
Starting Member

5 Posts

Posted - 2011-05-29 : 16:00:10

I have two queries which both work on their own but need to combine them into one. One query returns a paged set of results (ie 10/25/50 at a time, reducing traffic). The other does lookups on another table to get the first/last of related data. Each query on its own is chunky enough and I can't get my novice head around combining the two!

Query 1:
ALTER PROCEDURE dbo.StoredProcedure1
(
@Headcode nvarchar(4) = NULL
)
AS
/* SET NOCOUNT ON */
WITH MySchedule AS (SELECT ScheduleID, MIN(Ordering) AS Origin, MAX(Ordering) AS Destination
FROM ScheduleLocation
GROUP BY ScheduleID), MyResults AS
(SELECT S.ScheduleID, O.LocationID AS OriginLocationID, O.DepartureTime AS departureTime, D.LocationID AS DestinationLocationID, D.ArrivalTime AS ArrivalTime
FROM MySchedule AS S INNER JOIN
ScheduleLocation AS O ON S.ScheduleID = O.ScheduleID AND S.Origin = O.Ordering INNER JOIN
ScheduleLocation AS D ON S.ScheduleID = D.ScheduleID AND S.Destination = D.Ordering)
SELECT R.ScheduleID, O.LocationCode AS Origin, R.departureTime AS Depart, D.LocationCode AS Destination, R.ArrivalTime AS Arrive, Schedule.Headcode,
Schedule.DateFrom, Schedule.DateTo, Schedule.RunDays
FROM MyResults AS R INNER JOIN
Location AS D ON R.DestinationLocationID = D.LocationID INNER JOIN
Location AS O ON R.OriginLocationID = O.LocationID INNER JOIN
Schedule ON R.ScheduleID = Schedule.ScheduleID)
RETURN

Query 2:
SELECT ScheduleID, Headcode, RunDays, DateFrom, '12:34' AS Depart, DateTo, '23:59' AS Arrive, 'A' AS Origin, 'B' AS Destination
FROM (SELECT ScheduleID, Headcode, DateFrom, DateTo, RunDays, ROW_NUMBER()
OVER(ORDER BY Headcode ASC, DateFrom ASC)
AS RecordRank FROM Schedule WHERE 1=1
(additional filters inserted here eg WHERE 1=1 AND Headcode LIKE '1A01')
)
AS ProductsWithRowNumber
WHERE RecordRank > @startRowIndex
AND RecordRank <= @startRowIndex + @maximumRows)

As you can see from the second query, I've temporarily put some dummy values in the SELECT (ie Depart, Arrive, Origin, Destination) which would be replaced by the final SELECT in the first query (O.LocationCode AS Origin etc).

I don't think you need to know the table detail but if you do, let me know. This little hobby is proving expensive time-wise!

Many thanks.

lappin
Posting Yak Master

182 Posts

Posted - 2011-06-01 : 11:16:51

If you take existing code but create a function instead of procedure you should be able to join the results of each.
e.g.
CREATE Function dbo.fn1
(
@Headcode nvarchar(4) = NULL
)
AS
/* SET NOCOUNT ON */
WITH MySchedule AS (SELECT ScheduleID, MIN(Ordering) AS Origin, MAX(Ordering) AS Destination
FROM ScheduleLocation
GROUP BY ScheduleID), MyResults AS
(SELECT S.ScheduleID, O.LocationID AS OriginLocationID, O.DepartureTime AS departureTime, D.LocationID AS DestinationLocationID, D.ArrivalTime AS ArrivalTime
FROM MySchedule AS S INNER JOIN
ScheduleLocation AS O ON S.ScheduleID = O.ScheduleID AND S.Origin = O.Ordering INNER JOIN
ScheduleLocation AS D ON S.ScheduleID = D.ScheduleID AND S.Destination = D.Ordering)
SELECT R.ScheduleID, O.LocationCode AS Origin, R.departureTime AS Depart, D.LocationCode AS Destination, R.ArrivalTime AS Arrive, Schedule.Headcode,
Schedule.DateFrom, Schedule.DateTo, Schedule.RunDays
FROM MyResults AS R INNER JOIN
Location AS D ON R.DestinationLocationID = D.LocationID INNER JOIN
Location AS O ON R.OriginLocationID = O.LocationID INNER JOIN
Schedule ON R.ScheduleID = Schedule.ScheduleID)
RETURN

Query 2:
CREATE Function dbo.fn2 -- OR create a VIEW
AS
SELECT ScheduleID, Headcode, RunDays, DateFrom, '12:34' AS Depart, DateTo, '23:59' AS Arrive, 'A' AS Origin, 'B' AS Destination
FROM (SELECT ScheduleID, Headcode, DateFrom, DateTo, RunDays, ROW_NUMBER()
OVER(ORDER BY Headcode ASC, DateFrom ASC)
AS RecordRank FROM Schedule WHERE 1=1
(additional filters inserted here eg WHERE 1=1 AND Headcode LIKE '1A01')
)
AS ProductsWithRowNumber
WHERE RecordRank > @startRowIndex
AND RecordRank <= @startRowIndex + @maximumRows)

Select * from fn1(hc)
join fn2 on fn1.ScheduleID = fn2.ScheduleID etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources