May 23, 2016 at 7:53 am
Hi all,
I have a table that is split into call 1, outcome 1, call 2 outcome 2, call 3, outcome 3. Each row of data will have a unique reference number
I am trying to simplify the outcome so I have a case when statement which points to another table with the long detailed version and the short simplified version. This is working however it is returning a seperate row for each simplified outcome.
so for example the output is:
RefCall 1 Outcome 1Call 2 Outcome 2 Call 3 Outcome 3
123412/05/2016 Intro 15/05/2016 NULL 20/05/2016NULL
123412/05/2016 NULL 15/05/2016 Info request 20/05/2016NULL
123412/05/2016 NULL 15/05/2016 NULL 20/05/2016Closed
Where as I want:
RefCall 1 Outcome 1 Call 2 Outcome 2 Call 3 Outcome 3
123412/05/2016 Intro 15/05/2016 Info request 20/05/2016closed
The code looks like this:
SELECT
CALL_1,
CASE WHEN RESP.OUTCOME1 IN OUT.OUTCOME THEN OUT.SIMPLIFIED END AS OUTCOME_1,
CALL_2,
CASE WHEN RESP.OUTCOME2 IN OUT.OUTCOME THEN OUT.SIMPLIFIED END AS OUTCOME_2,
CALL_3,
CASE WHEN RESP.OUTCOME3 IN OUT.OUTCOME THEN OUT.SIMPLIFIED END AS OUTCOME_3
FROM RESPONDING_CALLS RESP
LEFT JOIN OUTCOME_SIMPLIFIED OUT
ON RESP.OUTCOME_1 = OUT.OUTCOME
OR RESP.OUTCOME_2 = OUT.OUTCOME
OR RESP.OUTCOME_3 = OUT.OUTCOME
Any help is grately appriciated!!
May 23, 2016 at 8:02 am
Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 2, 2016 at 7:07 am
can you tell what is your expected result want to achive.
June 2, 2016 at 7:50 am
Maybe you need to aggregate?
SELECT
CALL_1,
MAX(CASE WHEN RESP.OUTCOME1 = OUT.OUTCOME THEN OUT.SIMPLIFIED END) AS OUTCOME_1,
CALL_2,
MAX(CASE WHEN RESP.OUTCOME2 = OUT.OUTCOME THEN OUT.SIMPLIFIED END) AS OUTCOME_2,
CALL_3,
MAX(CASE WHEN RESP.OUTCOME3 = OUT.OUTCOME THEN OUT.SIMPLIFIED END) AS OUTCOME_3
FROM RESPONDING_CALLS RESP
LEFT JOIN OUTCOME_SIMPLIFIED OUT
ON RESP.OUTCOME_1 = OUT.OUTCOME
OR RESP.OUTCOME_2 = OUT.OUTCOME
OR RESP.OUTCOME_3 = OUT.OUTCOME
GROUP BY
CALL_1,
CALL_2,
CALL_3;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply