StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Personally, I'd start by setting up a secondary moves table that logs the total number of previous moves:</p> <pre><code>SELECT this.id_move, this.id_card, this.quantity, [sumPrevMoves] = sum(prev.quantity), [sumMovesNow] = this.quantity + isnull(sum(prev.quantity), 0) FROM @moves this LEFT JOIN @moves prev on this.id_card = prev.id_card and this.id_move > prev.id_move GROUP BY this.id_move, this.id_card, this.quantity </code></pre> <p>Then, with a simple join to the @cards table you can find all kinds of useful things:</p> <pre><code>SELECT m.id_move, m.id_card, [cardCycles] = c.cycle, [thisMove] = m.quantity, m.sumPrevMoves, m.sumMovesNow, [totalCycles] = m.sumMovesNow / cycle, [totalMoves] = m.sumMovesNow % cycle FROM @cards c JOIN (/*query from above*/) m on c.id_card = m.id_card </code></pre> <p>from here, to find the <code>last cycle, first move</code>, you need two pieces of info:</p> <ol> <li>What was the final cycle count</li> <li>What was the minimum move of that cycle</li> </ol> <p>If you put everything together with some CTEs, the following should give you what you want:</p> <pre><code>;WITH moves2 as ( SELECT this.id_move, this.id_card, this.quantity, [sumPrevMoves] = sum(prev.quantity), [sumMovesNow] = this.quantity + isnull(sum(prev.quantity), 0) FROM @moves this LEFT JOIN @moves prev on this.id_card = prev.id_card and this.id_move > prev.id_move GROUP BY this.id_move, this.id_card, this.quantity ), allMoves as ( SELECT m.id_move, m.id_card, [cardCycles] = c.cycle, [thisMove] = m.quantity, m.sumPrevMoves, m.sumMovesNow, [totalCycles] = m.sumMovesNow / cycle, [totalMoves] = m.sumMovesNow % cycle FROM @cards c JOIN moves2 m on c.id_card = m.id_card ) SELECT am1.id_card, [firstMoveLastCycle] = min(am1.id_move) FROM allMoves am1 join ( SELECT id_card, [lastCycle] = max(totalCycles) FROM allMoves GROUP BY id_card ) am2 on am1.id_card=am2.id_card and am1.totalCycles = am2.lastCycle GROUP BY am1.id_card </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload