How I can select / sort dates by period intervals ?

Question

For ex:

If we have in table records like:

25/06/2009
28/12/2009
19/02/2010
16/04/2011
20/05/2012

I want to split/select this dates according to 6 month intervals starting from current date. result should be like: 0-6 month from now: first record 7-12 month from now: second record ...

It will be much apreciated if you make this simple as I made it very stupid and complicated like:

declare variable like t1=curdate()+6 
t2=curdate()+12

...

then selected records to fit between curdate() and t1, then t1 and t2 etc.

Thanks,

r.

Answer 1

CORRECTION: Had it backwards, Need to use Modulus, not integer division - sorry...

If MonthCount is a calculated value which counts the number of months since a specific Dec 31, and mod is modulus division (output the remainder after dividing)

Select [Column list here] 
From Table
Group By Case When MonthCount Mod 12 < 6 
         Then 0 Else 1 End

In SQL Server, for example, you could use the DateDiff Function

Select [Column list here] 
From Table
Group By Case When DateDiff(month, myDateColumn, curdate) % 12 < 6 
         Then 0 Else 1 End

( in SQL Server the percent sign is the modulus operator )

This will group all the record into buckets which each contain six months of data

Answer 2

SELECT (DATEDIFF(MONTH, thedate, GETDATE()) / 6) AS semester,
       SUM(receipt)
FROM thetable
GROUP BY semester
ORDER BY semester

the key idea is grouping and ordering by the expression that gives you the "semester".

Answer 3

This question really baffled me, cos I couldn't actually come up with a simple solution for it. Damn.

Best I could manage was an absolute bastardization of the following where you create a Temp Table, insert the "Periods" into it, join back to your original table, and group off that.

Assume your content table has the following

ID int
Date DateTime
Counter int

And you're trying to sum all the counter's in six month periods

DECLARE @min_date datetime
select @min_date = min(date) from test

DECLARE @max_date datetime
select @max_date = max(date) from test


DECLARE @today_a datetime
DECLARE @today_b datetime
set @today_a = getdate()
set @today_b = getdate()

CREATE TABLE #temp (startdate DateTime, enddate DateTime)

WHILE @today_a > @min_date
BEGIN
    INSERT INTO #temp (startDate, endDate) VALUES (dateadd(month, -6, @today_a), @today_a)
    SET @today_a = dateadd(month, -6, @today_a)
END
WHILE @today_b < @max_date
BEGIN
    INSERT INTO #temp (startDate, endDate) VALUES (@today_b, dateadd(month, 6, @today_b))
    SET @today_b = dateadd(month, 6, @today_b)
END

SELECT * FROM #temp

SELECT 
    sum(counter), 
    'Between ' + Convert(nvarchar(10), startdate, 121) + ' => ' +  Convert(nvarchar(10), enddate, 121) as Period
FROM test t
    JOIN #Temp ht
     ON t.Date between ht.startDate AND ht.EndDate
GROUP BY 
    'Between ' + Convert(nvarchar(10), startdate, 121) + ' => ' +  Convert(nvarchar(10), enddate, 121)

DROP TABLE #temp

I really hope someone can come up with a better solution my brain has obviously melted.

Answer 4

Not quite what you're attempting to accomplish, but you could use the DATEDIFF function to distinguish the ranging of each record:

SELECT t.MonthGroup, SUM(t.Counter) AS TotalCount
FROM (    
      SELECT Counter, (DATEDIFF(m, GETDATE(), Date) / 6) AS MonthGroup
      FROM Table
     ) t
GROUP BY t.MonthGroup

This would create a sub query with an expression that expresses the date ranging group you want. It would then group the sub-query by this date ranging group and you can then do whatever you want with the results.

Edit: I modified the example based on your example.

Answer 5

If you're using SQL Server:

SELECT *,
    (
        FLOOR
        (
            (
                DATEDIFF(month, GETDATE(), date_column)
                - CASE WHEN DAY(GETDATE()) > DAY(date_column) THEN 1 ELSE 0 END
            ) / 6.0
        ) * 6
    ) AS SixMonthlyInterval
FROM your_table

If you're using MySQL:

SELECT *,
    (
        FLOOR
        (
            (
                ((YEAR(date_column) - YEAR(CURDATE())) * 12)
                + MONTH(date_column) - MONTH(CURDATE())
                - CASE WHEN DAY(CURDATE()) > DAY(date_column) THEN 1 ELSE 0 END
            ) / 6.0
        ) * 6
    ) AS SixMonthlyInterval
FROM your_table