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

x
Solved

# SQL 2008 Query to produce a report

Posted on 2014-12-05
Medium Priority
108 Views
Hello Experts;

It’s Friday and the brain has stopped functioning.  I need to produce a query that looks like the first sheet in the attached Excel Workbook.  Sheet 2 contains the query and output of the table.

The report will contain multiple Scenarios and each Scenario will have 2 questions.  The columns called distractors  are the questions in a drop down list.  CorrectAns is the Correct answer, UserAns is what the user choose and the column called ShortUserAns is a ALPHA description of the  distractors, distractor_1 is A...Column Correct is an 0 and 1 value and 1 is correct.
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
• 7
• 7

LVL 24

Expert Comment

ID: 40484816
I'm away from my computer.

I'm assuming it's the % that is perplexing you. however about

sum(QuestionWeight) / sum(QuestionWeight) over(partition by SB_Num order by ShortUserAns Rows between unbounded preceding and unbounded following)

Then just group by/sum the other fields in your results.
0

Author Comment

ID: 40484911
Hello Philip;

Could you show the full Select statement?  I have a good clue what you are saying, but not completely.

Thanks
0

LVL 24

Expert Comment

ID: 40486339
``````With myTable as (SELECT [SB_Num]
,[Scenerio]
,[RptQuestion]
,sum(QuestionWeight) / 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]
FROM [ABC].[dbo].[Test_RPT]
WHERE SB_Num in ( 170100000050, 170100000060 )
GROUP BY SB_Num, [Scenerio] ,[RptQuestion], iif([Correct]=1, 'X', ''))

Select * from MyTable
ORDER BY SB_Num, [Freq] DESC
``````
0

Author Comment

ID: 40486783
Getting close Phillip!

Msg 8120, Level 16, State 1, Line 5
Column '[ABC].[dbo].[Test_RPT].QuestionWeight' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I comment that line out and try to execute the CTE I receive, "Incorrect syntax near ')'." on the GROUP BY line.
0

LVL 24

Expert Comment

ID: 40486793
Try adding it to the end of line 8:

``````With myTable as (SELECT [SB_Num]
,[Scenerio]
,[RptQuestion]
,sum(QuestionWeight) / 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]
FROM [ABC].[dbo].[Test_RPT]
WHERE SB_Num in ( 170100000050, 170100000060 )
GROUP BY SB_Num, [Scenerio] ,[RptQuestion], iif([Correct]=1, 'X', ''), [QuestionWeight])

Select * from MyTable
ORDER BY SB_Num, [Freq] DESC
``````
0

Author Comment

ID: 40486818
OK, I changed the GROUP BY TO, "GROUP BY [SB_Num], [Scenerio], [RptQuestion], iif([Correct]=1, 'X', ''), [ShortUserAns], [QuestionWeight] )"

I will do some quick testing and get back with you.

Thank you very much.
0

Author Comment

ID: 40486944
This is output of the query:

Scenerio              RptQuestion                                              Response                                                                     Freq      Correct
Scenario 1      What are the identified markers?      Claim for PTSD 30 years after traumatic event      1
Scenario 1      What are the identified markers?      Veteran's treatment statement at the VAMC              0
Scenario 1      What are the identified markers?      Drop in her evaluation proficiency marks              0
Scenario 1      What is the next step?                              Order a PTSD exam with medical opinion              1      X
Scenario 1      What is the next step?                              Develop for VA Form 21-0781a                                      0
Scenario 1      What is the next step?                       Request police report to verify traumatic event      0

This is the source for the first SB_Num '170100000050':

UserAns                                                                             ShortUserAns       Correct      QuestionWeight      QuestionScore
Claim for PTSD 30 years after traumatic event      A                       0             1                              0
Drop in her evaluation proficiency marks              C                       0             1                              0
Claim for PTSD 30 years after traumatic event      A                       0             1                              0
Claim for PTSD 30 years after traumatic event      A                       0             1                              0
Veteran''s treatment statement at the VAMC              B                       0             1                              0

The results do not match the source as there are 3 A's.

How can I display all of the questions available even if there is no response?

We are running 2008 R2 as info.
0

LVL 24

Expert Comment

ID: 40486964
>> The results do not match the source as there are 3 A's.

That's not what your spreadsheet asked for. "Report" says that "Freq" is a percentage, not a sum.

Having said that, it is rounding it to zero decimal places, so here's an update:

``````With myTable as (SELECT [SB_Num]
,[Scenerio]
,[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]
FROM [ABC].[dbo].[Test_RPT]
WHERE SB_Num in ( 170100000050, 170100000060 )
GROUP BY SB_Num, [Scenerio] ,[RptQuestion], iif([Correct]=1, 'X', ''), [QuestionWeight])

Select * from MyTable
ORDER BY SB_Num, [Freq] DESC
``````
0

LVL 24

Accepted Solution

Phillip Burton earned 2000 total points
ID: 40486970
>> How can I display all of the questions available even if there is no response?

You need another table with all of the questions available, and then join them together; however, the solution to this question and the structure of all the tables should be posted as another question.
0

Author Closing Comment

ID: 40486995
Great answer.  I always enjoy learning something new.
0

LVL 24

Expert Comment

ID: 40487080
Please could you post the complete result you are getting.
0

Author Comment

ID: 40487090
The query:
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

The results:
Scenerio      RptQuestion      Response      Freq      Correct
Scenario 1      What are the identified markers?      Claim for PTSD 30 years after traumatic event      100.000000000000
Scenario 1      What are the identified markers?      Veteran's treatment statement at the VAMC      33.333333333333
Scenario 1      What are the identified markers?      Drop in her evaluation proficiency marks      33.333333333333
Scenario 1      What is the next step?      Order a PTSD exam with medical opinion      100.000000000000      X
Scenario 1      What is the next step?      Develop for VA Form 21-0781a      33.333333333333
Scenario 1      What is the next step?      Request police report to verify traumatic event      33.333333333333
0

Author Comment

ID: 40487380
Percentages are not correct
0

LVL 24

Expert Comment

ID: 40488350
I need to recreate it.

Can you please post the structure of the table and the results from:

Select * FROM [ABC].[dbo].[Test_PTR]
WHERE [SB_Num] in ( '170100000050', '170100000060' )
0

## Featured Post

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
###### Suggested Courses
Course of the Month7 days, 20 hours left to enroll