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 2012 Forums
 Transact-SQL (2012)
 SQL Query Help please

Author  Topic 

Spunny
Starting Member

2 Posts

Posted - 2015-01-15 : 00:00:09
Hi All,

We are using SQL Server 2012. We have Table1 like below.

Table1:

SecurityKey Description PID

110 qqq 1

111 ddd 1

112 fff 1

113 vvvv 1

114 bbb 1

115 ggg 1

116 hhhh 1

117 iiii 1


One of the third party system adds records into the following table like this.

Table2

ID MasterID Type Amount SecurityKey Date TransNum

1 110 Buy 600 0 01/13/2014 2178

2 111 Buy 100 110 01/13/2014 2179

3 112 Buy 200 110 01/13/2014 2180

4 113 Buy 300 110 01/13/2014 2181

5 114 Sell 100 110 01/14/2014

6 115 Sell 200 110 01/14/2014

7 116 Sell 300 110 01/14/2014

8 117 Buy 200 110 01/14/2014 2183


In above table, primary key is combination of ID, MasterID and Type. All ‘Buy’ type transactions are entered into Table2 by Vendor system by running scheduled job. SecurityKey 110 has child records. So, in Table2, 111, 112, 113 belong to 110. Next day, another system creates exact matching ‘Sell’ transactions to all buy transactions except for parent (here record with MasterID 110). When ‘Sell’ transactions are created, TransNum is not populated.

I need to write a select query in such a way that I need to get all ‘Buy’ Transactions for a particular date and get the 'Sell' Transactions for same date, but with TransNum of it's 'buy' record. Suppose, I am querying for 01/14/2014, then I need to get all 'Buy' transactions for 14th and 'Sell' Transactions for 14th and get it's transNum from 'Buy' records which happend on 13th.

The result set should look like this for passed in date i.e. 01/14/2014.

8 117 Buy 200 110 01/14/2014 2183

5 114 Sell 100 110 01/14/2014 2179

6 115 Sell 200 110 01/14/2014 2180

7 116 Sell 300 110 01/14/2014 2181



How can I write select query to get result set above by applying ‘Buy’ transaction TransNum to corresponding ‘Sell’Transaction which happened on different dates. I see only amount and SecurityKey as joining columns.

Thanks,

Spunny

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-21 : 02:37:48
If the tran number is empty then find the tran number Using subquery (Consider the previous transaction with the matching Master Id and Type)

Regards
Viggneshwar A
Go to Top of Page
   

- Advertisement -