SQL COUNT between dates in two different column

Question

Let's say, we have this table:

STUDENT |  START  | END
1       |1998-1-1 |2001-1-1
2       |1999-1-1 |2001-1-1
3       |2000-1-1 |2004-1-1
4       |2000-1-1 | NULL

I'm trying to do is:

Count number of students between start and end dates!

Solution 1

Looks like you need to use a basic COUNT aggregate:

SELECT COUNT(Student)
FROM YourTable
WHERE Start >= @Start 
    AND End <= @End

I've used >= and <= respectively around the start and end date fields. Feel free to change to > or < as needed. It was unclear from your question whether you wanted between a specific field or if you were checking for a range between those two fields.

Solution 2

Use the between Operator and COUNT aggregate function

SELECT COUNT(student) column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

Between can be used with text so insert the dates where the values are,

Read more here if you still don't understand

EDIT : That should work, sorry about the error

http://www.w3schools.com/sql/sql_between.asp

Comments

[Bohemian]

What do you mean "between" dates? Do you mean a given date is between start and end, or a given date range "overlaps" the start and end? Or something else?

[jdidi]

I wanted sql that count student who started between dates and count student who ended study between dates.

[Yaroslav]

What the OP wants is to Count number of students between start and end dates. Your answer just return a set of rows.