SELECT cust.Customer_id, cust.Customer_name, v.visit_date,; v.location_id, v.Visits_id; FROM ; hpmg!Customer cust ; INNER JOIN hpmg!Visits v ; ON cust.Customer_id = v.Customer_id; inner join (select Customer_id, visit_date, location_id ; from hpmg!Visits ; WHERE visit_date between ?StartOfTheYear and ?EndOfTheYear ; GROUP BY Customer_id, visit_date, location_id ; HAVING COUNT(*) > 1 ) as MultipleVisits ON v.Customer_id = MultipleVisits.Customer_ID ; AND v.visit_date = MultipleVisits.visit_date and v.Location_id = MultipleVisits.Location_id; WHERE v.visit_date between ?StartOfTheYear and ?EndOfTheYearI also suggest to use date column as is if you have index on date column and not on year(date) (In the latter case use your original where).
>SELECT Customer.Customer_id, Customer.Customer_name, Visits.visit_date,; > Visits.location_id, Visits.Visits_id, 0 AS vcount; > FROM ; > hpmg!Customer ; > LEFT OUTER JOIN hpmg!Visits ; > ON Customer.Customer_id = Visits.Customer_id; > WHERE YEAR(Visits.visit_date) = ( ?lnYear) INTO CURSOR curHold_1 >>
> SELECT Customer.Customer_id, Visits.visit_date, Visits.location_id,; > COUNT(*) AS vcount; > FROM ; > hpmg!Customer ; > LEFT OUTER JOIN hpmg!Visits ; > ON Customer.Customer_id = Visits.Customer_id; > WHERE YEAR(Visits.visit_date) = ( 2015 ); > GROUP BY Customer.Customer_id, Visits.visit_date, Visits.location_id; > HAVING ( vcount ) > ( 1 ) INTO CURSOR curHold_2 >>
> SELECT curHold_1.Customer_id, curHold_1.Customer_name, curHold_1.Visits_id,; > curHold_1.visit_date, curHold_1.location_id; > FROM ; > curHold_1 ; > INNER JOIN curHold_2 ; > ON curHold_1.Customer_id = curHold_2.Customer_id; > WHERE ( curHold_1.Customer_id = curHold_2.Customer_id; > AND curHold_1.visit_date = curHold_2.visit_date ); > AND curHold_1.location_id = curHold_2.location_id >>