a SQL aggregation query

Question

Hi there,

I have an table with 2 columns, a date column and an int column and I want to sum the int columns per month where a month start on the 15th

So the first result would be from today to the 15 of next month (Jan), the next row will be from the 16Jan to the 15Feb and so on until there are no more dates in the first column

Makes sense?

EDITED!!!

Sorry, i'll explain what I mean better. A month is defined from the 15th of one month to the 14th of the following month. The first month in the results will be from today to the 14th where the 14th could be a day away or 3 weeks away etc. The second month will be to the 14th of the next month. The last month in the result will be from the 15th to the end date on the table, so it's possible that the final month could be 2 days of data

Better?

I'll look at the answers now

Answer 1

So you want the month to be considered as the month that is one month later than fourteen days ago. So from today (Dec 17th), we go back 14 days to Dec 3rd, and then add a month to make it Jan 3rd. So today counts as January.

A common method for truncating the month is DATEADD(month, DATEDIFF(month, 0, someDate), 0). I'll use that, but first take fourteen days off, and I'll add one when I do the month add.

SELECT 
    DATEADD(month, 1+DATEDIFF(month, 0, DATEADD(day, -14, yourDate)),0) as theMonth, 
    SUM(yourInt) as Total
FROM yourTable
GROUP BY DATEADD(month, 1+DATEDIFF(month, 0, DATEADD(day, -14, yourDate)),0);

Good luck with this... you may prefer to start with something simple like:

SELECT 
    DATEADD(month, 1+DATEDIFF(month, 0, DATEADD(day, -14, yourDate)),0) as theMonth
    , *
FROM yourTable

...so that you can understand the logic around the month calculation.

Answer 2

As mentioned before, you have to group by an expression that creates buckets defined by the calendar Month

  Select dateadd(month, datediff(month, 0, datecol-14), 0 ) CalendarMonth, 
        sum(col) Total
  From Table
  Group By dateadd(month, datediff(month, 0, datecol-14), 0)

The expresion dateadd(month, datediff(month, 0, datecol-14), 0) will always generate a date of midnight, the first of the month which the date 14 days ago was in...

NOTE: The group by expresion or expressions must be identical to, and include all expressions in the select clause that are not aggregate functions

EDIT: This expression creates buckets by month, from the 15th through the 14th of the following month, but describes the buckets using the date of the first of the month the bucket starts in. i.e., It generates a date of the first of the month the start of the bucket is in... for example, any date from 15 Nov through 14 Dec will give you 1 Nov, any date from 15 Dec through 14 Jan will give you 1 Dec, etc.