Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Niki
Yak Posting Veteran
51 Posts |
Posted - 2013-12-20 : 10:56:42
|
Hi,I would like to draw an ANSWER from the 4 questions that are posed in a survey.Here are the rules- If all Q1,Q2,Q3 & Q4 = ‘Yes’ then ANSWER = ‘Yes’- If there are only 2 questions answered and 2 left blank, pick the one that is answered other than ‘No’ out of the answered responses- If there are three responses, then use the answer other than ‘No’ which appears in Q2, then Q3, then Q1.I need help to build a CASE statement for the above.Thank you,Niki |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-12-20 : 11:37:56
|
Can you supply some sample data with expected output in a consumable format? Here are some links to help you provide that:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxPS - We don't do homework, but we can help guide you. |
|
|
Niki
Yak Posting Veteran
51 Posts |
Posted - 2013-12-20 : 15:01:31
|
Sorry, I was really under pressure to make this update. I accomplished using multiple case statements as below. I am looking for a better way to accomplish the same task. Update Table1 set t1.HomLang = --if the HLS = all English, For EL students, HomLang "Unknown" case when (lng.FirstlearntLang = '00' and lng.LangSpokenbyAdults = '00' and lng.LangSpokenbychild = '00' ) then 'UU' --When there are only two languages listed out of Q1,Q2 &Q3, and one is English, use the language other than English when (lng.FirstlearntLang is null and lng.LangSpokenbyAdults is not null and lng.LangSpokenbychild is not null) and lng.LangSpokenbyAdults = '00' then lng.LangSpokenbychild when(lng.FirstlearntLang is null and lng.LangSpokenbyAdults is not null and lng.LangSpokenbychild is not null) and lng.LangSpokenbychild = '00' then lng.LangSpokenbyAdults when (lng.LangSpokenbyAdults is null and lng.FirstlearntLang is not null and lng.LangSpokenbychild is not null) and lng.FirstlearntLang = '00' then lng.LangSpokenbychild when (lng.LangSpokenbyAdults is null and lng.FirstlearntLang is not null and lng.LangSpokenbychild is not null) and lng.LangSpokenbychild = '00' then lng.FirstlearntLang when (lng.LangSpokenbychild is null and lng.FirstlearntLang is not null and lng.LangSpokenbyAdults is not null) and lng.FirstlearntLang = '00' then lng.LangSpokenbyAdults when (lng.LangSpokenbychild is null and lng.FirstlearntLang is not null and lng.LangSpokenbyAdults is not null) and lng.LangSpokenbyAdults = '00' then lng.FirstlearntLang --If there are three languages listed, then use the language other than English which appears in Q2, then Q3, then Q1 -- possibility of one of them being English when (lng.FirstlearntLang = '00' and lng.LangSpokenbyAdults != '00' and lng.LangSpokenbychild != '00') then lng.LangSpokenbyAdults when (lng.LangSpokenbyAdults = '00' and lng.FirstlearntLang != '00' and lng.LangSpokenbychild != '00') then lng.LangSpokenbychild when (lng.LangSpokenbychild = '00' and lng.FirstlearntLang != '00' and lng.LangSpokenbyAdults != '00') then lng.LangSpokenbyAdults -- when all three are non-english L2 takes priority over L3 take priority over L1 when ( lng.FirstlearntLang != '00' and lng.LangSpokenbyAdults != '00' and lng.LangSpokenbychild != '00' ) then lng.LangSpokenbyAdults --If there is only one language listed, and it’s not English, pick this when ( lng.FirstlearntLang is null and lng.LangSpokenbyAdults is null and lng.LangSpokenbychild != '00' ) then lng.LangSpokenbychild when ( lng.FirstlearntLang is null and lng.LangSpokenbyAdults != '00' and lng.LangSpokenbychild is null ) then lng.LangSpokenbyAdults when ( lng.FirstlearntLang != '00' and lng.LangSpokenbyAdults is null and lng.LangSpokenbychild is null ) then lng.FirstlearntLang --If there is only one non-English, pick this when ( lng.FirstlearntLang ='00' and lng.LangSpokenbyAdults = '00' and lng.LangSpokenbychild != '00' ) then lng.LangSpokenbychild when ( lng.FirstlearntLang = '00' and lng.LangSpokenbyAdults != '00' and lng.LangSpokenbychild = '00' ) then lng.LangSpokenbyAdults when ( lng.FirstlearntLang != '00' and lng.LangSpokenbyAdults = '00' and lng.LangSpokenbychild = '00' ) then lng.FirstlearntLang endfrom Table1 t1INNER JOIN table2 lng ON t1.table_GU = lng.table_GU WHERE t1.HomLang = '00' commit transactionend trybegin catch if @@trancount > 0 rollback DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1)end catchNiki |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-21 : 01:08:52
|
Unless you post some sample data and explain what your exact requirement is its difficult to help you out. follow the link posted and give sample data in required format.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|