StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Assuming <code>StartDate</code> and <code>EndDate</code> are of type <code>date</code> or <code>datetime</code>, and <code>Amount1</code> & <code>Amount2</code> are integers:</p> <pre><code>SELECT Date, Amount1 = Amount1Whole + CASE WHEN DayNum < Amount1Rem THEN 1 ELSE 0 END, Amount2 = Amount2Whole + CASE WHEN DayNum < Amount2Rem THEN 1 ELSE 0 END, Type FROM ( SELECT Date = DATEADD(day, v.number, t.StartDate), DayNum = v.number, Amount1Whole = Amount1 / DAY(t.EndDate), Amount2Whole = Amount2 / DAY(t.EndDate), Amount1Rem = Amount1 % DAY(t.EndDate), Amount2Rem = Amount2 % DAY(t.EndDate), t.Type FROM atable t INNER JOIN master..spt_values v ON v.type = 'P' AND v.number BETWEEN 0 AND DATEDIFF(day, t.StartDate, t.EndDate) ) s </code></pre> <p>SQL Server performs integer division when both operands are integers, so, for instance, the result of <code>5 / 2</code> would be <code>2</code>, and not <code>2.5</code>.</p> <p><a href="https://stackoverflow.com/questions/4273723/what-is-the-purpose-of-system-table-table-master-spt-values-and-what-are-the-mea"><code>master..spt_values</code></a> is a long-existing system table used for internal purposes. It contains a subset of rows that you can use as a <a href="http://www.sqlservercentral.com/articles/T-SQL/62867/" rel="nofollow noreferrer" title="The "Numbers" or "Tally" Table: What it is and how it replaces a loop."><em>number (tally) table</em></a>, which is what it serves as in this query.</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