Solved

Displaying all question results even if there were none

Posted on 2014-12-08
8
86 Views
Last Modified: 2014-12-17
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

Open in new window

Scenario-1.xlsx
0
Comment
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
  • Learn & ask questions
  • 3
  • 2
  • 2
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
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 49

Expert Comment

by:Vitor Montalvão
ID: 40488717
What the current query returns?
0
 

Author Comment

by:wdbates
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 & 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

Open in new window


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

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40492779
Results of that query do NOT help me help you.

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 49

Accepted Solution

by:
Vitor Montalvão earned 500 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

by:wdbates
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

by:wdbates
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question