May 10, 2011 at 10:00 pm
Goal:
- Return the sum of all transactions for all NEW clients, grouped by broker Name
Tables and Fields:
Brokers
- BrokerKey (Primary Key)
- BrokerName
ClientBrokers
- ClientKey (Foreign Key)
- BrokerKey (Foreign Key)
Clients
- ClientKey (Primary Key)
- MasterClientKey
- ClientName
Transactions
- TransactionID (Primary Key)
- ClientKey (Foreign Key)
- PostDate
- Amount
Notes:
- A new client is one whose very first non-zero transaction occurred (posted) sometime this year
- ClientMasterKey refers to a parent client
* This can happen if a company changes names or is bought by another client company (forming a "client set" of multiple related clients)
* In these cases, the first transaction can come from ANY member of the "client set", including the parent client or a sibling client
I ran the following SQL to see the first post date for each member of one particular "client set":
select min(PostDate), clients.clientkey, masterclientkey, name
from clients, transactions
where clients.clientkey = transactions.clientkey
and masterclientkey in (1436)
or clients.clientkey in (1436)
and transactions.type = 1
and transactions.status = 0
and aramt <> 0
group by clients.clientkey, masterclientkey, name
...and got...
2005-08-24, 724, 1436, AB Corp
1995-09-19, 1436, 0, AB Corp dba AB Alpha Corp
2007-11-15, 1962, 1436, SignalTech AB
2006-05-03, 989, 1436, SignalTech AB of Colorado
2007-04-04, 1421, 1436, SignalTech Group
Unfortunately I don't know all of the business rules about when clientkeys and masterclientkeys are assigned, but my observation has been that
there can be transactions under some or all of the related members in a client set. The tricky thing for me has been to only include
NEW clients for the sum of transactions for each broker.
Can anyone help? It would be greatly appreciated.
May 11, 2011 at 10:20 am
If your objective is to :
Return the sum of all transactions for all NEW clients, grouped by broker Name
Then why are you doing the following?
group by clients.clientkey, masterclientkey, name
Why are you doing a min(PostDate)?
Why don't you include an aggregate of the Amount?
If you want to:
only include NEW clients for the sum of transactions for each broker.
Why are you not joining on broker?
What does the following code 1436 mean? IN (1436)
You may want to avoid the following statement for it will result in a table scan?
and aramt <> 0
What is aramt?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 12:02 pm
Sorry about that. I should have been more clear. The SQL I posted was just my first attempt at starting to figure the whole problem out. I do realize that the final query will join in brokers and aggregate the transactions, but my thought process was that first I would just like to isolate a list of the new clients as the sub-query and then I could build on that.
To answer your questions:
1) ArAmt is the amount field in the transactions table. In the list of tables/fields, I called it Amount, but it's really ArAmt.
2) 1436 is the client key for one of the client records and is the masterclientkey for the other records in the "Client Set". I have been considering 1436 to be the parent record of the other 4, which are all siblings of one another because they share the same parent. The query would be easy if the parent always had the first transaction, because then I could just check to see if a client or it's parent had a transaction in the current year and, if so, it would be considered a new client. The complication arises in that any of the sibling records could have transactions prior to the current year, so in those cases it would not be considered a new client. This can happen because the "Client Set" is composed of merged and sometimes renamed companies so transactions are still listed under their original clientkey. Once the rename or merge happens, the masterclientkey is added to the client record to indicate that another client is considered to be the "master" client for this set.
3) The Type = 1, Status = 0, ArAmt <> 0 are the only transactions that should count so that's why I included those in my initial query.
May 11, 2011 at 12:43 pm
How do you identify a new client?
I would start by rewriting a query that incudes all of the column, aggregates and tables that you need.
Try to avoid <>, instead use aramt > 0.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 1:48 pm
A new client is one whose very first non-zero transaction occurred (posted) sometime this year. However, the first transaction could come from any member of a client set, meaning it's parent (masterclientkey) or any sibling (clients that have the same masterclientkey). If the parent or any sibling has it's first transaction prior to this year, it's not considered a new client.
May 11, 2011 at 2:25 pm
As you mentioned you do not know all of the Business Requirements.
You may want to get clarification on the requirements and fully understand them before you proceed.
You stated that a new client is one whose very first non-zero transaction occurred (posted) sometime this year.
I do not see how the MIN(POSTDATE) helps you achieve this objective.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 3:17 pm
OK. I figured that if I got the min(postdate) for each client set then I could compare the min(postdate) to Jan. 1, 2011 and only include the new ones. If I can get a list of new client sets and give each set a Set ID, then I could perhaps sum up all of the transactions for each client set and then group it by broker.
May 11, 2011 at 3:28 pm
I'm sorry but I do not follow the Business Logic....
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 3:41 pm
Which part of the business logic don't you understand? I thought that I explained a lot of detail there and spelled out the query's goal explicitly.
May 11, 2011 at 3:54 pm
You stated that:
"Unfortunately I don't know all of the business rules about when clientkeys and masterclientkeys are assigned, but my observation has been ..."
What part of the business rules do you not understand and what actions have you taken to close the gap?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 4:00 pm
If you do not not know what the business requirements how can you write a query that satisfies the business requirements.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 11, 2011 at 4:20 pm
Oh, I see. I shouldn't have mentioned that because it was confusing. I just meant that I don't know how the broker team determines which client is the master client when 2 clients merge or when 1 client acquires another. However, I can see what the data looks like in the clients table when that happens. In my first post I displayed the 5 records that make up a client set showing the 5 distinct clientkeys, 4 of which have a reference to a masterclientkey (parent). There are transactions for each of the clientkey records so I need to determine if any of those 5 clientkeys had a transaction before Jan. 1, 2011. If so, then I want to eliminate them from the query because they are not considered to be new clients.
Once I have a list of new clients, the idea is to sum up the transactions and group them by broker. The end result would be something like
Broker 1, $1,520
Broker 2, $2,300
Broker 3, $1,150
This means that Broker 1 had $1,520 worth of transactions from new clients whose first transaction was this year. The broker could have transactions from older clients as well, but they would not be counted.
May 12, 2011 at 2:23 am
Saying things like
However, I can see what the data looks like in the clients table when that happens.
is not very helpful to people who are trying to understand your requirements and help you solve your problem - which does not sound difficult, by the way.
If you were able to provide sample table schemas, input data and desired output data this would all be solved within the space of one post. See the link in my signature for details of how best to frame your question.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply