Solved

Displaying all question results even if there were none

Posted on 2014-12-08
8
83 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
  • 3
  • 2
  • 2
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
What the current query returns?
0
 

Author Comment

by:wdbates
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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 45

Accepted Solution

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now