RudiSQLQ RudiSQLQ - 3 months ago 9
SQL Question

SQL-Server - Help to find Responses with 11

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
DB.[dbo].[T_FCT_QUESTIONS]
WHERE CALENDAR_MONTH = '201607'
AND RESPONSE = '11'


What i need to find out ist, the Question_ID's of Customers which contain the response 11 and if there is only a 11 as response or are there also other results from (0-10) in a particular calendar month.

How can i achieve this?

THX and BR

Rudi

Answer

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.

Comments