comment calculer la somme dans mongodb


user944513

Salut, j'ai ce type de collection.

[{
    "_id" : ObjectId("5d7e56cc61f05d22ff7ee318"),
    "Emp No" : "0088P2C",
    "Emp Name" : "MANISHA JHA",
    "Card No" : "2076",
    "Department" : "THBS",
    "Att Date" : "03-Jun-2019",
    "In Time" : "03-Jun-2019 10:53",
    "Out Time" : "03-Jun-2019 18:13",
    "Status" : "P       ",
    "Late By " : "01:53",
    "Early By " : "00:00",
    "Total Hour" : "07:20",
    "OT Hour" : "00:00",
    "Location" : "Consolidated",
    "id" : "1c4bdbcd0f57c17a7160697266e9b371"
},
{
    "_id" : ObjectId("5d7e56cc61f05d22ff7ee319"),
    "Emp No" : "0088P2C",
    "Emp Name" : "MANISHA JHA",
    "Card No" : "2076",
    "Department" : "THBS",
    "Att Date" : "04-Jun-2019",
    "In Time" : "04-Jun-2019 09:50",
    "Out Time" : "04-Jun-2019 15:33",
    "Status" : "P       ",
    "Late By " : "00:50",
    "Early By " : "02:27",
    "Total Hour" : "05:43",
    "OT Hour" : "00:00",
    "Location" : "Consolidated",
    "id" : "a54e78844d0c27bd15607feb50aa272b"
}]

Je veux calculer la total hoursrequête .i comme cet agrégat employeeset obtenir son heure totale

await Attendance.aggregate(
            [
                {$match: {Department: "THBS"}},
                {
                    $group: {
                        _id: '$Emp No',
                        totalTime: {$sum: "$Total Hour"},
                        data: {$addToSet: {attendance: "$Att Date", Status: "$Status", id: "$id"}}
                    }
                },
                {$project: {'Emp No': '$_id', _id: 0, data: 1, totalTime: 1}}
            ])

total hourprovenant actuellement 0de cette requête.12.63

Mickl

Essayez de convertir Total Hourstocké sous forme de chaîne en minutes en utilisant $ toInt , $ substr et $ multiplier . Ensuite, vous pouvez additionner cette valeur et la reconvertir en nombre d'heures en utilisant $ divide :

db.collection.aggregate([
    {$match: {Department: "THBS"}},
    {
        $addFields: {
        TotalMinutes: {
            $add: [ 
                { $toInt: { $substr: [ "$Total Hour", 3, 2 ] } },
                { $multiply: [ { $toInt: { $substr: [ "$Total Hour", 0, 2 ] } }, 60 ] }
            ]
        }
        }
    },
    {
        $group: {
            _id: '$Emp No',
            totalTime: {$sum: "$TotalMinutes"},
            data: {$addToSet: {attendance: "$Att Date", Status: "$Status", id: "$id"}}
        }
    },
    {
        $project: {
            'Emp No': '$_id', 
            _id: 0, 
            data: 1, 
            totalTime: { $add: [ 
                { $toInt: { $divide: [ "$totalTime", 60 ] } },
                { $divide: [ { $mod: [ "$totalTime", 60 ] }, 100 ] }
                ]
            }
        }
    }
])

Aire de jeux Mongo

EDIT: 12,63 est égal à 13 heures et 3 minutes dans ce cas

Articles connexes