Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Displaying all question results even if there were none

Posted on 2014-12-08
Medium Priority
91 Views
Looking at the attached Excel workbook Sheet 1 how would you display the results if one of the Results questions returned a Zero (0)?

Here is the existing query from Expert-Exchange question 28575361.

``````USE ABC;
GO

With myTable as
(SELECT
[Scenerio]
,[SB_Num]
,[RptQuestion]
,sum([QuestionWeight]) * 100.0/ sum([QuestionWeight]) over(partition by [SB_Num] order by [ShortUserAns] Rows between unbounded preceding and unbounded following) as [Freq]
,iif([Correct]=1, 'X', '') as [Correct]
,[ShortUserAns]
FROM [ABC].[dbo].[Test_PTR]
WHERE [SB_Num] in ( '170100000050', '170100000060' )
GROUP BY [SB_Num], [Scenerio], [RptQuestion], iif([Correct]=1, 'X', ''), [QuestionWeight], [ShortUserAns]  )

Select [Scenerio]
,[RptQuestion]
,CASE
WHEN [ShortUserAns] = 'A' AND SB_NUM = '170100000050' THEN 'Claim for PTSD 30 years after traumatic event'
WHEN [ShortUserAns] = 'B' AND SB_NUM = '170100000050' THEN 'Veteran''s treatment statement at the VAMC'
WHEN [ShortUserAns] = 'C' AND SB_NUM = '170100000050' THEN 'Drop in her evaluation proficiency marks'
WHEN [ShortUserAns] = 'D' AND SB_NUM = '170100000050' THEN 'Not enough information to accurately answer'
WHEN [ShortUserAns] = 'E' AND SB_NUM = '170100000050' THEN 'Separation from service for pregnancy'
WHEN [ShortUserAns] = 'F' AND SB_NUM = '170100000050' THEN 'C and E'
WHEN [ShortUserAns] = 'A' AND SB_NUM = '170100000060' THEN 'Order a PTSD exam with medical opinion'
WHEN [ShortUserAns] = 'B' AND SB_NUM = '170100000060' THEN 'Develop for VA Form 21-0781a'
WHEN [ShortUserAns] = 'C' AND SB_NUM = '170100000060' THEN 'Request police report to verify traumatic event'
WHEN [ShortUserAns] = 'D' AND SB_NUM = '170100000060' THEN 'Send FDC exclusion paragraph'
END AS Response
, [Freq]
, [Correct]
from MyTable
ORDER BY SB_Num, [Freq] DESC
``````
Scenario-1.xlsx
0
Question by:wdbates
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2

LVL 49

Expert Comment

ID: 40487988
In the previous question, the "Accepted Answer" isn't really the answer - but it IS VERY GOOD ADVICE

... the structure of all the tables should be posted as another question.

Please provide some raw data (per table) and the "expected result". Then, with both those items (data and result) we can help produce the required query.
0

LVL 52

Expert Comment

ID: 40488717
What the current query returns?
0

Author Comment

ID: 40491877
Updated Query and Results:

``````USE ABC;
GO

WITH cte1 AS
(SELECT
SB_Num
, count(SB_Num) AS total
, Scenerio
FROM ABC.dbo.Test_RPT rpt INNER JOIN dbo.UserD ud ON rpt.UserID = ud.UserID AND ud.ExcludeFromRpts = 0
WHERE Section = 1 and Scenerio IN ( 'Scenario 1', 'Scenario 2', 'Scenario 3'  )
GROUP BY SB_Num, Scenerio),
cte2 AS (SELECT  SB_Num
, SUM( CASE ShortUserAns
WHEN 'A' THEN 1
WHEN 'B' THEN 1
WHEN 'C' THEN 1
WHEN 'D' THEN 1
WHEN 'E' THEN 1
WHEN 'F' THEN 1
END ) AS  nShortUserAns
,ShortUserAns
,iif(Correct=1, 'X', '') as Correct
FROM ABC.dbo.Test_RPT rpt2 INNER JOIN dbo.UserD ud2 ON rpt2.UserID = ud2.UserID AND ud2.ExcludeFromRpts = 0
WHERE Section = 1 and Scenerio IN ( 'Scenario 1', 'Scenario 2', 'Scenario 3' )
GROUP BY SB_Num, ShortUserAns, Correct)

