Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a temp table like this

TblTransactions

I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.

So, I only want to see

CCC 333333333 Ambition Trust Purchase,

CCC 333333333 Ambition Trust Sell

This is the SQL giving me the same Portcode, Cusip combo from a table.

SELECT PortfolioName, CUSIP, COUNT(*) AS CNT
INTO #tmpCusip
FROM #tmpTrades 
GROUP BY PortfolioName, MCUSIP
HAVING COUNT(*) > 1

Does anyone have any ideas?

share|improve this question
    
Is there a timestamp or other column in the table that gives order to each subgroup? –  Jon Seigel Aug 29 '13 at 16:22
    
...is the order in each subgroup important (i.e., Sell then Purchase vs. Purchase then Sell)? Does it start from "zero"? –  Jon Seigel Aug 29 '13 at 16:41

1 Answer 1

up vote 0 down vote accepted

I have been playing with it but I don't know how to get same PortCode,Cusip combo to give me only offsetting transactions (Buy,Sells) not (Purchase,Purchase) combos.

There is no Buy in your table. You only have Sell and Purchase.

Below is a good starting point for you to get (Purchase,Sell) combo :

;with temptrades 
as
(select *, row_number() over (partition  by PortfolioName, [transaction]
order by PortfolioName, [transaction]) as rowNumber
    from tmpTrades
)
select  portcode,cusip, PortfolioName,  [transaction]   
 from temptrades    
where  rowNumber = 1
order by portcode

enter image description here

SQLFiddle here.

share|improve this answer
    
The result set you screenshotted nicely highlights my comment on the question -- the DDD subgroup came out in the opposite order of the OP's screenshot. Is the original data (Sell, Sell, Purchase) or (Sell, Purchase, Sell), etc.? I suppose we don't even know if that's important or not. –  Jon Seigel Aug 29 '13 at 16:39
    
I don't think it is relevant to my question. The partition by should work for what I am trying to accomplish. –  AKudla Aug 29 '13 at 17:07
    
@user27642 the comment or the answer ? –  Kin Aug 29 '13 at 17:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.