Hi Everyone!

I'm trying to create a stored procedure to create a table with a LOT of data. I have been playing around with this for quite a while and I still haven't gotten it to work!

The select query is copied and pasted below. I need to replace [CM1] with 53 other questions.

Thank you so much in advance. I hope someone knows how to do this! :)


SELECT 'CM1'
      ,[Segment]
      ,[SubSegment]
      ,[PrimaryMarketAudience]
      ,[SalesDistrict]
      ,[GRS_DATE]
      ,SUM(CASE WHEN [CM1] IN (1,2,3,4)
                THEN 1
            ELSE 0
        END) AS NUM_DSAT
      ,SUM(CASE WHEN [CM1] IN (8,9)
                THEN 1
                ELSE 0
                END) AS NUM_VSAT
      ,COUNT(*) AS Total
      ,SUM(CASE WHEN [CM1] = 1 and [Segment] = 'Major'
        THEN 0.02
        WHEN [CM1] = 1 and[Segment] ='Education'
        THEN 0.02
        WHEN [CM1] = 1 and [Segment]= 'CAS'
        THEN 0.02
        WHEN [CM1] = 1 and [Segment] = 'Small Business'
        THEN 2
        WHEN [CM1] = 1 and [Segment]= 'Mid-Market'
        THEN 2
        WHEN [CM1] = 1 and [Segment]= 'IT Pro'
        THEN 2.4
            WHEN [CM1] = 2 and [Segment] = 'Major'
        THEN 0.02
        WHEN [CM1] = 2 and [Segment]= 'Education'
        THEN 0.02
        WHEN [CM1] = 2 and [Segment]= 'CAS'
        THEN 0.02
        WHEN [CM1] = 2 and [Segment] = 'Small Business'
        THEN 2
        WHEN [CM1] = 2 and [Segment]= 'Mid-Market'
        THEN 2
        WHEN [CM1] = 2 and [Segment]= 'IT Pro'
        THEN 2.4
        WHEN [CM1] = 3 and [Segment] = 'Major'
        THEN 0.02
        WHEN [CM1] = 3 and [Segment]= 'Education'
            THEN 0.02
        WHEN [CM1] = 3 and [Segment]= 'CAS'
        THEN 0.02
        WHEN [CM1] = 3 and [Segment] = 'Small Business'
        THEN 2
        WHEN [CM1] = 3 and [Segment]= 'Mid-Market'
        THEN 2
        WHEN [CM1] = 3 and [Segment]= 'IT Pro'
        THEN 2.4
        WHEN [CM1] = 4 and [Segment] = 'Major'
        THEN 0.02
        WHEN [CM1] = 4 and [Segment]= 'Education'
        THEN 0.02
        WHEN [CM1] = 4 and [Segment]= 'CAS'
        THEN 0.02
        WHEN [CM1] = 4 and [Segment] = 'Small Business'
        THEN 2
        WHEN [CM1] = 4 and [Segment]= 'Mid-Market'
        THEN 2
        WHEN [CM1] = 4 and [Segment]= 'IT Pro'
        THEN 2.4
        ELSE 0
        END) as RND_DSAT
      ,SUM(CASE WHEN [CM1] = 8 and [Segment] = 'Major'
        THEN 0.02
        WHEN [CM1] = 8 and [Segment]= 'Education'
        THEN 0.02
        WHEN [CM1] = 8 and [Segment]= 'CAS'
        THEN 0.02
        WHEN [CM1] = 8 and [Segment] = 'Small Business'
        THEN 2
        WHEN [CM1] = 8 and [Segment]= 'Mid-Market'
        THEN 2
        WHEN [CM1] = 8 and [Segment]= 'IT Pro'
        THEN 2.4
            WHEN [CM1] = 9 and [Segment] = 'Major'
        THEN 0.02
        WHEN [CM1] = 9 and [Segment]= 'Education'
        THEN 0.02
        WHEN [CM1] = 9 and [Segment]= 'CAS'
        THEN 0.02
        WHEN [CM1] = 9 and [Segment] = 'Small Business'
        THEN 2
        WHEN [CM1] = 9 and [Segment]= 'Mid-Market'
        THEN 2
        WHEN [CM1] = 9 and [Segment]= 'IT Pro'
        THEN 2.4
        ELSE 0
        END) as RND_VSAT
      ,SUM(CASE WHEN [SEGMENT] = ‘Major'
        THEN 0.02
        WHEN [SEGMENT] = 'Education'
        THEN 0.02
        WHEN [SEGMENT] = 'CAS'
        THEN 0.02
        WHEN [SEGMENT] = 'Small Business'
        THEN 2
        WHEN [SEGMENT] = 'Mid-Market'
        THEN 2
        WHEN [SEGMENT] = 'IT Pro'
        THEN 2.4
        ELSE 0
        END) as RND_Total
FROM [2007H2GRS].[dbo].[H1_H2Data]
WHERE GRS_Date = 200702
GROUP BY [Segment]
      ,[SubSegment]
      ,[PrimaryMarketAudience]
      ,[SalesDistrict]
    ,[GRS_DATE]
      ,[CM1]

Could you not just create it as a stored procedure?

CREATE PROCEDURE CORRINEO_QUERY @CM1 VARCHAR(255) AS
... then your query, but replace CM1 with @CM1

How about storing you 54 questions in a table and then joining that table into your query?

Regards,

Sean Anderson