Subquery condition from main query select

Question

I want to do some subquery select where its condition is taken from main query select.

Here's my query :

SELECT 
    DISTINCT MONTH(datetime_end) as B,
    (
        SELECT 
                SUM(DATEDIFF(DAY, datetime_start, datetime_end))
            FROM 
                    [simrke].[trx_medical_ruang] MR,
                    [simrke].[trx_ruang] R
            WHERE
                MR.ruang_cd = R.ruang_cd AND 
                R.ruang_nm like 'BAKUNG%'   AND
                MONTH(datetime_end) = B
    )
FROM 
        [simrke].[trx_medical_ruang] M
ORDER BY B
    ;

Where sub query select condition MONTH(datetime_end) = B is from previous select but I got this error message :

[Err] 42S22 - [SQL Server]Invalid column name 'B'.

What is a proper way to point to first B select?

Answer 1

Try this : MONTH(MR.datetime_end) = MONTH(M.datetime_end)

SELECT 
    DISTINCT MONTH(datetime_end) as B,
    (
        SELECT 
                SUM(DATEDIFF(DAY, datetime_start, datetime_end))
            FROM 
                    [simrke].[trx_medical_ruang] MR,
                    [simrke].[trx_ruang] R
            WHERE
                MR.ruang_cd = R.ruang_cd AND 
                R.ruang_nm like 'BAKUNG%'   AND
                MONTH(MR.datetime_end) = MONTH(M.datetime_end)
    )
FROM 
        [simrke].[trx_medical_ruang] M
ORDER BY B