Join On clause with an IN statement
-
Good Morning Gents,
I've come across a piece of code which i have never seen before, and was hoping that someone could help explain it to me.
ON T.CT_YEAR in(
case
.[DBO].[FN_GET_YEAR]
(
CAST
(
C.[YEAR] AS VARCHAR(4)
)
+ '-01-01'
)
when 'L' then 'L'
when 'C' then 'C'
when 'O' then 'O'
else 'N'
end
)
AND T.CH_CODE = C.PROJECTID
AND T.CC_DEPT = C.DIVISION
The CT_Year column is simply C for current year L for last year, O for Other, N for Next.
The Function simply returns the year value.
Thanks in advance guys
-
Basically this will take only rows from table T where T.CT_YEAR is L,C,O or N, depending on the value of C.Year.
In other words, if for example C.Year is the current year, T.CT_Year should be C.
No reason to use IN though, as the case statement will always return only one result. Using a function is also not so good for performance, as this function will be called for every row. Maybe joining agains a small lookup table is more efficient.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Thanks For clearing this up Koen,
with regards to this seperate table i was contemplating creating a table where anything prior to current year is negative, current year is 0 i.e. 2015 = 1 , 2014 = 0, 2013 = -1 2012 = -2 etc and then use this with the join.
However I'm not sure how this would fit in with the T.CT_Year and C.Year join?
-
I would create some help table that translates years to the C,N,O,P values.
I would join the C table against this table to get those values for each C.Year value.
Then I would join against the T table and match T.CT_Year against the C,N,O,P value found in the previous join.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
ON T.CT_YEAR in
(
case
.[DBO].[FN_GET_YEAR]
(
CAST
(
C.[YEAR] AS VARCHAR(4)
)
+ '-01-01'
)
when 'L' then 'L'
when 'C' then 'C'
when 'O' then 'O'
else 'N'
end
)
AND T.CH_CODE = C.PROJECTID
AND T.CC_DEPT = C.DIVISION
well According to your statement and the query above
the function [DBO].[FN_GET_YEAR] is a scalar function and if it returns only any one of the Character then "L,C,N,O" the above code will be like this
ON T.CT_YEAR =[DBO].[FN_GET_YEAR] (CAST(C.[YEAR] AS VARCHAR(4)) +'-01-01')
AND T.CH_CODE=C.PROJECTID
AND T.CC_DEPT=C.DIVISION
if the function return more than listed values then it would be like this
ON T.CT_YEAR =(CASE [DBO].[FN_GET_YEAR] (CAST(C.[YEAR] AS VARCHAR(4)) +'-01-01')
WHEN 'L' THEN 'L'
WHEN 'C' THEN 'C'
WHEN 'O' THEN 'O'
ELSE 'N'
END)
AND T.CH_CODE=C.PROJECTID
AND T.CC_DEPT=C.DIVISION
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply