Grouping data by date ranges

Question

I wonder how to choose a data range depending on a date range?

I have this data in my billing table in the 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 

Closing date is 27 of each month. so I would like to group all the data from 27 to 26 of the next month into a group.

I think that I would like the result to be like that.

  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 

Answer 1

It is not clear the context of your question. Are you requesting a database?

If so, you are asking about time, but it looks like you have a column in row format.

First of all, convert your data to a datetime data type (or some equivalent, what db engine are you using?), And then use the grouping criteria as follows:

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

EDIT:

So, are you in Linq? Another language, the 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")); 

Answer 2

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

Answer 3

If you intend to do this often, it would be worth investing in a table that assigns a unique identifier to each month and 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'); 

Then you can precisely group 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 headers, etc. It’s best to process it from a DBMS - SQL receives the data in the correct sequence, and the software that receives the data presents it to the user.

If you cannot translate SQL to LINQ, this will make us two. Sorry, I never used LINQ, so I have no idea what that means.