Get records count between two dates I

Question

0.00/5 (No votes)

See more:

Hi Experts,

I have listofholidays table
With columns
RowId holidayname fromdate and todate

If an employee apply for a leave from date - to date.In this range if any holiday return that count based on conditions fromdate=to date and fromdate != to date

How can I achieve the result??

What I have tried:

I wrote two queries
Select count(*) as count1 from listof holidays where fromdate between @fromdate and @todate

Select count(*) as count2 from listofholidays where todate
Between @fromdate and @todate

I am summing these two counts but in table fromdate and todate equal
Its giving double count

Posted 30-May-17 3:41am

prasanna204

Updated 31-May-17 12:28pm

Add a Solution

Comments

OriginalGriff 30-May-17 9:49am

That doesn't make a lot of sense - perhaps if you add sample input and outputs so we can see what you get, and what you expect it might be clearer?

Use the "Improve question" widget to edit your question and provide better information.

prasanna204 30-May-17 9:56am

Sure
Table data:
1 test 30-05-2017 30-05-2017
2 test1 31-05-2017 31-05-2017

Employee applied leaves from 26-05-2017 to 31-05-2017
O/p
Result count 2 fromdate = to date
So if two dates or not equal how to apply conditions ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2017-05-30T05:21:00

0. Those dates seem to be of varchar type, if so, you should change them to date type.
1. Looking at your example, it seems that each holiday only lasts one day, if so, why need two dates - fromdate and todate? In such a case, you only need a date field say date_of_holiday, then get a count of records whose (date_of_holiday between fromdate and todate) and you are done.
2. If there are holidays that span more that one day, then consider these scenarios:
2.1 (holiday_from_date between leave_start_date and leave_end_date) AND (holiday_to_date > leave_end_date) OR
2.2 (holiday_to_date between leave_start_date and leave_end_date) AND (holiday_from_date < leave_start_date) OR
2.3 (holiday_from_date between leave_start_date and leave_end_date) AND (holiday_to_date between leave_start_date and leave_end_date)

Richard Deeming · Answer 2 · 2017-05-31T12:28:00

Solution 2

Are you looking for a count of holidays which overlap the proposed leave?

SQL

SELECT
    COUNT(*) As OverlappingCount
FROM
    ListOfHolidays
WHERE
    FromDate <= @ToDate
And
    ToDate >= @FromDate
;

Posted 31-May-17 12:28pm

Richard Deeming