ozman_ny
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.locationNam e as NotHomeLocation, count(distinct Customers.CustomerID) as NumberofCustomers from Customers
Join Locations
on Customers.locationID = Locations.LocationID
Join Sales
on Customers.CustomerID=Sales .CustomerI D
Join Locations AS SalesLocations
on Sales.LocationID=Sales.Loc ationID
Join Sales2
on Customers.CustomerID=Sales 2.Customer ID
WHERE Customers.LocationID=1953 and Sales.CustomerID=Customers .CustomerI D and year(Sales.ClosedOrderDate )>=2015 and Sales.ClosedOrderDate>Sale s2.LastVis itDate
group by Sales.CustomerID
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.locationNam
Join Locations
on Customers.locationID = Locations.LocationID
Join Sales
on Customers.CustomerID=Sales
Join Locations AS SalesLocations
on Sales.LocationID=Sales.Loc
Join Sales2
on Customers.CustomerID=Sales
WHERE Customers.LocationID=1953 and Sales.CustomerID=Customers
group by Sales.CustomerID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Closing. Provided what was asked.