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.

  • Parent-Child one-to-one Relation same Table
  • T-SQL How to select rows without duplicate values from one column?
  • querying binary column using like in sql server
  • How can I delete in SSIS a FlatFile in ControlFlow when it has dynamic name?
  • how to debug a recursive trigger
  • SQL Server command in vb.net not working cause all in one sentence
  • 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:

    • Client ABC has placed 45 orders over 35 total weeks at location A
    • Client ABC has placed 35 orders over 15 total weeks at location B
    • Client ABC has placed 15 orders over 15 total weeks at location C

    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.

  • Manupilating previous month data according to current month
  • One Solution collect form web for “SQL Group by Client Location”

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.