Solved

SQL 2008 Query to produce a report

Posted on 2014-12-05
14
93 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
  • 7
  • 7
14 Comments
 
LVL 24

Expert Comment

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

Author Comment

by:wdbates
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
>> 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
Comment Utility
>> 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
Comment Utility
Great answer.  I always enjoy learning something new.
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Please could you post the complete result you are getting.
0
 

Author Comment

by:wdbates
Comment Utility
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
Comment Utility
Percentages are not correct
0
 
LVL 24

Expert Comment

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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