August 20, 2020 at 1:55 pm
I am in the process of converting an access query to T-SQL. There is an IIf statement which I am having trouble translating.
SUM (LOTOSMIS_ACC_TYPE.sing_ind * LOTOSMIS_RET_DAILY.grs_amn *
iif( gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 ), switch(LOTOSMIS_RET_DAILY.gm_var=1, 0, TRUE, 1), 0)) AS RegSales
What I gave tried so far :
Sum(T.SING_IND * P.GRS_AMN *
CASE
WHEN
P.gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 )
THEN 1
else 0 end) as RegSales,
I dont think it is correct. What am I doing wrong?
August 20, 2020 at 2:08 pm
Your statement looks right, but if it multiplies by 0, then it will always be 0. So why not something a little cleaner
Sum(CASE WHEN P.gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 ) then
T.SING_IND * P.GRS_AMN
Else
0
end) as RegSales
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 21, 2020 at 2:45 pm
I am in the process of converting an access query to T-SQL. There is an IIf statement which I am having trouble translating.
SUM (LOTOSMIS_ACC_TYPE.sing_ind * LOTOSMIS_RET_DAILY.grs_amn *
iif( gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 ), switch(LOTOSMIS_RET_DAILY.gm_var=1, 0, TRUE, 1), 0)) AS RegSales
What I gave tried so far :
Sum(T.SING_IND * P.GRS_AMN *
CASE
WHEN
P.gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 )
THEN 1
else 0 end) as RegSales,
I dont think it is correct. What am I doing wrong?
Okay, so what causes you to think that it's not correct? Have you compared the results to those you get in your T-SQL ? MS Access can often do some rather "interesting" things that one might not anticipate, so it's even possible that the formula in MS Access only appears to work... but may not actually provide the correct result. As we don't have your data, any response that identifies something to "try" is just guesswork. If you can specify the exact rules for creating this SUM, and provide some sample data that represents multiple plausible conditions in your data, we then have a shot at solving the problem. The only alternative is pure guesswork...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 26, 2020 at 1:36 pm
It looks like you are not taking the LOTOSMIS_RET_DAILY.gm_var=1 condition into account.
You would need to code it something like this:
Sum(T.SING_IND * P.GRS_AMN *
CASE
WHEN P.gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 )
THEN case
when LOTOSMIS_RET_DAILY.gm_var=1 then 0
else 1
end
ELSE 0 end as RegSales
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply