Link to home
Start Free TrialLog in
Avatar of ozman_ny
ozman_nyFlag for United States of America

asked on

Issues with My query Part II

I had this question after viewing Have issues with Query MySQL.

I have the query working but now we have a new problem to solve.  

" I have to select Customers for example location 1953, that went to other locations and never came back to the originating (home location)"

The tables that I have to work are Sales, Locations, Customers.  
TableSales Contains: LocationID, CustomerID, and all the sales dates(ClosedOrderDate) for the CustomerID
Table Locations: Contains Locations ID and Location Name
Table Customers contains CustomersID, Location ID, for the customers

To facilitate the search for the last date the customer came to the "Home Location" I created a table called Sales2, where I keep the following
LocationID
CustomerID
LastVisitDate (this is the last Visit to the Home Location)

The result Im expecting is: The # of Distinct Customers that go from Home Location (1953) to each of the other 80 Locations

HomeLocation        NotHomeLocation     # of Customers
1953                             3272-NewYork2          4
1953                             3922-NewYork3          6
1953                             28900-NewJersey1     10
etc..
The results Im getting are:
HomeLocation        NotHomeLocation     # of Customers
1953                             3272-NewYork2          1
1953                             3272-NewYork2          1
1953                             3272-NewYork2          1
1953                             3272-NewYork2          1
etc.

The query I wrote is:

Select  Customers.LocationID, Locations.locationName as HomeLocation,  SalesLocations.locationName as NotHomeLocation, count(distinct Customers.CustomerID) as NumberofCustomers from Customers

   Join Locations
   on Customers.locationID = Locations.LocationID
   Join Sales
    on Customers.CustomerID=Sales.CustomerID
      Join Locations AS SalesLocations
     on Sales.LocationID=Sales.LocationID
    Join Sales2
    on Customers.CustomerID=Sales2.CustomerID

    WHERE Customers.LocationID=1953 and Sales.CustomerID=Customers.CustomerID  and year(Sales.ClosedOrderDate)>=2015 and Sales.ClosedOrderDate>Sales2.LastVisitDate
    group by Sales.CustomerID
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Closing. Provided what was asked.