Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Surely this can be one select statement??
Message
Information générale
Forum:
Visual FoxPro
Catégorie:
Codage, syntaxe et commandes
Titre:
Surely this can be one select statement??
Versions des environnements
Visual FoxPro:
VFP 9 SP2
OS:
Windows XP SP2
Network:
Windows 2003 Server
Database:
MS SQL Server
Divers
Thread ID:
01651131
Message ID:
01651131
Vues:
88
I think I'm doing something dumb here - 3 select statements but seems like there should be a way to do this in just one????

First table: Customer
customer_id
customer_name

Second table: Visits
visits_id
customer_id
visit_date
location_id

...what I want is customer_id, customer_name, visit_id, visit_date and location_id for all location_id's where a customer visited more than once on the same day for a particular year.
This is what I'm doing now - but like I said, seems like there should be a better way.
----------------------------------------------------------
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
...I suppose I could leave it this way but I'd really like a cleaner way to do it.
ICQ 10556 (ya), 254117
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform