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)
 Find all transactions on same day

Author  Topic 

ArunPhilip
Starting Member

21 Posts

Posted - 2014-12-21 : 14:47:23
Hi,

I have a table that holds all the transactions information, the table looks like this,

Account SecureId Date1 Date2 Type Note Operator ModifiedBy Modifiedate

1234 00000 12Jun14 12Jun14 S NULL CONV NULL NULL
1234 00000 12Jun14 12Jun14 B CK CONV NULL NULL
2222 11111 15Mar14 15Mar14 S WK CONV Null NULL
2222 11111 15Mar14 15Mar14 B NULL CONV Null NULL
3333 44444 16May14 17May14 S NULL CONV NULL NULL

I would like to get an output that pairs the B and S for the same account, on same date and same Secureid.

This is what I have tried so far,
SELECT cp1.[Account]
,cp1.[SECID]
,cp1.[DDATE]
,cp1.[EFFEDATE]
,cp1.[TYPE]
,cp1.[NOTE]
,cp1.[OPER]
,cp1.[MODIBy]
,cp1.[MODIDATE]
,cp1.[TRANNUM]
,cp1.[PAR]
,cp1.[AMTU]
,cp1.[AMTC]
FROM CAPGAINS cp1
INNER JOIN CAPGAINS cp2
ON cp1.Account=cp2.Account
AND cp1.secid = cp2.secid
AND cp1.EFFEDATE=cp2.EFFEDATE
AND CP1.DDATE=cp2.DDATE
AND cp1.TYPE In ('B','S')
WHERE cp1.Account=cp2.Account
AND cp1.secid = cp2.secid
AND cp1.EFFEDATE=cp2.EFFEDATE
AND CP1.DDATE=cp2.DDATE
AND cp1.TYPE IN ('B','S')
GROUP BY cp1.Type,cp1.Account,cp1.secid,cp1.DDATE,cp1.[EFFEDATE],cp1.[NOTE]
,cp1.[OPER]
,cp1.[MODIBy]
,cp1.[MODIDATE]
,cp1.[TRANNUM]
,cp1.[PAR]
,cp1.[AMTU]
,cp1.[AMTC]

Any help with this is much apprciated.

Thanks in advance,
Philip

ArunPhilip
Starting Member

21 Posts

Posted - 2014-12-23 : 09:04:26
This is how I solved it.
SELECT cp1.[Account]
,cp1.[SECID]
,cp1.[DDATE]
,cp1.[EFFEDATE]
,cp1.[TYPE]
,cp1.[NOTE]
,cp1.[OPER]
,cp1.[MODIBy]
,cp1.[MODIDATE]
,cp1.[TRANNUM]
,cp1.[PAR]
,cp1.[AMTU]
,cp1.[AMTC]
FROM CAPGAINS cp1
INNER JOIN CAPGAINS cp2
ON cp1.EFFEDATE=cp2.EFFEDATE
AND CP1.ddate=cp2.ddate
AND cp1.account=cp2.account
AND Cp1.Secid=cp2.Secid
WHERE (cp1.Type='B' AND Cp2.Type='S') OR (cp1.Type='S' AND cp2.type='B')
ORDER BY PortCode
Go to Top of Page
   

- Advertisement -