Select with multiple cases and join time out

Sorry for the title, I am not sure what to put there.

I am trying to summarize data per year with cases & select. I am not sure this is the right way to do it. In my cases, it can be select with multiple join to check data on that year & month.

This is sample query for January. There are 12 query (Jan-Dec) and I union them all. If data become bigger, I thought the joined table is taking too long and my query time out.

SELECT TOP 1 1 AS INT_MONTH, 'Jan' AS STR_MONTH,
STATUS = CASE WHEN EXISTS (SELECT TOP 1 FIELD FROM TB
WHERE MONTH=1 & YEAR=@year) THEN 'Ready' ELSE 'Not Yet' END,
CREATED_BY = CASE WHEN EXISTS (SELECT TOP 1 FIELD FROM TB
WHERE MONTH=1 & YEAR=@year)
THEN 'Ready' ELSE '-' END,
CREATED_DT = CASE WHEN EXISTS (SELECT TOP 1 FIELD FROM TB
WHERE MONTH=1 & YEAR=@year)
THEN (SELECT TOP 1 DT FROM FIELD WHERE MONTH=1 & YEAR=@year) ELSE NULL END,
DATA1 = CASE WHEN EXISTS(SELECT TOP 1 FIELD FROM TB INNER JOIN TB2 ON TB2.F1 = TB.F1 WHERE TB2.MONTH=1 & TB2.YEAR=@year)
THEN 'Ready' ELSE 'Not Ready' END,
DATA2 = CASE WHEN EXISTS (SELECT TOP 1 FIELD FROM TB INNER JOIN TB3 ON TB2.F1 = TB.F1 WHERE TB3.MONTH=1 & TB3.YEAR=@year)
THEN 'Ready' ELSE 'Not Ready' END


In my real query there are 3 inner join inside the cases. I simplified it here to explain the problem.

Thanks in advance

TOP 1

WHEN EXISTS

UNION

2 Answers
2

You can use a month table for not use union

;WITH M AS (
SELECT * FROM ( VALUES(1,'Jan'),(2,'Feb'),(3,'Mar'),(4,'Apr'),(5,'May'),(6,'Jın'),(7,'Jul'),(8,'Aug'),(9,'Sep'),(10,'Oct'),(11,'Nov'),(12,'Dec') ) M(INT_MONTH, STR_MONTH)
)
SELECT
M.INT_MONTH,
M.STR_MONTH,
CASE WHEN TB.FIELD IS NULL THEN 'Not Yet' ELSE 'Ready' END AS STATUS,
CASE WHEN TB.FIELD IS NULL THEN '-' ELSE 'Ready' END AS CREATED_BY,
CASE WHEN TB.FIELD IS NULL THEN NULL ELSE DT END AS CREATED_DT,
CASE WHEN TB2.F1 IS NULL THEN 'Not Ready' ELSE 'Ready' END AS DATA1,
CASE WHEN TB3.F1 IS NULL THEN 'Not Ready' ELSE 'Ready' END AS DATA2
FROM M
LEFT JOIN TB ON M.INT_MONTH = TB.MONTH AND TB.YEAR=@year
LEFT JOIN TB2 ON TB2.F1 = TB.F1 AND M.INT_MONTH = TB2.MONTH AND TB2.YEAR=@year
LEFT JOIN TB3 ON TB3.F1 = TB.F1 AND M.INT_MONTH = TB3.MONTH AND TB3.YEAR=@year


From what I understand from your query, your query can be modified with JOINS. Please find below corresponding query. You can get an idea about how to change your query accordingly.

Also, I would recommend to change the column names YEAR, MONTH, as they are keywords in sql server. I have put square brackets around them to avoid syntax errors.

SELECT TOP 1 1 AS INT_MONTH, 'Jan' AS STR_MONTH,
STATUS = CASE WHEN t.Field IS NOT NULL THEN 'Ready' ELSE 'Not Yet' END,
CREATED_BY = CASE WHEN t.Field IS NOT NULL THEN 'Ready' ELSE '-' END,
CREATED_DT = CASE WHEN t.Field IS NOT NULL
THEN d.DT ELSE NULL END,
DATA1 = CASE WHEN t2.F1 IS NOT NULL
THEN 'Ready' ELSE 'Not Ready' END,
DATA2 = CASE WHEN t2.F1 IS NOT NULL
THEN 'Ready' ELSE 'Not Ready' END
FROM TB AS t
LEFT JOIN DT as d
ON t.[Month] = d.[Month] and t.[year] = d.[year]
LEFT JOIN TB2 AS t2
ON t2.F1 = t.F1 and t2.[month] = t.[month] and t2.[year] = t.[year]
WHERE [Month] = 1 AND [YEAR] = @year


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.