i've got a question on an SQL.
I have a table which contains, Question Responses from different customers.
Following Basic Code:
SELECT CUSTOMER_ID, QUESTIONS_ID, RESPONSE, CALENDAR_MONTH FROM
WHERE CALENDAR_MONTH = '201607'
AND RESPONSE = '11'
If I well understood, I propose this solution.
You should do two
SELECT statements with grouping, one for RESPONSE 11 and the other for other RESPONSE codes. Then you have to join these selections with a LEFT JOIN.
SELECT Resp11.*, CASE WHEN Resp0_10.CALENDAR_MONTH IS NULL AND Resp0_10.CUSTOMER_ID IS NULL AND Resp0_10.QUESTIONS_ID IS NULL THEN 'No other RESPONSE for this month' ELSE 'Other RESPONSE for this month' END FROM ( SELECT CALENDAR_MONTH, CUSTOMER_ID, QUESTIONS_ID FROM DB.[dbo].[T_FCT_QUESTIONS] WHERE RESPONSE = '11' GROUP BY CALENDAR_MONTH, CUSTOMER_ID, QUESTIONS_ID ) Resp11 LEFT JOIN ( SELECT CALENDAR_MONTH, CUSTOMER_ID, QUESTIONS_ID FROM DB.[dbo].[T_FCT_QUESTIONS] WHERE RESPONSE >= '0' AND RESPONSE <= '10' GROUP BY CALENDAR_MONTH, CUSTOMER_ID, QUESTIONS_ID ) Resp0_10 ON Resp11.CALENDAR_MONTH=Resp0_10.CALENDAR_MONTH AND Resp11.CUSTOMER_ID=Resp0_10.CUSTOMER_ID AND Resp11.QUESTIONS_ID=Resp0_10.QUESTIONS_ID
Items not available in the second selections wll have only NULL items, so no other response is given for these items.