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.
| 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.Table2ID 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/20146 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)RegardsViggneshwar A |
 |
|
|
|
|
|
|
|
SQL Query Help please