Little puzzle on data selection

[Ryan]

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

[James Goodwin]

Re: Little puzzle on data selection


"Ryan" <ryanofford@hot mail.com> wrote in message
news:1101996460 .738947.289740@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> 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.[/color]

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

[Erland Sommarskog]

Re: Little puzzle on data selection

[posted and mailed, please reply in news]

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> 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.[/color]

SELECT TransactionID, coalesce(maxcor p, 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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ryan]

Re: Little puzzle on data selection

Thanks ! Will give this a try.

Erland Sommarskog <esquel@sommars kog.se> wrote in message news:<Xns95B3F3 141F310Yazorman @127.0.0.1>...[color=blue]
> [posted and mailed, please reply in news]
>
> Ryan (ryanofford@hot mail.com) writes:[color=green]
> > 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.[/color]
>
> SELECT TransactionID, coalesce(maxcor p, 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.[/color]

[--CELKO--]

Re: Little puzzle on data selection

>> 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!