Find all transactions on same day

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources