Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 76 - Identify the sequence of accounting transactions

The challenge is to identify the sequence of accounting transactions. The table has data which is ordered. You need to follow the order of the data, and detect a certain condition which lasts for a sequence of data rows, you need to give these data points an ID which uniquely identifies the sequence. The originally idea of this challenge is borrowed from SQL Server MVP Erland Sommarskog, who mentioned a real-world problem during a conversation. The original problem has been significantly modified to fit it within the TSQL Challegnes framework.

Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

Sample Data

    
AccountNo InstrumentID TradeDatesOrder		   Qty	 TotalQty 
--------- ------------ ----------------------- ----- -------- 
1		  1			   2011-02-02 01:00:00.000 -50	 -50      
1		  1			   2011-02-02 01:00:01.000 -120	 -170     
1		  1			   2011-04-02 01:00:00.000	170	  0       
1		  1			   2011-06-12 01:00:00.000 -100	 -100     
1		  1			   2011-06-18 01:00:00.000	50	 -50      
1		  1			   2011-06-20 01:00:00.000 -500	 -550     
1		  1			   2011-07-01 01:00:00.000  1000  450     
2		  2			   2011-05-20 01:00:00.000 -450	  0       
2		  2			   2011-08-19 01:00:00.000 -320	 -320     
2		  2			   2011-08-30 01:00:00.000	320	  0       
2		  3			   2011-12-20 01:00:00.000 -400	 -400     

The table transactions has five columns. An account buys and sells financial instruments. We are interested in finding all such sequences from where the account opened a short position until it was closed.

TradeDatesOrder is when the account bought and sold the instrument. qty is the number of instruments bought or sold. totqty is the total qty remaining after the transaction.

The first sequence starts with the first row of each account + instrument combination. Subsequent sequences start with the first transaction after the totqrty has become 0. Each sequence ends when the totqty becomes 0 or when all transactions are over.

All transactions within the sequence should have a block number which is unique for the same accountno and instrumentid.

Expected Results

   
AccountNo InstrumentID TradeDatesOrder		   Qty	 TotalQty BlockNo RowNoInBlock
--------- ------------ ----------------------- ----- -------- ------- ------------
1		  1			   2011-02-02 01:00:00.000 -50	 -50      1       1
1		  1			   2011-02-02 01:00:01.000 -120	 -170     1       2      
1		  1			   2011-04-02 01:00:00.000	170	  0       1       3 
1		  1			   2011-06-12 01:00:00.000 -100	 -100     2       1
1		  1			   2011-06-18 01:00:00.000	50	 -50      2       2
1		  1			   2011-06-20 01:00:00.000 -500	 -550     2       3 
1		  1			   2011-07-01 01:00:00.000  1000  450     2       4
2		  2			   2011-05-20 01:00:00.000 -450	  0       1       1
2		  2			   2011-08-19 01:00:00.000 -320	 -320     2       1
2		  2			   2011-08-30 01:00:00.000	320	  0       2       2
2		  3			   2011-12-20 01:00:00.000 -400	 -400     1       1

Rules

  1. There is no limit to the length of a sequence you need to handle.
  2. Within an AccountNo/InstrumentID block all TradeDatesOrder values will be unique.
  3. The output should be ordered by AccountNo, InstrumentID, BlockNo, RowNoInBlock.

Sample Script

Use the TSQL Script given below to generate the source table and fill them with sample data.

 
IF OBJECT_ID('TC76','U') IS NOT NULL BEGIN   
	DROP TABLE TC76  
END  
GO   
CREATE TABLE TC76 (
	AccountNo       INT      NOT NULL,
    InstrumentID    INT      NOT NULL,
    TradeDatesOrder DATETIME NOT NULL,
    Qty             INT      NOT NULL,
    TotalQty        INT    NOT NULL,
    CONSTRAINT pk PRIMARY KEY (AccountNo, InstrumentID, TradeDatesOrder)
)  
GO    
INSERT INTO TC76(AccountNo,InstrumentID,TradeDatesOrder,Qty,TotalQty)  
SELECT 1, 1, '20110202 01:00:00',  -50,  -50 UNION ALL
SELECT 1, 1, '20110202 01:00:01', -120, -170 UNION ALL
SELECT 1, 1, '20110402 01:00:00',  170,    0 UNION ALL
SELECT 1, 1, '20110612 01:00:00', -100, -100 UNION ALL
SELECT 1, 1, '20110618 01:00:00',   50,  -50 UNION ALL
SELECT 1, 1, '20110620 01:00:00', -500, -550 UNION ALL
SELECT 1, 1, '20110701 01:00:00', 1000,  450 UNION ALL
SELECT 2, 2, '20110520 01:00:00', -450,    0 UNION ALL
SELECT 2, 2, '20110819 01:00:00', -320, -320 UNION ALL
SELECT 2, 2, '20110830 01:00:00',  320,    0 UNION ALL
SELECT 2, 3, '20111220 01:00:00', -400, -400

SELECT * FROM TC76
GO

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
  3. Your solution can use features from all versions up to and including SQL Server 2012.
Database Source Control in just 5 minutes
It takes just 5 minutes to connect your SQL databases to source control. Got 5 minutes to spare? Bring your database development process forward by 5 years. Get started now.

puzzles
13 · 13% · 4057
Submit a Solution
Previous Challenge
Next Challenge
4
Liked
29
Comments
40
Solutions



Submit

TSQL Challenge 76 - Identify the sequence of accounting transactions

Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.