locked
Combining 3 selects into 1 Stored Procedure RRS feed

  • Question

  • Hi

    I want to create a stored procedure and have created a query using 3 select statements.
    How do I combine these 3 selects so I can reference the totals and counts from all 3 selects in srs report builder?

    /************************************************/
    /* INDIVIDUAL TOTAL - E-SINGLE                  */
    /************************************************/
    
    SELECT 
    	COUNT(*)			AS 'Ind. Total - E-Single',
    	COUNT(*) * 2			AS 'Total Ind'
    	
    FROM dbo.Contact CON
    
    LEFT OUTER JOIN StringMap STC
         ON            STC.attributename   = 'new_coveragetype' 
         AND           STC.attributevalue = CON.new_CoverageType
         AND           STC.objecttypecode = 2 
         AND           STC.langid = 1033
         
    WHERE  CON.StatusCode		   = 1
    AND (
    			CON.AccountIdName NOT IN
    			 (
    				'Company 1',
    				'Company 2',
    				'Company 3',
    				'Company 4',
    				'Company 5',
    				'Company 6'
    			 )
    			OR CON.AccountIdName IS NULL
    		)
    
    
    AND    STC.Value          = 'E - Single'
    
    
    /************************************************/
    /* INDIVIDUAL TOTAL - ES-COUPLE                 */
    /************************************************/
    
    SELECT COUNT(*) 		AS 'Ind Total - ES-Couple',
    COUNT(*) * 3			AS 'Total Couple'
    
    
    FROM dbo.Contact CON
    LEFT OUTER JOIN StringMap STC
         ON            STC.attributename   = 'new_coveragetype' 
         AND           STC.attributevalue = CON.new_CoverageType
         AND           STC.objecttypecode = 2 
         AND           STC.langid = 1033
    WHERE  CON.StatusCode		   = 1
    AND (
    			CON.AccountIdName NOT IN
    			 (
    				'Company 1',
    				'Company 2',
    				'Company 3',
    				'Company 4',
    				'Company 5',
    				'Company 6'
    			 )
    			 OR CON.AccountIdName IS NULL
    		)
    
    
    AND    STC.Value          = 'ES - Couple'
    
    
    /************************************************/
    /* INDIVIDUAL TOTAL - F-FAMILY                  */
    /************************************************/
    
    SELECT COUNT(*) 		AS 'Ind Total - F-Family',
    COUNT(*) * 4			AS 'Total Family'
    
    FROM dbo.Contact CON
    LEFT OUTER JOIN StringMap STC
         ON            STC.attributename   = 'new_coveragetype' 
         AND           STC.attributevalue = CON.new_CoverageType
         AND           STC.objecttypecode = 2 
         AND           STC.langid = 1033
    WHERE  CON.StatusCode		   = 1
    AND (
    			CON.AccountIdName NOT IN
    			 (
    				'Company 1',
    				'Company 2',
    				'Company 3',
    				'Company 4',
    				'Company 5',
    				'Company 6'
    			 )
    			OR CON.AccountIdName IS NULL
    		)
    
    
    AND    STC.Value          = 'F - Family'
    
    
    
    

    Wednesday, July 24, 2013 9:43 AM

All replies

  • Hi,

    Could you please try the following code:

    SELECT 
    	 SUM(CASE WHEN STC.Value = 'E - Single' THEN 1 ELSE 0 END)      AS 'Ind. Total - E-Single'
    	,SUM(CASE WHEN STC.Value = 'E - Single' THEN 1 ELSE 0 END) * 2  AS 'Total Ind'
    	,SUM(CASE WHEN STC.Value = 'ES - Couple' THEN 1 ELSE 0 END)     AS 'Ind. Total - ES-Couple'
    	,SUM(CASE WHEN STC.Value = 'ES - Couple' THEN 1 ELSE 0 END) * 3 AS 'Total Couple'
    	,SUM(CASE WHEN STC.Value = 'F - Family' THEN 1 ELSE 0 END)      AS 'Ind. Total - F - Family'
    	,SUM(CASE WHEN STC.Value = 'F - Family' THEN 1 ELSE 0 END) * 4  AS 'Total Family'
    	
    FROM dbo.Contact CON
    
    LEFT OUTER JOIN StringMap STC
         ON            STC.attributename   = 'new_coveragetype' 
         AND           STC.attributevalue = CON.new_CoverageType
         AND           STC.objecttypecode = 2 
         AND           STC.langid = 1033
         
    WHERE  CON.StatusCode		   = 1
    AND (
    			CON.AccountIdName NOT IN
    			 (
    				'Company 1',
    				'Company 2',
    				'Company 3',
    				'Company 4',
    				'Company 5',
    				'Company 6'
    			 )
    			OR CON.AccountIdName IS NULL
    		)

    Best regards

    Steve


    Steve Sämmang, Vienna, Austria
    Blog: xrm.io Website: simplic.at

    Wednesday, July 24, 2013 9:53 AM