1

How to calculate Month-To-Date(MTD) and Year-To-Date(YTD) in mongodb in a single query? sample data below, in this data requestedOn is a date field, I want to calculate MTD & YTD, on the assumption of financial year on "1st Jan of the year"(For example financial year for year 2016 is "01-Jan-2016" :

     {
  "_id": {
    "$oid": "5808578b33fa6f161c9747f8"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2000-03-01",
  "bookName": "Test6",
  "revenue": 10.0,
  "unitsSold": 1,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    },
    {
      "categoryCode": "Cooking/Beverages/Bartending"
    },
    {
      "categoryCode": "Food Receipe/Taste"
    }
  ]
}{
  "_id": {
    "$oid": "5808578b33fa6f161c9747f9"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2000-03-01",
  "bookName": "Test1",
  "revenue": 11.0,
  "unitsSold": 2,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    },
    {
      "categoryCode": "Cooking/Beverages/Bartending"
    },
    {
      "categoryCode": "Food Receipe/Taste"
    }
  ]
}{
  "_id": {
    "$oid": "5808578b33fa6f161c9747fa"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2000-06-01",
  "bookName": "Test2",
  "revenue": 12.0,
  "unitsSold": 3,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    },
    {
      "categoryCode": "Cooking/Beverages/Bartending"
    },
    {
      "categoryCode": "Food Receipe/Taste"
    }
  ]
}{
  "_id": {
    "$oid": "5808578b33fa6f161c9747fb"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2000-07-01",
  "bookName": "Test3",
  "revenue": 13.0,
  "unitsSold": 4,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    },
    {
      "categoryCode": "Cooking/Beverages/Bartending"
    },
    {
      "categoryCode": "Food Receipe/Taste"
    }
  ]
}{
  "_id": {
    "$oid": "5808578b33fa6f161c9747fc"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2009-09-01",
  "bookName": "Test4",
  "revenue": 14.0,
  "unitsSold": 5,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    },
    {
      "categoryCode": "Cooking/Beverages/Bartending"
    },
    {
      "categoryCode": "Food Receipe/Taste"
    }
  ]
}{
  "_id": {
    "$oid": "5808578b33fa6f161c9747fd"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2009-06-01",
  "bookName": "Test5",
  "revenue": 15.0,
  "unitsSold": 6,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    },
    {
      "categoryCode": "Cooking/Beverages/Bartending"
    },
    {
      "categoryCode": "Food Receipe/Taste"
    }
  ]
}{
  "_id": {
    "$oid": "5808578b33fa6f161c9747fe"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2004-06-01",
  "bookName": "Test10",
  "revenue": 16.0,
  "unitsSold": 7,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    },
    {
      "categoryCode": "Cooking/Beverages/Bartending"
    },
    {
      "categoryCode": "Food Receipe/Taste"
    }
  ]
}{
  "_id": {
    "$oid": "5808578b33fa6f161c9747ff"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2000-01-01",
  "bookName": "Test11",
  "revenue": 100.0,
  "unitsSold": 100,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    },
    {
      "categoryCode": "Cooking/Beverages/Bartending"
    },
    {
      "categoryCode": "Food Receipe/Taste"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e462"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2000-02-01",
  "bookName": "Test1",
  "revenue": 20.0,
  "unitsSold": 10,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e463"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2001-02-01",
  "bookName": "Test2",
  "revenue": 19.0,
  "unitsSold": 9,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e464"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2001-02-01",
  "bookName": "Test3",
  "revenue": 18.0,
  "unitsSold": 8,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e465"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2007-06-01",
  "bookName": "Test4",
  "revenue": 17.0,
  "unitsSold": 7,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e466"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2005-06-01",
  "bookName": "Test5",
  "revenue": 16.0,
  "unitsSold": 6,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e467"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2004-06-01",
  "bookName": "Test1",
  "revenue": 15.0,
  "unitsSold": 5,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e468"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2002-06-01",
  "bookName": "Test2",
  "revenue": 14.0,
  "unitsSold": 4,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e469"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2001-06-01",
  "bookName": "Test3",
  "revenue": 13.0,
  "unitsSold": 3,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e46a"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2000-06-01",
  "bookName": "Test4",
  "revenue": 12.0,
  "unitsSold": 2,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}{
  "_id": {
    "$oid": "580857b833fa6f0c3499e46b"
  },
  "_class": "exceltest.TestBean",
  "requestedOn": "2008-06-01",
  "bookName": "Test5",
  "revenue": 11.0,
  "unitsSold": 1,
  "bookCategory": [
    {
      "categoryCode": "Cooking/"
    },
    {
      "categoryCode": "Cooking/Beverages"
    },
    {
      "categoryCode": "Food Receipe/"
    },
    {
      "categoryCode": "Food Receipe/Bartending"
    }
  ]
}

Regards

Kris

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
chiku
  • 485
  • 2
  • 8
  • 23

1 Answers1

1

It is a good practice to keep dates in MongoDB in its native dateformat ISODate().

You can use date formats like $year,$month,$day,$hour etc.

These can be used for grouping , in your case:

    db.collectionName.aggregate([
          {$group:{_id:{'Date':{$year:'$requestedOn'}},total:{$sum:'$FieldName'}}}
     ])

to convert string to ISODate , answers can be found at

   - [http://stackoverflow.com/questions/15473772/how-to-convert-from-string-to-date-data-type?noredirect=1&lq=1][2]
   - [http://stackoverflow.com/questions/15473772/how-to-convert-from-string-to-date-data-type?noredirect=1&lq=1][2]
cartman619
  • 552
  • 4
  • 17