SQL SERVER JOIN ID rows from one SELECT query to second SELECT query

Our fleet drivers accumulate hours in each type of rig that we operate.

This first query returns a list of driver IDs for a given route.

declare @day int = 14689; -- Today's "day" (# of days from 1/1/1980)
declare @dep varchar(3) = 'BXBU';
declare @carrier int = 1;
declare @flt int = 703;
declare @legcd char(1) = '';

SELECT  r.DRIVER_ID

FROM ROSTER r

JOIN CRROUTE cr ON (cr.CDATE = r.DUTYDAY 
                and cr.CROUTE = r.DUTYNO)

JOIN CrewLeg cl on (cl.CDATE = cr.CDATE 
                and cl.croute = cr.CROUTE 
                and cl.DAY = r.LEG_DAY 
                and cl.DEP = r.LEG_DEP 
                and cl.CARRIER = r.LEG_CARRIER 
                and cl.flt = r.LEG_FLT 
                and cl.LEGCD = r.LEG_LEGCD)

WHERE   r.leg_day = @day
AND     r.leg_dep = @dep
AND     r.leg_CARRIER = @carrier
AND     r.leg_flt = @flt
AND     r.leg_legcd = @legcd
AND     r.POS <= 2

GROUP BY r.ID,r.leg_day, r.leg_carrier, r.leg_flt, r.leg_legcd

Result:

1109
1209
1310

I need to feed these IDs into this second query, which returns the number of accumulated hours for a given Driver ID.

SELECT SUM(LT.BLON - LT.BLOF) as 'HOURSINTYPE'
FROM ROSTER R
INNER JOIN CREWLEG CL on R.LEG_DAY = CL.DAY AND R.LEG_CARRIER = CL.CARRIER AND R.LEG_FLT = CL.FLT AND R.LEG_DEP = CL.DEP AND R.LEG_LEGCD = CL.LEGCD AND R.DUTYDAY = CL.CDATE AND R.DUTYNO = CL.CROUTE
INNER JOIN LEGMAIN LM on R.LEG_DAY = LM.DAY AND R.LEG_CARRIER = LM.CARRIER AND R.LEG_FLT = LM.FLT AND R.LEG_DEP = LM.DEP AND R.LEG_LEGCD = LM.LEGCD
INNER JOIN LEGTIMES LT on R.LEG_DAY = LT.DAY AND R.LEG_CARRIER = LT.CARRIER AND R.LEG_FLT = LT.FLT AND R.LEG_DEP = LT.DEP AND R.LEG_LEGCD = LT.LEGCD
WHERE R.Driver_ID = {{Each_ID_From_1st_Query}} AND LEG_DAY <= @day AND PAX = 0 AND R.LEG_FLT > 0 AND LT.BLON != 0 AND LM.AC IN(8,10)

I’m trying to achieve:

ID      Hours
-----   -----
1109    109
1209    530
1310    76

EDIT: For clarification, if it helps, both queries join the ROSTER table to a different set of joined tables. The ID is in the ROSTER table in both queries. The first query selects the IDs, but the second query needs to use those IDs.

Both queries work fine independently of each other if I manually supply the specific ID I want for the second query.

I know how to do this with a cursor, but I know there must be some way to do this in a JOIN.

I tried several approaches:

WITH cte1 AS (
    FIRST QUERY
), cte2 AS (
    SECOND QUERY
) SELECT -- uhm.... what? I can't get the ID from cte1 into cte2.

And I tried various incarnation of:

(FIRST QUERY) a
JOIN 
(SECOND QUERY) b
WHERE a.Driver_ID = b.Driver_ID

But could never quite get the syntax to work.

I do not have access to modify the database in any way, so I cannot create VIEWs to do this.

This question is very similar to what I am trying to do, but it received no answer:

SQL Server – Join results of one query to first matching result from second query

Answer

You should be able to use an IN expression to use the results from the first query in the second. For example, using a CTE:

declare @day int = 14689; -- Today's "day" (# of days from 1/1/1980)
declare @dep varchar(3) = 'BXBU';
declare @carrier int = 1;
declare @flt int = 703;
declare @legcd char(1) = '';

WITH CTE AS (
    SELECT  r.DRIVER_ID
    FROM ROSTER r
    JOIN CRROUTE cr ON (cr.CDATE = r.DUTYDAY 
                    and cr.CROUTE = r.DUTYNO)
    JOIN CrewLeg cl on (cl.CDATE = cr.CDATE 
                    and cl.croute = cr.CROUTE 
                    and cl.DAY = r.LEG_DAY 
                    and cl.DEP = r.LEG_DEP 
                    and cl.CARRIER = r.LEG_CARRIER 
                    and cl.flt = r.LEG_FLT 
                    and cl.LEGCD = r.LEG_LEGCD)
    WHERE   r.leg_day = @day
    AND     r.leg_dep = @dep
    AND     r.leg_CARRIER = @carrier
    AND     r.leg_flt = @flt
    AND     r.leg_legcd = @legcd
    AND     r.POS <= 2
    GROUP BY r.ID,r.leg_day, r.leg_carrier, r.leg_flt, r.leg_legcd
)
SELECT R.Driver_ID, SUM(LT.BLON - LT.BLOF) as 'HOURSINTYPE'
FROM ROSTER R
INNER JOIN CREWLEG CL on R.LEG_DAY = CL.DAY AND R.LEG_CARRIER = CL.CARRIER AND R.LEG_FLT = CL.FLT AND R.LEG_DEP = CL.DEP AND R.LEG_LEGCD = CL.LEGCD AND R.DUTYDAY = CL.CDATE AND R.DUTYNO = CL.CROUTE
INNER JOIN LEGMAIN LM on R.LEG_DAY = LM.DAY AND R.LEG_CARRIER = LM.CARRIER AND R.LEG_FLT = LM.FLT AND R.LEG_DEP = LM.DEP AND R.LEG_LEGCD = LM.LEGCD
INNER JOIN LEGTIMES LT on R.LEG_DAY = LT.DAY AND R.LEG_CARRIER = LT.CARRIER AND R.LEG_FLT = LT.FLT AND R.LEG_DEP = LT.DEP AND R.LEG_LEGCD = LT.LEGCD
WHERE R.Driver_ID IN (SELECT DRIVER_ID FROM CTE)
  AND LEG_DAY <= @day
  AND PAX = 0
  AND R.LEG_FLT > 0
  AND LT.BLON != 0
  AND LM.AC IN(8,10)
GROUP BY R.Driver_ID