asked on

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

Phillip Burton

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.

wdbates

ASKER

Hello Philip;

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

Thanks

Phillip Burton

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

wdbates

ASKER

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.

Phillip Burton

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

wdbates

ASKER

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.

wdbates

ASKER

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.

Phillip Burton

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

ASKER CERTIFIED SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wdbates

ASKER

Great answer. I always enjoy learning something new.

Phillip Burton

Please could you post the complete result you are getting.

wdbates

ASKER

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

wdbates

ASKER

Percentages are not correct

Phillip Burton

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' )