Little puzzle on data selection

I have the following data (very simplified version)

TransactionId Agent_Code
---- ----
191462 95328C
205427 000024C
205427 75547C

Agent Code 75547C is a corporate agent. The others are not. I have a
list of corporate codes so I can query against it, BUT what I want to
do is...

Return a unique TransactionId and max of the AgentCode, but if the
Agent is a corporate agent, I need to return max of the corporate agent
codes. We can have multiple agents against the transaction and
sometimes have a mix of corporate and none corporate agents. What we
need to do is see the corporate adviser if there is one. I only want 1
record per TransactionId.

We derive more data (sales hierarchy) from this, so are not interested
in anything other than the maximum, but need to know if it was
corporate which therefore gives me a different hierarchy later.

Ideally I want to do this in a view and not use an SP. I can then use
this in my main view. If I have to resort to an SP, then so be it, but
I would appreciate any helpful comments (or even better, the answer)
Thanks

Ryan"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1101996460.738947.289740@.z14g2000cwz.googlegr oups.com...
> I have the following data (very simplified version)
> TransactionId Agent_Code
> ---- ----
> 191462 95328C
> 205427 000024C
> 205427 75547C
> Agent Code 75547C is a corporate agent. The others are not. I have a
> list of corporate codes so I can query against it, BUT what I want to
> do is...
> Return a unique TransactionId and max of the AgentCode, but if the
> Agent is a corporate agent, I need to return max of the corporate agent
> codes. We can have multiple agents against the transaction and
> sometimes have a mix of corporate and none corporate agents. What we
> need to do is see the corporate adviser if there is one. I only want 1
> record per TransactionId.

I would think something like (Obviously untested):

--No Corp Agent
Select TransactionID, Max(Agent_Code) from Transtable a where NOT
EXIST(select * from TransTable b inner join CorpAgents c on b.Agent_Code =
c.Agent_Code where b.TransactionID = a.TransactionID)

UNION ALL
--Corp Agent
Select TransactionID, Max(Agent_Code) from TransTable a inner join
CorpAgents c on a.Agent_Code = c.Agent_Code

Good Luck

Jim|||[posted and mailed, please reply in news]

Ryan (ryanofford@.hotmail.com) writes:
> Return a unique TransactionId and max of the AgentCode, but if the
> Agent is a corporate agent, I need to return max of the corporate agent
> codes. We can have multiple agents against the transaction and
> sometimes have a mix of corporate and none corporate agents. What we
> need to do is see the corporate adviser if there is one. I only want 1
> record per TransactionId.

SELECT TransactionID, coalesce(maxcorp, maxanyone)
FROM (SELECT t.TransactionID, maxanyone = MAX(t.AgentCode),
maxanyone = MAX(a.AgentCode)
FROM transactions t
LEFT JOIN agents a ON t.AgentCode = a.AgentCode
GROUP BY t.TransactionID)

And as you surely know, had you included CREATE TABLE, INSERT and expected
output, the solution would have been tested. Now it's not.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks ! Will give this a try.

Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns95B3F3141F310Yazorman@.127.0.0.1>...
> [posted and mailed, please reply in news]
> Ryan (ryanofford@.hotmail.com) writes:
> > Return a unique TransactionId and max of the AgentCode, but if the
> > Agent is a corporate agent, I need to return max of the corporate agent
> > codes. We can have multiple agents against the transaction and
> > sometimes have a mix of corporate and none corporate agents. What we
> > need to do is see the corporate adviser if there is one. I only want 1
> > record per TransactionId.
> SELECT TransactionID, coalesce(maxcorp, maxanyone)
> FROM (SELECT t.TransactionID, maxanyone = MAX(t.AgentCode),
> maxanyone = MAX(a.AgentCode)
> FROM transactions t
> LEFT JOIN agents a ON t.AgentCode = a.AgentCode
> GROUP BY t.TransactionID)
> And as you surely know, had you included CREATE TABLE, INSERT and expected
> output, the solution would have been tested. Now it's not.|||>> Return a unique transaction_id and max of the agent_code, but if
the agent is a corporate agent, I need to return max of the corporate
agent codes. <<

Just for fun, try this version:

CREATE VIEW CorpTrans (transaction_id, agent_code)
AS
SELECT transaction_id,
COALESCE(
MAX(CASE WHEN T1.agent_code IN (SELECT agent_code FROM
CorpAgents)
THEN T1.agent_code ELSE NULL END) -- corp_agent,
MAX(CASE WHEN T1.agent_code NOT IN (SELECT agent_code FROM
CorpAgents)
THEN T1.agent_code ELSE NULL END) -- non_corp_agent
) AS agent_code
FROM Transactions AS T1
GROUP BY Transaction_id;

You could also drop the COALESCE (), if it would be more useful to see
both kinds of agents. id the number of corporate agents is small enugh
to fit into main storage, this might actually be a good way to do it!sql