SQL Server query GROUP BY with Where

Question

I have table tblEvents with columns dEvent_Date, dEvent_Time, tEvent_Emp_Code_num

I must group all dates and get all minimum time from each of them.

For example I wrote below query, but its nothing printed.

SELECT dEvent_Date, MIN(dEvent_Time) as minTime 
FROM tblEvents 
WHERE
     tEvent_Emp_Code_num = 26 AND dEvent_Date = 11/18/2010 
GROUP BY dEvent_Date

After that I tested below query, it works, but it prints all days, I need only specified date's data.

SELECT dEvent_Date, MIN(dEvent_Time) as minTime 
FROM tblEvents 
WHERE tEvent_Emp_Code_num = 26 GROUP BY dEvent_Date

Can you help me?

Does query SELECT * FROM tblEvents WHERE tEvent_Emp_Code_num = 26 AND dEvent_Date = 11/18/2010 returns anything? — sll, Jul 22 '11 at 08:05
possible duplicate of [SQL: Find the max record per group](http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group), also [Select max in Group](http://stackoverflow.com/questions/95866/select-max-in-group) and many, many others on this site alone. — Tomalak, Jul 22 '11 at 08:07

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

This will work for this case:

SELECT dEvent_Date, MIN(dEvent_Time) as minTime 
FROM   tblEvents 
WHERE  tEvent_Emp_Code_num = 26 
GROUP BY dEvent_Date
HAVING  CAST('2015/01/01 00:00:01' AS DATETIME) < dEvent_Date 
        AND dEvent_Date >  CAST('2015/05/05 00:00:01' AS DATETIME)

(Refrences) how to use the keyword having:

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

https://msdn.microsoft.com/en-us/library/ms180199.aspx

score 0 · Answer 2 · answered Jul 22 '11 at 08:03

0

How is your Date stored in table? The default Date format of MySQL is: yyyy-mm-dd. Also you must enclose date in single quotes.

And you should search like:

where dEvent_Date = '2010/11/18'

if you are not specifying Date Format.

answered Jul 22 '11 at 08:03

RKh

13,818
46
152
265

score 0 · Answer 3 · answered Jul 22 '11 at 08:19

0

if db is dql this the Formate to check the value In DB,,,,,

or else u,,,, you have to convert the date value into '2010-11-18' like

$date   =   date('Y-m-d',strtotime(11/18/2010)); 

   dEvent_Date = $date

answered Jul 22 '11 at 08:19

K6t

1,821
1
13
21

score 0 · Accepted Answer · answered Jul 22 '11 at 08:25

i had same problem and i have used cast function in mysql where my query look like

CREATE VIEW v_a AS SELECT
  tblEvents .*,
  EXTRACT(YEAR FROM dEvent_Date) AS 'year',
  EXTRACT(MONTH FROM dEvent_Date) AS 'month',
  DAYOFMONTH(dEvent_Date) AS 'day'
FROM tblEvents WHERE tEvent_Emp_Code_num = 26;
SELECT
  v.year,
  v.month,
  v.day,
  count(v.id)
FROM v_a AS v
GROUP BY v.year,v.month,v.day

for SQL SErver you may use CONVERT Function

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]

or cast function

SELECT CAST(YEAR(GETDATE()) AS VARCHAR(4))

perhaps following post is more useful

Get Date Only Function

SQL Server query GROUP BY with Where

4 Answers4