> >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 ?EndOfTheYear > >>
>>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 >>>>