Select a.Scenerio
,CASE
WHEN a.SB_NUM IN ( '170100000050', '170100000070', '170100000090' ) THEN 'What are the identified markers?'
WHEN a.SB_NUM IN ( '170100000060', '170100000080', '170100000100' ) THEN 'What is the next step?'
END AS RptQuestion
,CASE
WHEN b.ShortUserAns = 'A' AND b.SB_NUM = '170100000050' THEN 'Claim for PTSD 30 years after traumatic event'
WHEN b.ShortUserAns = 'B' AND b.SB_NUM = '170100000050' THEN 'Veteran''s treatment statement at the VAMC'
WHEN b.ShortUserAns = 'C' AND b.SB_NUM = '170100000050' THEN 'Drop in her evaluation proficiency marks'
WHEN b.ShortUserAns = 'D' AND b.SB_NUM = '170100000050' THEN 'Not enough information to accurately answer'
WHEN b.ShortUserAns = 'E' AND b.SB_NUM = '170100000050' THEN 'Separation from service for pregnancy'
WHEN b.ShortUserAns = 'F' AND b.SB_NUM = '170100000050' THEN 'C and E'
WHEN b.ShortUserAns = 'G' AND b.SB_NUM = '170100000050' THEN 'B &amp; C'
WHEN b.ShortUserAns = 'A' AND b.SB_NUM = '170100000060' THEN 'Order a PTSD exam with medical opinion'
WHEN b.ShortUserAns = 'B' AND b.SB_NUM = '170100000060' THEN 'Develop for VA Form 21-0781a'
WHEN b.ShortUserAns = 'C' AND b.SB_NUM = '170100000060' THEN 'Request police report to verify traumatic event'
WHEN b.ShortUserAns = 'D' AND b.SB_NUM = '170100000060' THEN 'Send FDC exclusion paragraph'
WHEN b.ShortUserAns = 'A' AND b.SB_NUM = '170100000070' THEN 'Confronted a fellow sailor'
WHEN b.ShortUserAns = 'B' AND b.SB_NUM = '170100000070' THEN 'Disciplinary actions for alcohol abuse and going AWOL'
WHEN b.ShortUserAns = 'C' AND b.SB_NUM = '170100000070' THEN 'Excellent evaluations'
WHEN b.ShortUserAns = 'D' AND b.SB_NUM = '170100000070' THEN 'No markers. It''s a traditional military initiation'
WHEN b.ShortUserAns = 'A' AND b.SB_NUM = '170100000080' THEN 'Develop for lay statement of sailor'
WHEN b.ShortUserAns = 'B' AND b.SB_NUM = '170100000080' THEN 'Update and refer claim as RFD'
WHEN b.ShortUserAns = 'C' AND b.SB_NUM = '170100000080' THEN 'Schedule PTSD exam with medical opinion'
WHEN b.ShortUserAns = 'D' AND b.SB_NUM = '170100000080' THEN 'Develop for ship record to confirm event'
WHEN b.ShortUserAns = 'A' AND b.SB_NUM = '170100000090' THEN 'Current treatment coincides with STRs'
WHEN b.ShortUserAns = 'B' AND b.SB_NUM = '170100000090' THEN 'Reopened claim for PTSD'
WHEN b.ShortUserAns = 'C' AND b.SB_NUM = '170100000090' THEN 'Rating decision denied due to no corroborating evidence'
WHEN b.ShortUserAns = 'D' AND b.SB_NUM = '170100000090' THEN 'None of the above'
WHEN b.ShortUserAns = 'A' AND b.SB_NUM = '170100000100' THEN 'Develop for police report of sexual trauma'
WHEN b.ShortUserAns = 'B' AND b.SB_NUM = '170100000100' THEN 'Develop for the Military Personal Records, PIES 018, since the records would show what type discharge'
WHEN b.ShortUserAns = 'C' AND b.SB_NUM = '170100000100' THEN 'Send exclusion letter b/c Vet did not submit VA Form 21-0781a'
WHEN b.ShortUserAns = 'D' AND b.SB_NUM = '170100000100' THEN 'Request Prenatal Clinical records to confirm pregnancy'
END AS Response
, CAST(b.nShortUserAns * 100.00 / a.total AS DECIMAL(10,2)) AS Freq
, Correct
FROM cte1 AS a JOIN cte2 AS b on a.SB_Num = b.SB_Num
ORDER BY a.SB_Num, Freq DESC
``````

``````Scenerio	        RptQuestion	                                         Response	                                                                Freq	Correct
Scenario 1	What are the identified markers?	Claim for PTSD 30 years after traumatic event	75.00
Scenario 1	What are the identified markers?	Veteran's treatment statement at the VAMC	       25.00
Scenario 1	What is the next step?	                        Order a PTSD exam with medical opinion	       75.00	X
Scenario 1	What is the next step?	                        Develop for VA Form 21-0781a	                               25.00
Scenario 2	What are the identified markers?	Confronted a fellow sailor	                                       75.00
Scenario 2	What are the identified markers?	Disciplinary actions for alcohol abuse and going AWOL	25.00	X
Scenario 2	What is the next step?	                        Develop for lay statement of sailor	75.00
Scenario 2	What is the next step?	                        Update and refer claim as RFD	25.00
Scenario 3	What are the identified markers?	Current treatment coincides with STRs	75.00
Scenario 3	What are the identified markers?	Reopened claim for PTSD	25.00
Scenario 3	What is the next step?	                       Develop for police report of sexual trauma	50.00
Scenario 3	What is the next step?	                       Develop for the Military Personal Records, PIES 018, since the records would show what type discharge	25.00
Scenario 3	What is the next step?	Send exclusion letter b/c Vet did not submit VA Form 21-0781a	25.00
``````
0

LVL 49

Expert Comment

ID: 40492779

I need SAMPLE DATA from the tables (ABC.dbo.Test_RPT , dbo.UserD), names or anything considered private should be replaced with innocent data. Like the data tab of your earlier attachment, but with all fields and all tables that are used.
0

LVL 52

Accepted Solution

Vitor Montalvão earned 2000 total points
ID: 40495719
Still can't understand the question but I have a couple of recommendations about your query:

## 1.

The SUM function can be replaced by COUNT:
COUNT(ShortUserAns) AS  nShortUserAns

## 2.

You should create a Response table with 3 columns (UserAns, SB_Num, ResponseText) so you can get rid of that CASE statement  to get the Response and will give you the flexibility to add and change responses
0

Author Comment

ID: 40499214
OK, I will take a look on Monday.  Been on the road the last 4 nights and came home and discovered the apartment next to mine caught on fire, so sorry for the delay.
0

Author Closing Comment

ID: 40504952
Created the table and I can't believe that I was using that CASE statement.  Things are working now and thank you, but sorry for the delay.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…