Can we use the by group and where is the condition with the same field name

Question

I have a requirement how to pull out all the records in the date range selected by the user, selecting all the employees who started from January 15, 2011 to August 20, 2011 and by date.

How do I write an SQL query for this:

SELECT * FROM employees WHERE startdate >= '15-jan-2011' AND startdate <= '20-aug-2011' GROUP BY startdate 

Answer 1

That's right. This will filter the records in your date range and then group them every day when there is data.

It should be noted that you can only select the start date, and then any aggregates that you calculate. Otherwise, it should work fine.

For example, this query will give you the number of employees for each start date:

 SELECT startdate, count(*) FROM employees WHERE startdate >= '15-jan-2011' AND startdate <= '20-aug-2011' GROUP BY startdate 

Answer 2

You can, but the GROUP BY clause is used to group sets of rows, so this doesn't make sense for your question (or something related to "SELECT *").

To answer your question:

 SELECT DATEADD(dd, 0, DATEDIFF(dd,0,StartDate)) AS 'StartDate', <other fields> FROM Employees WHERE StartDate BETWEEN '15-Jan-2011' AND '20-Jan-2011' ORDER BY StartDate 

Note: the removal of time from the date came from here