Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: Trouble with Count and Sum and Joined Tables

    I am trying to fit all queries into 1 query but I am getting stuck. I have trouble with the Case and joining tables.


    I am trying to get this query

    Code:
    Select COUNT (CASE WHEN CODEID LIKE '%545456%'
    		OR	 CODEID LIKE '%878974%'
    		THEN CODEID 
                    END) AS 'IBM_5_bundle'
    FROM 	history
    where MailDate between '20100210' and '20100228'
    and OrderStat in ('OKAY', 'GOOD)
    and OfferCode in ('105', '199', '122')
    and filename like '%NL%'
    
    Select COUNT (CASE WHEN CODEID LIKE '%2312315%'
    		    OR	 CODEID LIKE '%48678%'
    		    THEN CODEID 
                        END) AS 'a',
           COUNT (CASE WHEN CODEID LIKE '%23156%'
    		    OR	 CODEID LIKE '%2315648%'
    		    THEN CODEID 
                        END) AS 'b',
           COUNT (CASE WHEN CODEID LIKE '%5457756%'
    		    OR	 CODEID LIKE '%2315645%'
    		    THEN CODEID 
                        END) AS 'c,
           COUNT (CASE WHEN CODEID LIKE '%2315641%'
    OR	 CODEID LIKE '%24421412%'
    		    OR	 CODEID LIKE '%24421413%'
    		    OR	 CODEID LIKE '%24421414%'
    		    OR	 CODEID LIKE '%24421415%'
    		    OR	 CODEID LIKE '%2442141%'
    		    THEN CODEID 
                        END) AS 'd',
    	 COUNT (CASE WHEN CODEID LIKE '%244214123%'
    		    OR	 CODEID LIKE '%244214126%'
    		    OR	 CODEID LIKE '%73408%'
    			THEN CODEID 
                        END) AS 'e',
    	COUNT (CASE WHEN CODEID LIKE '%23121212%'
    		    OR	 CODEID LIKE '%23121215%'
    		    THEN CODEID 
                        END) AS 'f',
    	COUNT (CASE WHEN CODEID LIKE '%231212111%'
    		    OR	 CODEID LIKE '%2312121112%'
    		    THEN CODEID 
                        END) AS 'g'
    
    FROM 	history
    WHERE 	 between '20100210' and '20100228'
    AND 	OrderStat IN ('OKAY', 'GOOD)
    AND OfferCode IN ('105', '199', '122')
    
    
    select IBM_BPLAN = 
    (select count(*)
    from history a, Package.dbo.PackageType b
    where a.LeadRecordID = b.LeadRecordID 
    and a.MailDate = '20100210' 
    --and calldate >= '20100220'
    and a.OrderStat in ('OKAY', 'GOOD)
    and a.Priceplan <> ''
    and a.OfferCode in ('105', '199', '122')
    and a.priceplan in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
    '73216','73217','73218','73219')
    and b.price_plan not in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
    '73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
    '73232'))
    +
    (select count(*)
    from history a, Leads.dbo.LeadRecordVW_Inbound b
    where a.LeadRecordID = b.LeadRecordID 
    and a.MailDate  between '20100210' and '20100228'
    --and calldate >= '20100220'
    and a.OrderStat in ('OKAY', 'GOOD)
    and a.Priceplan <> ''
    and a.OfferCode in ('105', '199', '122')
    and (a.priceplan <> b.price_plan)
    and a.priceplan in ('72876','72877','72878','72879')
    and b.price_plan not in ('72876','72877','72879') 
    )
    
    Select  sum((1+len(NULLIF(LTRIM(PanelList),''))-len(replace(NULLIF(LTRIM(PanelList),''),' ','')))) as 'IBM_ACS'
    from history
    where OrderStat in ('OKAY', 'GOOD)
    and MailDate  between '20100210' and '20100228'
    --and calldate >= '20100220'
    and OfferCode IN ('105', '199', '122')
    and PanelList > ' '

    Into

    Code:
    select MailDate,
    
          COUNT (CASE WHEN 	CODEID LIKE '%545456%' OR 	
    			CODEID LIKE '%878974%'
    			and FileNameExported like '%NL%'
    			THEN 1 
                    	END) AS [A],
    
    	COUNT (CASE WHEN CODEID LIKE '%2312315%'
    		    OR	 CODEID LIKE '%48678%'
    		    THEN 1 
                        END) AS [B],
    
           COUNT (CASE WHEN CODEID LIKE '%23156%'
    		    OR	 CODEID LIKE '%2315648%'
    		    THEN 1 
                        END) AS [B,
    
           COUNT (CASE WHEN CODEID LIKE '%5457756%'
    		    OR	 CODEID LIKE '%2315645%'
    		    THEN 1 
                        END) AS [D],
    
           COUNT (CASE WHEN CODEID LIKE '%2315641%'
    		    OR	 CODEID LIKE '%24421412%'
    		    OR	 CODEID LIKE '%24421413%'
    		    OR	 CODEID LIKE '%24421414%'
    		    OR	 CODEID LIKE '%24421415%'
    		    OR	 CODEID LIKE '%2442141%'
    		    THEN 1 
                        END) AS [E],
    	 COUNT (CASE WHEN CODEID LIKE '%244214123%'
    		    OR	 CODEID LIKE '%244214126%'
    		    OR	 CODEID LIKE '%73408%'
    			THEN 1 
                        END) AS [F],
    	COUNT (CASE WHEN CODEID LIKE '%23121212%'
    		    OR	 CODEID LIKE '%23121215%'
    		    THEN 1 
                        END) AS [G],
    	COUNT (CASE WHEN CODEID LIKE '%231212111%'
    		    OR	 CODEID LIKE '%2312121112%'
    		    THEN 1 
                        END) AS [H],
    	sum (case when PanelList > ' '
    		and 
    		((1+len(NULLIF(LTRIM(PanelList),''))-len(replace(NULLIF(LTRIM(PanelList),''),' ','')))) 
    		THEN 1 
    		end) as[I]
    
    from history
    where OrderStat in ('OKAY', 'GOOD)
    and MailDate  between '20100210' and '20100228'
    and OfferCode IN ('105', '199', '122')
    I have no idea how to incorporate this part into a case:

    Code:
    select IBM_BPLAN = 
    (select count(*)
    from history a, Package.dbo.PackageType b
    where a.LeadRecordID = b.LeadRecordID 
    and a.MailDate = '20100210' 
    --and calldate >= '20100220'
    and a.OrderStat in ('OKAY', 'GOOD)
    and a.Priceplan <> ''
    and a.OfferCode in ('105', '199', '122')
    and a.priceplan in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
    '73216','73217','73218','73219')
    and b.price_plan not in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
    '73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
    '73232'))
    +
    (select count(*)
    from history a, Leads.dbo.LeadRecordVW_Inbound b
    where a.LeadRecordID = b.LeadRecordID 
    and a.MailDate  between '20100210' and '20100228'
    --and calldate >= '20100220'
    and a.OrderStat in ('OKAY', 'GOOD)
    and a.Priceplan <> ''
    and a.OfferCode in ('105', '199', '122')
    and (a.priceplan <> b.price_plan)
    and a.priceplan in ('72876','72877','72878','72879')
    and b.price_plan not in ('72876','72877','72879') 
    )

    and I also get an error here:

    Code:
    sum (case when PanelList > ' '
    		and 
    		((1+len(NULLIF(LTRIM(PanelList),''))-len(replace(NULLIF(LTRIM(PanelList),''),' ','')))) 
    		THEN 1 
    		end) as[I]
    at the Then part

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    What platform and version?

    Depending on that you might be able to try something like:

    Code:
    select MailDate,
    
          COUNT (CASE WHEN 	CODEID LIKE '%545456%' OR 	
    			CODEID LIKE '%878974%'
    ........
         ,(select count(*)
               from history a, Package.dbo.PackageType b
           where a.LeadRecordID .......) as IBM_BPLAN 
       from .......
    What is the error you are getting on the sum? We could make guesses all day that have nothing to do with your error.

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •