SQL Server 2008 information per month divided onto days

Question

I have a table that has "periods" and looks like this (added some rows as example):

          | StartDate | EndDate  | Amount1 |   Amount2 | Type
==============================================================
1         |  20110101 | 20110131 |      89 |      2259 |    1
2         |  20110201 | 20110228 |     103 |     50202 |    1
3         |  20110301 | 20110331 |      90 |     98044 |    1
4         |  20110401 | 20110430 |      78 |    352392 |    1
==============================================================

As you can see each "period" is exactly one month. Each month is represented four times (there are 4 types) so this table has 48 rows.

I have a selection that gives me the number of days per period (simply, the day out of the EndDate, since its always the amount of days in that period) and Amount1PerDay and Amount2PerDay (the amount of that period divided by the number of days).

The first problem is:
I need a View on this table that shows me one row for each day in every period with the Amount1 and Amount2 columns that hold the value of that period divided by the number of days in that period.

The second problem is:
Most of these divisions do not give me a whole number. Decimals are not an option, so I need to divide the remaining days (after division) among the first days of that period. Take January as an example: Amount1 has 89. Divided over 31 days thats almost 2,9. So its 2 per day, and the first 27 days get 3 (,9 * 31 = 27). That way the result has only whole numbers in it.

score 1 · Answer 1 · answered Jul 08 '11 at 10:43

1

Q1

How to select a range of dates is explained in this article: http://social.msdn.microsoft.com/forums/en-US/sqlexpress/thread/916161f2-cf3c-4b6b-9015-9d13ed1af49e/

This should produce a column named THEDATE to be used for Q2

Q2

How to select the distributed amount goes like: SELECT FLOOR(AMOUNT / NUMDAYS) + IIF( DAY(THEDATE) <= (AMOUNT - NUMDAYS * FLOOR(AMOUNT / NUMDAYS) ), 1, 0)

NUMDAYS = DATEDIFF( d, STARTDATE, ENDDATE ) + 1

answered Jul 08 '11 at 10:43

Michiel

11
1

The logic is basically all right, but there's no `IIF` in SQL Server. – Andriy M Jul 08 '11 at 11:41
Thank you michiel. I did not bother to test this, because the instant-ready answer Andriy gave me worked perfectly. We did, however, read your link and it helped us a lot! – Jon Koeter Jul 11 '11 at 10:04

score 1 · Accepted Answer · edited May 23 '17 at 12:12

Assuming StartDate and EndDate are of type date or datetime, and Amount1 & Amount2 are integers:

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

SQL Server performs integer division when both operands are integers, so, for instance, the result of 5 / 2 would be 2, and not 2.5.

master..spt_values is a long-existing system table used for internal purposes. It contains a subset of rows that you can use as a number (tally) table, which is what it serves as in this query.

SQL Server 2008 information per month divided onto days

2 Answers2

Q1

Q2