select sum(blockno),sum(rownoinblock) (1 row(s) affected) Table 'TC76'. Scan count 10002, logical reads 21490, physical reads 2, read-ahead reads 41, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: C

v1

Redo. I'm now dealing with BlockNos totqty doesn't become 0 or grater before next account + instrument combination

First 'solution' was wrong (test data wouldn't show it though, it fails if more than 3 blocks exist for a single accountno, instrumentid. Fist query (cte) gets blockno, select then uses row_number to sequence within the block.

I had to resubmit this one because after I started to test with the test data I found a flaw. My original submission assumed that you could count on TotalQty staying in sync as the QTY changed with each transaction. The test data did not do this, but tth

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 poi

/* Author: Paul Rizza Date: 2/21/2012 Purpose: T-SQL Challenge number 76, group and number blocks of instr transactions by account Notes: 1. The first sequence starts with the first row of each account + instrument combination. 2. Subseque

SQL Server 2008 I create a virtual table with an EndDate column representing the first-coming row in the AccountNo/InstrumentId group where TotalQty = 0. If there is no such row it is set to the date of the last transaction in that AccountNo/InstrumentI

unfortunately living in 2005 kind of ugly; multiple CTE to arrive at transaction range for blocking and sequencing given previous CTE experience, feel that it may not scale well doesn't handle crossing zero, just specified criteria of hitting it

almost 1 pass.