SQL 2008 Query to produce a report

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
wdbatesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
wdbatesAuthor Commented:
Hello Philip;

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

Thanks
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

wdbatesAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
wdbatesAuthor Commented:
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
wdbatesAuthor Commented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>> 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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>> 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wdbatesAuthor Commented:
Great answer.  I always enjoy learning something new.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please could you post the complete result you are getting.
0
wdbatesAuthor Commented:
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
wdbatesAuthor Commented:
Percentages are not correct
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.