Problem With My Where Clause (long Query)

Hello,

I have to update a query that was given to me so that it displays only items that were created in a certain month and certain year (which I prompt the user for). I hard-coded a month and year to test (2008 year, 2 month). My results are including other months I'm not asking for.

I have marked my problems areas with /* Problem 1 */ and /* Problem 2 */ (same where clause n 2 locations).
|
What am I doing wrong? Doesn't matter what year or month I enter, I am always getting extra data

1st 3 columns of results (I removed the calculated fields)

 2008 1 Incoming
2008 1 Both
2008 2 Outgoing
2008 2 Incoming
2008 2 Both

I can't for the life of me figure out how to fix this up.

query below...

================================

DECLARE @TheYear  integer
DECLARE @TheMonth integer

SET @TheYear  = 2008
SET @TheMonth = 2


SELECT * FROM (
 SELECT 
  year(startime)  as yearstart,
  month(startime) as monthstart,
  directioncodename

, count(CASE

            WHEN  new_issuecategoryname is null

                  THEN activitycountvalue

            END ) as nullcall

, count(CASE

            WHEN  new_issuecategoryname='ACDelco Comment'

                  THEN activitycountvalue

            END ) as acdelcocommentcall

, count(CASE

            WHEN

            new_issuecategoryname='ACDelco General Inquiry'

                  THEN activitycountvalue

            END ) as acdgeneralinquirycall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco PPD'

                 

                  THEN activitycountvalue

            END ) as acdppdcall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco Promotion'

                 

                  THEN activitycountvalue

            END ) as acdpromotioncall

, count(CASE

            WHEN

            new_issuecategoryname='ACDelco Promotion - Calendar'

                 

                  THEN activitycountvalue

            END ) as acdpromotioncalendarcall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco TSS Program Enquiry'

                 

                  THEN activitycountvalue

            END ) as tssprogramenquirycall

, count(CASE

            WHEN 

                        new_issuecategoryname='ACDelco TSS Promotion'

                 

                  THEN activitycountvalue

            END ) as tsspromotioncall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco Training'

           

                  THEN activitycountvalue

            END ) as acdtrainingcall

, count(CASE

            WHEN 

                  new_issuecategoryname='ACDelco TSS Benefits'

                 

                  THEN activitycountvalue

            END ) as tssbenefitscall

, count(CASE

            WHEN 

                  new_issuecategoryname='ACDelco Other'

                 

                  THEN activitycountvalue

            END ) as acdothercall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco TAC Number'

                 

                  THEN activitycountvalue

            END ) as acdtacnumbercall

, count(CASE

            WHEN

            new_issuecategoryname='ACDelco PPD Promotion'

 

                  THEN activitycountvalue

            END ) as acdppdpromotioncall

, count(CASE

            WHEN 

      new_issuecategoryname='RealRewards - ISC'

                 

                  THEN activitycountvalue

            END ) as realrewardsisccall

, count(CASE

            WHEN

                  new_issuecategoryname='RealRewards - CounterPerson'

                 

                  THEN activitycountvalue

            END ) as realrewardscounterpersonscall

, count(CASE

            WHEN 

                        new_issuecategoryname='ACDelco Event'

                 

                  THEN activitycountvalue

            END ) as acdelcoeventcall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco TSS Lead'

                 

                  THEN activitycountvalue

            END ) as acdtssleadcall

, count(CASE

            WHEN

            new_issuecategoryname='ACDelco TSS Other'

                  THEN activitycountvalue

            END ) as acdtssothercall

