CODEWITHSUNDEEP
linq
VeecoTech
VeecoTech

Reputation: 2143

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. 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

Upvotes: 4

Views: 2660

Answers (3)

BertuPG
BertuPG

Reputation: 653

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"));

Upvotes: 3

Jonathan Leffler
Jonathan Leffler

Reputation: 753525

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.

Upvotes: 1

Ovidiu Pacurar
Ovidiu Pacurar

Reputation: 8209

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

Upvotes: 0

Related Questions