Grouping data by date ranges

I wonder how do I select a range of data depending on the date range?

I have these data in my payment table in format dd/mm/yyyy

 Id Date       Amount

 1   4/1/2011  300
 2  10/1/2011  200
 3  27/1/2011  100
 4   4/2/2011  300
 5  22/2/2011  400
 6   1/3/2011  500
 7   1/1/2012  600

The closing date is on the 27 of every month开发者_JAVA百科. so I would like to group all the data from 27 till 26 of next month into a group.

Meaning to say I would like the output as this.

 Group 1
 1      4/1/2011   300
 2     10/1/2011   200

 Group 2
 1     27/1/2011   100
 2      4/2/2011   300
 3     22/2/2011   400

 Group 3 
 1      1/3/2011   500

 Group 4 
 1      1/1/2012   600

It's not clear the context of your qestion. Are you querying a database?

If this is the case, you are asking about datetime but it seems you have a column in string format.

First of all, convert your data in datetime data type (or some equivalent, what db engine are you using?), and then use a grouping criteria like this:

GROUP BY datepart(month, dateadd(day, -26, [datefield])), DATEPART(year, dateadd(day, -26, [datefield]))

EDIT:

So, you are in Linq? Different language, same logic:

.GroupBy(x => DateTime
    .ParseExact(x.Date, "dd/mm/yyyy", CultureInfo.InvariantCulture) //Supposed your date field of string data type
    .AddDays(-26)
    .ToString("yyyyMM"));

If you are going to do this frequently, it would be worth investing in a table that assigns a unique identifier to each month and the start and end dates:

CREATE TABLE MonthEndings
(
    MonthID      INTEGER NOT NULL PRIMARY KEY,
    StartDate    DATE NOT NULL,
    EndDate      DATE NOT NULL
);
INSERT INTO MonthEndings VALUES(201101, '27/12/2010', '26/01/2011');
INSERT INTO MonthEndings VALUES(201102, '27/01/2011', '26/02/2011');
INSERT INTO MonthEndings VALUES(201103, '27/02/2011', '26/03/2011');
INSERT INTO MonthEndings VALUES(201112, '27/11/2011', '26/01/2012');

You can then group accurately using:

SELECT M.MonthID, P.Id, P.Date, P.Amount
  FROM Payments AS P
  JOIN MonthEndings AS M ON P.Date BETWEEN M.StartDate and M.EndDate
 ORDER BY M.MonthID, P.Date;

Any group headings etc are best handled out of the DBMS - the SQL gets you the data in the correct sequence, and the software retrieving the data presents it to the user.

If you can't translate SQL to LINQ, that makes two of us. Sorry, I have never used LINQ, so I've no idea what is involved.

SELECT *, CASE WHEN  datepart(day,date)<27 THEN datepart(month,date) 
ELSE datepart(month,date) % 12 + 1 END as group_name 
FROM payment