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

Question

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.

You need a condition like `WHERE YEAR(the_date) = 2022 AND MONTH(the_date) IN (1, 4)` — Olivier Jacot-Descombes, Aug 16 '22 at 14:53
would you please post your answer with sample? my mind is locked — Mironline, Aug 16 '22 at 15:00
We have not enough information to answer you question. What is the main query and the second query. Show us the queries and some sample input data. — Olivier Jacot-Descombes, Aug 16 '22 at 15:07
You can construct a table of months and cross join this to your query but it won't feed into your query so you won't get different output for different months as per your desired output. — planetmatt, Aug 16 '22 at 15:16
So you need to explain to us how a generic month joins to your data. — planetmatt, Aug 16 '22 at 15:55

score 0 · Answer 1 · answered Aug 16 '22 at 15:07

0

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

answered Aug 16 '22 at 15:07

jose_bacoy

12,227
1
20
38

Thank you for your quick response. your `select from year2020` returns only **one** value, but in my case it returns more than one value (like feb,march,.....). The point is I want to run the `main query` and union all results as much as records that select year2020 returns! does it make sense? – Mironline Aug 16 '22 at 15:17
how would you join year2020 table with nested queries if year2020 is returning more than one value? please give us sample data for "nested queries" and year2020 so that we can visualize it. thanks. – jose_bacoy Aug 16 '22 at 15:36

score 0 · Answer 2 · answered Aug 16 '22 at 15:48

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)

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

2 Answers2