I have a leave transaction table like below

{
    "leaveDuration": 1,
    "leaveDurationDetail": 1,
    "fromDate": {
        "$date": "2021-11-25T00:00:00.000Z"
    },
    "toDate": {
        "$date": "2021-11-27T00:00:00.000Z"
    },
    "leaveStatus": 1,
    "availableBalance": 9,
    "reason": "World Tour",
    "userId": {
        "$oid": "618cc2e592dffdd7e1257cb4"
    },
    "days": -1,
    "leaveType": {
        "$oid": "618b67968569132637760cb7"
    },
    "createdAt": {
        "$date": "2021-11-12T09:13:07.239Z"
    },
    "updatedAt": {
        "$date": "2021-11-12T09:13:07.239Z"
    },
    "__v": 0
}

You can see the above leave entry with 25th(fromDate) to 27th(toDate).

The query below is to check if the entered dates are already applied as leave or not.

[
    {
        '$match': {
            '$or': [
                {
                    '$and': [
                        {
                            'fromDate': {
                                '$lte': new Date('Tue, 25 Nov 2021 00:00:00 GMT')
                            }
                        }, {
                            'toDate': {
                                '$gte': new Date('Tue, 25 Nov 2021 00:00:00 GMT')
                            }
                        }
                    ]
                }, {
                    '$and': [
                        {
                            'fromDate': {
                                '$lte': new Date('Sun, 28 Nov 2021 00:00:00 GMT')
                            }
                        }, {
                            'toDate': {
                                '$gte': new Date('Sun, 28 Nov 2021 00:00:00 GMT')
                            }
                        }
                    ]
                }
            ]
        }
    }
]

You see the above query will return the data means the leave is already applied.

But this query is not working for 24 to 28.

So I understand the problem here that 24 is smaller than 25 and 28 is larger than 27 so it won't return any data.

But what query can we make to get the dates in between ?

If leave is applied from 25 to 27. How can we create a query in which if 24 to 28 is entered that it will return that your leave of 25 to 27 is already applied so 24 to 28 is not allowed as 25 to 27 comes in between 24 to 28.

CodePudding user response：

I see your query wont work if both Entered dates are same. So you might need 2 queries on based on condition

if(enteredFromDate == enteredToDate){

  fromDate: { $lte: yourEnteredfromDate },
  toDate: { $gte: yourEnteredfromDate }  

} else {
        $or: [{
            $and: [{
                fromDate: {
                    $gte: yourEnteredfromDate
                }
            }, {
                toDate: {
                    $gte: yourEnteredfromDate
                }
            }, ]
        }, 
        {
            $and: [{
                fromDate: {
                    $lte: yourEnteredtoDate
                }
            }, {
                toDate: {
                    $lte: yourEnteredtoDate
                }
            }, ]
        }]
    }
} 

This will full-fill all your dates. And provide Overlapping data.

CodePudding user response：

So all you have to do is change your logic, let's word it out first.

We want the "start" date to be greater than input x and we want the "end" date to be less than input y.

Now the query is quite simple:

[
    {
        '$match': {
            fromDate: {$gte: new Date('Tue, 24 Nov 2021 00:00:00 GMT')},
            toDate: {$lte: new Date('Sun, 28 Nov 2021 00:00:00 GMT')},
        }
    }
]

This will match any "leave" that starts after the 24, and ends prior to the 28th.

---- EDIT ----

For matching any dates you can use this:

[
    {
        '$match': {
            $or: [
                {
                    fromDate: {
                        $gte: new Date('Tue, 24 Nov 2021 00:00:00 GMT'),
                        $lte: new Date('Sun, 28 Nov 2021 00:00:00 GMT')
                    },
                },
                {
                    toDate: {
                        $gte: new Date('Tue, 24 Nov 2021 00:00:00 GMT'),
                        $lte: new Date('Sun, 28 Nov 2021 00:00:00 GMT')
                    },
                },
                {
                    $and: [
                        {
                            fromDate: {$lte: new Date('Tue, 24 Nov 2021 00:00:00 GMT')},
                        },
                        {
                            toDate: {$gte: new Date('Tue, 24 Nov 2021 00:00:00 GMT')},
                        }
                    ]
                },
                {
                    $and: [
                        {
                            fromDate: {$lte: new Date('Sun, 28 Nov 2021 00:00:00 GMT')},
                        },
                        {
                            toDate: {$gte: new Date('Sun, 28 Nov 2021 00:00:00 GMT')},
                        }
                    ]
                }
            ]
        }
    }
]