Need Advice on this - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Need Advice on this

Author

Topic

MangoSkin
Starting Member

22 Posts

Posted - 2010-06-14 : 18:52:36

I have three tables

Table Item
ItemID,AgentID,Amount
Table Process
ItemID,AgentID,ProcessDate
Table Agent
AgentID,AgentName,AmountLimit

Items need to be processed by their respective Agents. The Items have an Amount and also the Agents have AmountLimits.So if the amount on the item is more than the amountlimit of the agent then the agents supervisor needs to process it.So the processing goes from one agent to his supervisor depending on the amounts.

I need a query to give results like this.
ItemID,OriginalItemAgent,LastProcessingAgent,NeedMoreProcessing

The query that I have is this
Select i.itemID,
oA.AgentName as OriginalItemAgent,
lA.AgentName as LastProcessingAgent,
(case when i.Amount > lA.AmountLimit then '1' else '0' end ) as NeedMoreProcessing
from Item i left outer join Agent oA on oA.AgentID=i.AgentID
left outer join Process p on p.ItemID=i.ItemID left outer Agent lA on lA.AgentID=
(select AgentID from (
select row_number () over (partition by itemid order by ProcessDate desc) as seq, * from Process
) p
where p.seq = 1 and p.itemID = i.itemID)

Can someone give me a better way to do this.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources