Trying to figure out a SQL query problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trying to figure out a SQL query problem

Author

Topic

Father Branigan
Starting Member

5 Posts

Posted - 2011-05-04 : 22:12:52

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.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-05 : 07:15:49

[code]
SELECT min(PostDate), clients.clientkey, masterclientkey, name
FROM clients c
INNER JOIN
(
select postdate,clientKey
,[row] = row_number over (partition by clientkey order by postdate asc)
from transactions
) t

ON c.clientkey = t.clientkey
WHERE
(
masterclientkey in (1436)
or c.clientkey in (1436)
)
and t.type = 1
and t.status = 0
and aramt <> 0
and postdate >= '20110101' and row = 1
GROUP BY c.clientkey, masterclientkey, [name]
[/code]

Jim

Everyday I learn something that somebody else already knew

Father Branigan
Starting Member

5 Posts

Posted - 2011-05-05 : 16:42:44

Thanks for your help. I tried the query but I'm getting an error:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'over'.".

I have a screenshot of the query editor and results window, but I'm not sure if I can attach it or how.

Father Branigan
Starting Member

5 Posts

Posted - 2011-05-05 : 16:45:03

If it helps, here is the SQL server version:

9.00.4053.00 SP3 Standard Edition (64-bit)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-05 : 16:55:47

Doh!

,[row] = row_number () over (partition by clientkey order by postdate asc)

Jim

Everyday I learn something that somebody else already knew

Father Branigan
Starting Member

5 Posts

Posted - 2011-05-05 : 18:59:16

OK. I'd like to break it down so that I can understand the functionality. I hope you can bear with me!

Here is the innermost clause...


select postdate,clientKey, type, status, aramt
       ,[row] = row_number () over (partition by clientkey order by postdate asc) 
from transactions
where type = 1
and status = 0
and aramt <> 0

Notice that I moved the type, status, and aramt conditions into this inner clause. I think this is appropriate because these are the only transaction types I want to include so if the client has some other type or status it shouldn't count in this case.

So my understanding is that you have added a row number for the result set, and the result set is ordered by clientkey, and then postdate for each clientkey.

Is my understanding correct?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-06 : 07:18:54

Correct. The partition by clause determines the groupings that are to be ordered by postdate. If I had made the ordering descending, then the same query would give you the latest postdate.

Jim

Everyday I learn something that somebody else already knew

Father Branigan
Starting Member

5 Posts

Posted - 2011-05-06 : 18:36:38

Thanks again for your help.

Ah, I see. So the Row field that you created assigns a number for all of the transactions for each client, ordered by postdate. And then the row number starts over at 1 for the next client. Very cool. I have never seen "over / partition by" used before.

OK. So row 1 for each clientkey now represents the postdate for the first non-zero type 1 status 0 transaction.

Now for the rest of the code I have this:


SELECT min(PostDate), c.clientkey, masterclientkey, name
FROM clients  c
INNER JOIN 
(
	select postdate,clientKey, type, status, aramt
		 ,[row] = row_number () over (partition by clientkey order by postdate asc) 
	from transactions
	where type = 1
	and status = 0
	and aramt <> 0
) t
ON c.clientkey = t.clientkey
WHERE
(
	masterclientkey in (6672)
	or c.clientkey in (6672)
)
and t.postdate >= '20110101' and row = 1
GROUP BY c.clientkey, masterclientkey, [name]
order by c.name

I ran this code and I see the following.

2011-01-10,6671,6672,Z-Baker Logistics

The problem is that this clientkey 6671 has a sibling clientkey 4839 who also has a masterclientkey of 6672 (i.e. 6671 and 4839 share the same parent). The first transaction for 4839 was on 2010-01-14 so this would not be considered a new client and therefore the query should return nothing.

My ultimate goal is to get the entire list of new clients and only new clients, and then to sum up the transactions for each broker. I'm really hoping that it can be done in a single query and not require a special function to be written.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources