Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to group date quarterly wise?

I have documents which contain a date and I'm wondering how to group them according to quarterly basis?

My schema is:

var ekgsanswermodel = new mongoose.Schema({
    userId: {type: Schema.Types.ObjectId},
    topicId : {type: Schema.Types.ObjectId},
    ekgId : {type: Schema.Types.ObjectId},
    answerSubmitted :{type: Number},
    dateAttempted : { type: Date},
    title : {type: String},
    submissionSessionId : {type: String}  
});

1st quarter contains months 1, 2, 3. 2nd quarter contains months 4, 5, 6 and so on up-to 4th quarter.

My final result should be:

 "result" : [ 
   {
     _id: {
        quater:
     },
     _id: {
        quater:
     },
    _id: {
        quater:
     },
     _id: {
        quater:
     }
  }
like image 818
Rishabh Garg Avatar asked Feb 03 '15 04:02

Rishabh Garg


People also ask

How do you categorize dates?

Drag down the column to select the dates you want to sort. Click Home tab > arrow under Sort & Filter, and then click Sort Oldest to Newest, or Sort Newest to Oldest.

How do I sort dates into quarters in Excel?

(1) In the Column drop down list, please specify the date column that you will sort by quarter. (2) Click the Sort On drop down list, and select Quarter. (3) Specify the sort order in the Order drop down list.


2 Answers

You could make use of the $cond operator to check if:

  • The $month is <= 3, project a field named quarter with value as "one".
  • The $month is <= 6, project a field named quarter with value as "two".
  • The $month is <= 9, project a field named quarter with value as "three".
  • else the value of the field quarter would be "fourth".
  • Then $group by the quarter field.

Code:

db.collection.aggregate([
  {
    $project: {
      date: 1,
      quarter: {
        $cond: [
          { $lte: [{ $month: "$date" }, 3] },
          "first",
          {
            $cond: [
              { $lte: [{ $month: "$date" }, 6] },
              "second",
              {
                $cond: [{ $lte: [{ $month: "$date" }, 9] }, "third", "fourth"],
              },
            ],
          },
        ],
      },
    },
  },
  { $group: { _id: { quarter: "$quarter" }, results: { $push: "$date" } } },
]);

Specific to your schema:

db.collection.aggregate([
  {
    $project: {
      dateAttempted: 1,
      userId: 1,
      topicId: 1,
      ekgId: 1,
      title: 1,
      quarter: {
        $cond: [
          { $lte: [{ $month: "$dateAttempted" }, 3] },
          "first",
          {
            $cond: [
              { $lte: [{ $month: "$dateAttempted" }, 6] },
              "second",
              {
                $cond: [
                  { $lte: [{ $month: "$dateAttempted" }, 9] },
                  "third",
                  "fourth",
                ],
              },
            ],
          },
        ],
      },
    },
  },
  { $group: { _id: { quarter: "$quarter" }, results: { $push: "$$ROOT" } } },
]);
like image 113
BatScream Avatar answered Oct 13 '22 02:10

BatScream


You could use following to group documents quarterly.

{
    $project : {
        dateAttempted : 1,
        dateQuarter: {
            $trunc : {$add: [{$divide: [{$subtract: [{$month: 
            "$dateAttempted"}, 1]}, 3]}, 1]}
        }
    }
}
like image 32
Tofeeq Avatar answered Oct 13 '22 00:10

Tofeeq