Results 1 to 4 of 4

Thread: Date Problem

  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: Date Problem

    When I execute this sql it gives me random dates as oppose to the one I specified.


    Code:
    select  a.date,
    
          COUNT (CASE WHEN a.FCODE LIKE '%1233%'
    		    OR	 a.FCODE LIKE '%12441%'
    		    THEN a.FCODE
                        END) AS 'A',
           COUNT (CASE WHEN a.FCODE LIKE '%124412412%'
    		    OR	 a.FCODE LIKE '%6554%'
    		    THEN a.FCODE
                        END) AS 'B',
           COUNT (CASE WHEN a.FCODE LIKE '%456564%'
    		    OR	 a.FCODE LIKE '%45654%'
    		    THEN a.FCODE
                        END) AS 'C',
           COUNT (CASE WHEN a.FCODE LIKE '%234234%'
                        OR	 a.FCODE LIKE '%23444%'
    		    THEN a.FCODE
                        END) AS 'D',
    	 COUNT (CASE WHEN a.FCODE LIKE '%1111%'
    		    OR	 a.FCODE LIKE '%7651324%'
    		    OR	 a.FCODE LIKE '%2341111%'		
    		    THEN a.FCODE
                        END) AS 'E',
    	 COUNT (CASE WHEN a.FCODE LIKE '%876568%'
    		    OR	 a.FCODE LIKE '%567856%'
    		    THEN a.FCODE
                        END) AS 'F',
    	 COUNT (CASE WHEN a.FCODE LIKE '%234525%'
    		    OR	 a.FCODE LIKE '%7322211%'
    		    THEN a.FCODE
                        END) AS 'G',
    	  
    	   PACKAGEB = count (case when a.priceplan in ('73960','73961','73962','74558','74566','74569','74577','74591','74592','74593',
    						'74594','74595','74596','74608','74609','74610','74611','74612','74613','75310','75311','75312', '83234','83241', '83236', '83237', 
    						'83238')
    
    	and b.price_plan not in ('73960','73961','73962','74558','74566','74569','74577','74591','74592','74593',
    '74594','74595','74596','74608','74609','74610','74611','74612','74613','75310','75311','75312', '83234', '83241', '83236', '83237', 
    '83238') then 1 else null end) +
    count (case when a.priceplan <> b.price_plan
    and a.priceplan in ('73442', '73443', '83234','83241', '83236', '83237', '83238')
    and b.price_plan not in ('73442', '73443', '83234','83241', '83236', '83237', '83238') then 1 else null end),
    sum (case when a.ACCCODE > ' '
    then ((1+len(NULLIF(LTRIM(a.ACCCODE t),''))-len(replace(NULLIF(LTRIM(a.ACCCODE ),''),' ',''))))
    end ) as [CODESUM]
    
    from history a
    left join Mobile.dbo.Records b
    on a.LeadRecordID = b.LeadRecordID 
    and a.State in ('Processing', 'Fulfilled Order')
    and a.date between '20100201' and '20100301'
    and a.PROMCODE IN ('AB123', 'CB123','AS123')
    group by a.date

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need to format the dates in your WHERE clause:
    Code:
    select  a.date,
    
          COUNT (CASE WHEN a.FCODE LIKE '&#37;1233%'
    		    OR	 a.FCODE LIKE '%12441%'
    		    THEN a.FCODE
                        END) AS 'A',
           COUNT (CASE WHEN a.FCODE LIKE '%124412412%'
    		    OR	 a.FCODE LIKE '%6554%'
    		    THEN a.FCODE
                        END) AS 'B',
           COUNT (CASE WHEN a.FCODE LIKE '%456564%'
    		    OR	 a.FCODE LIKE '%45654%'
    		    THEN a.FCODE
                        END) AS 'C',
           COUNT (CASE WHEN a.FCODE LIKE '%234234%'
                        OR	 a.FCODE LIKE '%23444%'
    		    THEN a.FCODE
                        END) AS 'D',
    	 COUNT (CASE WHEN a.FCODE LIKE '%1111%'
    		    OR	 a.FCODE LIKE '%7651324%'
    		    OR	 a.FCODE LIKE '%2341111%'		
    		    THEN a.FCODE
                        END) AS 'E',
    	 COUNT (CASE WHEN a.FCODE LIKE '%876568%'
    		    OR	 a.FCODE LIKE '%567856%'
    		    THEN a.FCODE
                        END) AS 'F',
    	 COUNT (CASE WHEN a.FCODE LIKE '%234525%'
    		    OR	 a.FCODE LIKE '%7322211%'
    		    THEN a.FCODE
                        END) AS 'G',
    	  
    	   PACKAGEB = count (case when a.priceplan in ('73960','73961','73962','74558','74566','74569','74577','74591','74592','74593',
    						'74594','74595','74596','74608','74609','74610','74611','74612','74613','75310','75311','75312', '83234','83241', '83236', '83237', 
    						'83238')
    
    	and b.price_plan not in ('73960','73961','73962','74558','74566','74569','74577','74591','74592','74593',
    '74594','74595','74596','74608','74609','74610','74611','74612','74613','75310','75311','75312', '83234', '83241', '83236', '83237', 
    '83238') then 1 else null end) +
    count (case when a.priceplan <> b.price_plan
    and a.priceplan in ('73442', '73443', '83234','83241', '83236', '83237', '83238')
    and b.price_plan not in ('73442', '73443', '83234','83241', '83236', '83237', '83238') then 1 else null end),
    sum (case when a.ACCCODE > ' '
    then ((1+len(NULLIF(LTRIM(a.ACCCODE t),''))-len(replace(NULLIF(LTRIM(a.ACCCODE ),''),' ',''))))
    end ) as [CODESUM]
    
    from history a
    left join Mobile.dbo.Records b
    on a.LeadRecordID = b.LeadRecordID 
    and a.State in ('Processing', 'Fulfilled Order')
    and a.date between '2010-02-01' and '2010-03-01'
    and a.PROMCODE IN ('AB123', 'CB123','AS123')
    group by a.date
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2010
    Posts
    10
    Quote Originally Posted by Pat Phelan View Post
    You need to format the dates in your WHERE clause
    and a.date between '2010-02-01' and '2010-03-01'
    and a.PROMCODE IN ('AB123', 'CB123','AS123')
    group by a.date[/code]-PatP
    I tried that but it still didnt work. Do you think it's because of my join tables?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mzsuga View Post
    Do you think it's because of my join tables?
    no

    change this --
    Code:
    from history a
    left join Mobile.dbo.Records b
    on a.LeadRecordID = b.LeadRecordID 
    and a.State in ('Processing', 'Fulfilled Order')
    and a.date between '20100201' and '20100301'
    and a.PROMCODE IN ('AB123', 'CB123','AS123')
    group by a.date
    to this --
    Code:
    from history a
    left join Mobile.dbo.Records b
    on a.LeadRecordID = b.LeadRecordID 
    WHERE a.State in ('Processing', 'Fulfilled Order')
    and a.date between '20100201' and '20100301'
    and a.PROMCODE IN ('AB123', 'CB123','AS123')
    group by a.date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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