StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can do this in a relatively straightforward way by creating a period table, which you can join with the accounts table to create one row per account per period.</p> <p>Here's an example. Let's set up some temporary tables:</p> <pre><code>create table #balance ( id int identity, balance float, date datetime, aid int ) create table #period ( id int identity, startdt datetime, enddt datetime ) </code></pre> <p>Enter some test data:</p> <pre><code>insert into #yourtable (balance, date, aid) values (4,'2009-01-01',1) insert into #yourtable (balance, date, aid) values (5,'2009-01-10',1) insert into #yourtable (balance, date, aid) values (6,'2009-01-10',1) insert into #yourtable (balance, date, aid) values (7,'2009-01-16',1) insert into #yourtable (balance, date, aid) values (2,'2009-01-01',2) insert into #yourtable (balance, date, aid) values (3,'2009-01-10',2) insert into #yourtable (balance, date, aid) values (4,'2009-01-10',2) insert into #yourtable (balance, date, aid) values (5,'2009-01-16',2) insert into #period (startdt, enddt) values ('2009-01-01','2009-01-06') insert into #period (startdt, enddt) values ('2009-01-06','2009-01-11') insert into #period (startdt, enddt) values ('2009-01-11','2009-01-16') insert into #period (startdt, enddt) values ('2009-01-16','2009-01-21') </code></pre> <p>Now let's query all periods:</p> <pre><code>from #period p </code></pre> <p>Add one row for each balance before the end of the period:</p> <pre><code>left join #balance b1 on b1.date <= p.enddt </code></pre> <p>Search for balances in between the balance from the first join, and the end of the period:</p> <pre><code>left join #balance b2 on b2.aid = b1.aid and b1.id < b2.id and b2.date <= p.enddt </code></pre> <p>Then filter out the rows that are not the last balance for their period.</p> <pre><code>where b2.aid is null </code></pre> <p>The b2 join basically looks for the "in-between" value, and by saying it's id is null, you say no in-between row exists. The final query looks like this:</p> <pre><code>select b1.aid , p.startdt , b1.balance from #period p left join #balance b1 on b1.date <= p.enddt left join #balance b2 on b2.aid = b1.aid and b1.id < b2.id and b2.date <= p.enddt where b2.aid is null order by b1.aid, p.startdt </code></pre> <p>Note: the queries assume a balance with a later date always has a larger id. If you never have to balances with exactly the same end date, you can replace "b1.id < b2.id" with "b1.date < b2.date".</p>

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