t-sql cross join - using one column as parameter in other query-CodePudding

The main query

select case when 
       aCalculatedDatefield > XMONTH then 1 else null 
       end as V
from 
   ( some nested queries where the aCalculatedDatefield is created)

and I want to run it for some months in year 2022

select XMONTH from year2020 where XMONTH = condition that returns more than one value

The result should be sth like

 --------------------------------------
Month | V
 --------------------------------------
JAN   | 1
 --------------------------------------
APR   | null
 --------------------------------------

I think this query should be run with cross join, but I don't know how to use XMONTH from the second query in the main query in SQL Server.

CodePudding user response：

This query works for me when I test it using MS SQL Server.

select MONTH, case when 
       aCalculatedDatefield > (select XMONTH 
                               from year2020 
                               where XMONTH = 202001) 
        then 1 else null 
       end as V
from test

 Result:
 MONTH  V
 Jan    1
 Feb    (null)

 http://sqlfiddle.com/#!18/5d33a/1

CodePudding user response：

Not sure to underestand your problem without sample data but here a sample of what u can achieve with CTE query

WITH CTE_calculatedTable AS 
(
    SELECT 202002 aCalculatedDatefield, 'Jan' MONTH
    UNION ALL 
    SELECT 202000 aCalculatedDatefield, 'Feb' MONTH
 ) 
select MONTH, case when 
       aCalculatedDatefield > (select XMONTH from year2020 where XMONTH = 202001) then 1 else null 
       end as V
from CTE_calculatedTable

http://sqlfiddle.com/#!18/30551/7

The UNION ALL is just a sample, you can replace by whatever you want, even a

select * from (VALUES (1, 'Jan'), (2, 'Feb')) AS tmpTab(aCalculatedDatefield, MONTH)