Solved

SQL 2008 Query to produce a report

Posted on 2014-12-05
14
103 Views
Last Modified: 2014-12-09
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
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
  • 7
  • 7
14 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
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

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

by:Phillip Burton
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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

by:Phillip Burton
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

Open in new window

0
 

Author Comment

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

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

by:Phillip Burton
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

Open in new window

0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 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

by:wdbates
ID: 40486995
Great answer.  I always enjoy learning something new.
0
 
LVL 24

Expert Comment

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

Author Comment

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

by:wdbates
ID: 40487380
Percentages are not correct
0
 
LVL 24

Expert Comment

by:Phillip Burton
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

730 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