Solved

Displaying all question results even if there were none

Posted on 2014-12-08
8
88 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 49

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 50

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 49

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 50

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

695 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