, count(CASE

            WHEN  new_issuecategoryname is not null

                  and   new_issuecategoryname!='ACDelco Comment'

                  and   new_issuecategoryname!='ACDelco General Inquiry'

                  and   new_issuecategoryname!='ACDelco PPD'

                  and   new_issuecategoryname!='ACDelco Promotion'

                  and   new_issuecategoryname!='ACDelco Promotion - Calendar'

                  and   new_issuecategoryname!='ACDelco TSS Program Enquiry'

                  and   new_issuecategoryname!='ACDelco TSS Promotion'

                  and   new_issuecategoryname!='ACDelco Training'

                  and   new_issuecategoryname!='ACDelco TSS Benefits'

                  and   new_issuecategoryname!='ACDelco Other'

                  and   new_issuecategoryname!='ACDelco TAC Number'

                  and   new_issuecategoryname!='ACDelco PPD Promotion'

                  and   new_issuecategoryname!='RealRewards - ISC'

                  and   new_issuecategoryname!='RealRewards - CounterPerson'

                  and   new_issuecategoryname!='ACDelco Event'

                  and   new_issuecategoryname!='ACDelco TSS Lead'

                  and   new_issuecategoryname!='ACDelco TSS Other'

                  THEN activitycountvalue

            END ) as othercall

,count(activitycountvalue) as totalcall

 

FROM (

select

            startime =

                  CASE

                        WHEN filteredphonecall.new_cmgstartdatetime is not null

                              THEN filteredphonecall.new_cmgstartdatetime

                        WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null

                              THEN filteredphonecall.actualstart

                        ELSE

                              filteredphonecall.createdon        

                        END  

            , 1 as activitycountvalue

            , new_issuecategoryname

            , new_issuecategory

            , 'phone call' as activitytypecodename

            , filteredphonecall.new_languagename

            , filteredphonecall.directioncodename

from
  /* PROBLEM 1 */
                filteredphonecall

  WHERE
  (
    (
      filteredphonecall.new_cmgstartdatetime is not null
      AND
        (Year(filteredphonecall.new_cmgstartdatetime)  = @TheYear)
      AND  
      (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)
     )

    OR
                  (
     filteredphonecall.actualstart is not null 
     AND
     (Year(filteredphonecall.new_cmgstartdatetime)  = @TheYear)
     AND  
     (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)
    )
  
    OR
    (
     filteredphonecall.actualstart is not null
     AND
      (Year(filteredphonecall.actualstart)  = @TheYear)
    AND  
    (Month(filteredphonecall.actualstart) = @TheMonth)
    )

    OR
    (
     filteredphonecall.createdon is not null
     AND
       (Year(filteredphonecall.createdon)  = @TheYear)
     AND  
     (Month(filteredphonecall.createdon) = @TheMonth)
    )
  )

)as phoneactivities

GROUP BY  year(startime), month(startime), directioncodename

 

UNION ALL

 


/* KATHY1 */

SELECT  year(startime) as yearstart

, month(startime) as monthstart

,'Both' as directioncodename

, count(CASE

            WHEN  new_issuecategoryname is null

                  THEN activitycountvalue

            END ) as nullcall

, count(CASE

            WHEN  new_issuecategoryname='ACDelco Comment'

                  THEN activitycountvalue

            END ) as acdelcocommentcall

, count(CASE

            WHEN

            new_issuecategoryname='ACDelco General Inquiry'

                 

                  THEN activitycountvalue

            END ) as acdgeneralinquirycall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco PPD'

                 

                  THEN activitycountvalue

            END ) as acdppdcall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco Promotion'

                 

                  THEN activitycountvalue

            END ) as acdpromotioncall

, count(CASE

            WHEN

            new_issuecategoryname='ACDelco Promotion - Calendar'

                 

                  THEN activitycountvalue

            END ) as acdpromotioncalendarcall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco TSS Program Enquiry'

                 

                  THEN activitycountvalue

            END ) as tssprogramenquirycall

, count(CASE

            WHEN 

                        new_issuecategoryname='ACDelco TSS Promotion'

                 

                  THEN activitycountvalue

            END ) as tsspromotioncall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco Training'

           

                  THEN activitycountvalue

            END ) as acdtrainingcall

, count(CASE

            WHEN 

                  new_issuecategoryname='ACDelco TSS Benefits'

                 

                  THEN activitycountvalue

            END ) as tssbenefitscall

, count(CASE

            WHEN 

                  new_issuecategoryname='ACDelco Other'

                 

                  THEN activitycountvalue

            END ) as acdothercall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco TAC Number'

                 

                  THEN activitycountvalue

            END ) as acdtacnumbercall

