question

Hello all: Basically what I need to do is check if each ID gave FiscalYr1 AND FiscalYr2 AND FiscalYr3 and FiscalYr4. This is a per person thing. Here is my query so far but I cant quite figure out how to make the where statement look at each person. Each time they gave a donation, it is on one separate line per donation. All i want to verify is that they gave any amount in each of the years specified. Any help would be greatly appreciated. Thanks! (ps, how do i format my SQL text in here) Select * From (Select PREFERRED_MAIL_VIEW.ID, CONTRIBUTION_VIEW.Prefix, PREFERRED_MAIL_VIEW.[First Name], PREFERRED_MAIL_VIEW.[Middle Name], PREFERRED_MAIL_VIEW.[Last Name], PERSON.SUFFIX, PREFERRED_MAIL_VIEW.[Company Name], PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.Cell, PREFERRED_MAIL_VIEW.Home, PREFERRED_MAIL_VIEW.[Address Type], PREFERRED_MAIL_VIEW.[Preferred Email], PREFERRED_MAIL_VIEW.PERSON_STATUS, PREFERRED_MAIL_VIEW.[Mail Rules 1], PREFERRED_MAIL_VIEW.[Mail Rules 2], PREFERRED_MAIL_VIEW.[Mail Rules 3], Case When Month(CONTRIBUTION.CONTRIB_DONOR_DATE) In (1, 2, 3, 4, 5, 6, 7, 8) Then Cast(Year(CONTRIBUTION.CONTRIB_DONOR_DATE) - 1 As varchar(4)) + '-' + Cast(Right(Year(CONTRIBUTION.CONTRIB_DONOR_DATE), 2) As varchar(4)) Else Cast(Year(CONTRIBUTION.CONTRIB_DONOR_DATE) As varchar(4)) + '-' + Cast(Right(Year(CONTRIBUTION.CONTRIB_DONOR_DATE) + 1, 2) As varchar(4)) End FiscalYr, CONTRIBUTION.CONTRIB_DONOR_DATE, CONTRIBUTION.CONTRIB_TYPE As Type, CONTRIBUTION.CONTRIB_PLEDGE As [RD or Pledge ID], IsNull(CONTRIB_DONOR_DESIG.CONDES_HARD_CREDIT_AMT, 0) As [Hard Credit Amount], IsNull(CONTRIB_DONOR_DESIG.CONDES_SOFT_CREDIT_AMT, 0) As [Soft Credit Amount], IsNull(CONTRIB_DONOR_DESIG.CONDES_MATCH_CREDIT_AMT, 0) As [Match Credit Amount], DESIGNATION.DESIG_LONG_DESC As Designation, CONTRIB_DONOR_DESIG.CONDES_CAMPAIGN As Campaign, CONTRIBUTION_VIEW.BIRTH_DATE, CONTRIBUTION_VIEW.[Life Largest Amount], CONTRIBUTION_VIEW.[Largest Amount Date], CONTRIBUTION_VIEW.[Life Smallest Amount], CONTRIBUTION_VIEW.[Smallest Amount Date], CONTRIBUTION_VIEW.[Last Amount], CONTRIBUTION_VIEW.[Last Amount Date], CONTRIBUTION_VIEW.[First Amount], CONTRIBUTION_VIEW.[First Amount Date], CONTRIBUTION_VIEW.[Lifetime Giving] From CONTRIBUTION Left Outer Join CONTRIBUTION_LS On CONTRIBUTION_LS.CONTRIBUTION_ID = CONTRIBUTION.CONTRIBUTION_ID Full Outer Join CONTRIB_DONOR_DESIG On CONTRIBUTION_LS.CONTRIB_DONOR_DESIGS = CONTRIB_DONOR_DESIG.CONTRIB_DONOR_DESIG_ID Inner Join DESIGNATION On CONTRIB_DONOR_DESIG.CONDES_DESIGNATION = DESIGNATION.DESIGNATION_ID Inner Join CONSTITUENT_LS On CONTRIB_DONOR_DESIG.CONDES_CONTRIB_DONOR = CONSTITUENT_LS.CN_CONTRIB_DONORS Full Outer Join PREFERRED_MAIL_VIEW On CONSTITUENT_LS.CONSTITUENT_ID = PREFERRED_MAIL_VIEW.ID Full Outer Join PERPHONE On PERPHONE.ID = PREFERRED_MAIL_VIEW.ID Inner Join PERSON On CONSTITUENT_LS.CONSTITUENT_ID = PERSON.ID Inner Join PERSON_LS On PERSON_LS.ID = PERSON.ID Full Join ADR_PHONES On ADR_PHONES.ADDRESS_ID = PREFERRED_MAIL_VIEW.ID Left Join CONTRIBUTION_VIEW On PREFERRED_MAIL_VIEW.ID = CONTRIBUTION_VIEW.ID Where CONTRIBUTION.CONTRIBUTION_ID Is Not Null Group By PREFERRED_MAIL_VIEW.ID, CONTRIBUTION_VIEW.Prefix, PREFERRED_MAIL_VIEW.[First Name], PREFERRED_MAIL_VIEW.[Middle Name], PREFERRED_MAIL_VIEW.[Last Name], PERSON.SUFFIX, PREFERRED_MAIL_VIEW.[Company Name], PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.Cell, PREFERRED_MAIL_VIEW.Home, PREFERRED_MAIL_VIEW.[Address Type], PREFERRED_MAIL_VIEW.[Preferred Email], PREFERRED_MAIL_VIEW.PERSON_STATUS, PREFERRED_MAIL_VIEW.[Mail Rules 1], PREFERRED_MAIL_VIEW.[Mail Rules 2], PREFERRED_MAIL_VIEW.[Mail Rules 3], CONTRIBUTION.CONTRIB_DONOR_DATE, CONTRIBUTION.CONTRIB_TYPE, CONTRIBUTION.CONTRIB_PLEDGE, DESIGNATION.DESIG_LONG_DESC, CONTRIB_DONOR_DESIG.CONDES_CAMPAIGN, CONTRIBUTION_VIEW.BIRTH_DATE, CONTRIBUTION_VIEW.[Life Largest Amount], CONTRIBUTION_VIEW.[Largest Amount Date], CONTRIBUTION_VIEW.[Life Smallest Amount], CONTRIBUTION_VIEW.[Smallest Amount Date], CONTRIBUTION_VIEW.[Last Amount], CONTRIBUTION_VIEW.[Last Amount Date], CONTRIBUTION_VIEW.[First Amount], CONTRIBUTION_VIEW.[First Amount Date], CONTRIBUTION_VIEW.[Lifetime Giving], CONTRIB_DONOR_DESIG.CONDES_HARD_CREDIT_AMT, CONTRIB_DONOR_DESIG.CONDES_SOFT_CREDIT_AMT, CONTRIB_DONOR_DESIG.CONDES_MATCH_CREDIT_AMT Having CONTRIBUTION.CONTRIB_TYPE In ('GF', 'PP')) As giving