Solved

SQL 2008 Query to produce a report

Posted on 2014-12-05
14
96 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
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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

17 Experts available now in Live!

Get 1:1 Help Now