, count(CASE

            WHEN

            new_issuecategoryname='ACDelco PPD Promotion'

 

                  THEN activitycountvalue

            END ) as acdppdpromotioncall

, count(CASE

            WHEN 

      new_issuecategoryname='RealRewards - ISC'

                 

                  THEN activitycountvalue

            END ) as realrewardsisccall

, count(CASE

            WHEN

                  new_issuecategoryname='RealRewards - CounterPerson'

                 

                  THEN activitycountvalue

            END ) as realrewardscounterpersonscall

, count(CASE

            WHEN 

                        new_issuecategoryname='ACDelco Event'

                 

                  THEN activitycountvalue

            END ) as acdelcoeventcall

, count(CASE

            WHEN 

            new_issuecategoryname='ACDelco TSS Lead'

                 

                  THEN activitycountvalue

            END ) as acdtssleadcall

, count(CASE

            WHEN

            new_issuecategoryname='ACDelco TSS Other'

                  THEN activitycountvalue

            END ) as acdtssothercall

, count(CASE

            WHEN  new_issuecategoryname is not null

                  and   new_issuecategoryname!='ACDelco Comment'

                  and   new_issuecategoryname!='ACDelco General Inquiry'

                  and   new_issuecategoryname!='ACDelco PPD'

                  and   new_issuecategoryname!='ACDelco Promotion'

                  and   new_issuecategoryname!='ACDelco Promotion - Calendar'

                  and   new_issuecategoryname!='ACDelco TSS Program Enquiry'

                  and   new_issuecategoryname!='ACDelco TSS Promotion'

                  and   new_issuecategoryname!='ACDelco Training'

                  and   new_issuecategoryname!='ACDelco TSS Benefits'

                  and   new_issuecategoryname!='ACDelco Other'

                  and   new_issuecategoryname!='ACDelco TAC Number'

                  and   new_issuecategoryname!='ACDelco PPD Promotion'

                  and   new_issuecategoryname!='RealRewards - ISC'

                  and   new_issuecategoryname!='RealRewards - CounterPerson'

                  and   new_issuecategoryname!='ACDelco Event'

                  and   new_issuecategoryname!='ACDelco TSS Lead'

                  and   new_issuecategoryname!='ACDelco TSS Other'

                  THEN activitycountvalue

            END ) as othercall

,count(activitycountvalue) as totalcall

 

FROM (

select

            startime =

                  CASE

                        WHEN filteredphonecall.new_cmgstartdatetime is not null

                              THEN filteredphonecall.new_cmgstartdatetime

                        WHEN filteredphonecall.actualstart is not null and filteredphonecall.new_cmgstartdatetime is null

                              THEN filteredphonecall.actualstart

                        ELSE

                              filteredphonecall.createdon        

                        END  

            , 1 as activitycountvalue

            , new_issuecategoryname

            , new_issuecategory

            , 'phone call' as activitytypecodename

            , filteredphonecall.new_languagename

            , filteredphonecall.directioncodename

from
                filteredphonecall

  /* PROBLEM  2 */
                filteredphonecall

  WHERE
  (
    (
      filteredphonecall.new_cmgstartdatetime is not null
      AND
        (Year(filteredphonecall.new_cmgstartdatetime)  = @TheYear)
      AND  
      (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)
     )

    OR
                  (
     filteredphonecall.actualstart is not null 
     AND
     (Year(filteredphonecall.new_cmgstartdatetime)  = @TheYear)
     AND  
     (Month(filteredphonecall.new_cmgstartdatetime) = @TheMonth)
    )
  
    OR
    (
     filteredphonecall.actualstart is not null
     AND
      (Year(filteredphonecall.actualstart)  = @TheYear)
    AND  
    (Month(filteredphonecall.actualstart) = @TheMonth)
    )

    OR
    (
     filteredphonecall.createdon is not null
     AND
       (Year(filteredphonecall.createdon)  = @TheYear)
     AND  
     (Month(filteredphonecall.createdon) = @TheMonth)
    )
  )

)as phoneactivities

GROUP BY  year(startime), month(startime)

) as orderedresults

order by  yearstart, monthstart, directioncodename DESC;