CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2012data-manipulation
Sassy Llama
Sassy Llama

Reputation: 255

SQL Group by Client Location

Sample of Data I am trying to manipulate

Order  | OrderDate | ClientName| ClientAddress |  City  | State|  Zip  |
-------|-----------|-----------|---------------|--------|------|-------|
CO101  | 1/5/2015  | Client ABC| 101 Park Drive| Boston |  MA  | 02134 | 
C0102  | 2/6/2015  | Client ABC| 101 Park Drive| Boston |  MA  | 02134 | 
C0103  | 1/7/2015  | Client ABC| 354 Foo Pkwy  | Dallas |  TX  | 75001 |
C0104  | 3/7/2015  | Client ABC| 354 Foo Pkwy  | Dallas |  TX  | 75001 |
C0105  | 5/7/2015  | Client XYZ| 1 Binary Road | Austin |  TX  | 73301 | 
C0106  | 1/8/2015  | Client XYZ| 1 Binary Road | Austin |  TX  | 73301 | 
C0107  | 7/9/2015  | Client XYZ| 51 Testing Rd | Austin |  TX  | 73301 |

I have a database setup in MS-SQL Server with all client orders for the past two year period. Some clients only have one location, others have multiple locations. I would like to write a script that will show me the number of orders a customer placed by location over the total number of weeks there was at least one order.

Based on the results of this script, I would like to be able to deduce every customer location's summary of unique orders (placed at various times). For example:

I would like see this information for each unique location for each client. I am not sure how to aggregate the data in such a way. Here is where I am at with my script: 

SELECT t1.ClientName, (SELECT DISTINCT t2.ClientAddress), COUNT(DISTINCT t2.Orders) AS TotalOrders, 

DATEPART(week, t1.OrderDate) AS Week
FROM database t1 

INNER JOIN database t2 on t1.Orders = t2.Orders

GROUP BY DATEPART(week, t1.OrderDate), t1.ClientAddress, t2.ClientAddress 

HAVING COUNT(DISTINCT t2.SalesOrder) > 1
ORDER BY TotalOrders DESC

The results that I get show me the unique orders by location by week, but I'm not sure how to count the number of weeks in the way that I need; I have tried writing subqueries but I keep running into issues. I realize that in this script I am showing number of order by location by each individual week, I would like to count the total number of weeks within the time frame of where there is at least one order.

The results structure is as followed: 

| ClientName| ClientAddress |  TotalOrders | Week |
|-----------|---------------|--------------|------|
|Client ABC |101 Park Drive |      30      |  21  | 
|Client ABC |101 Park Drive |      29      |  13  | 
|Client ABC |101 Park Drive |      28      |  10  | 
|Client XYZ |1 Binary Road  |      27      |  19  | 
|Client XYZ |1 Binary Road  |      25      |  7   | 
|Client XYZ |51 Testing Rd  |      22      |  9   |

Any and all help would be greatly appreciated; thank you in advance.

Upvotes: 1

Views: 272

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Isn't this what you want?

SELECT t1.ClientName, ClientAddress, COUNT(DISTINCT t1.Orders) AS TotalOrders, 

COUNT(DISTINCT DATEPART(week, t1.OrderDate)) AS Weeks
FROM database t1     

GROUP BY t1.ClientName, t1.ClientAddress

HAVING COUNT(DISTINCT t2.SalesOrder) > 1
ORDER BY TotalOrders DESC

I don't really follow why you're doing a self-join. Seems useless to me, but I left it in, just in case, and to focus only on the change I made to get your result.

Upvotes: 2

Related Questions