0

I have a table like this:

EmployeeId       Checktime
1              2015-10-14 07:51:25.000
1              2015-10-14 18:56:47.000
1              2015-10-15 00:04:04.000

I want to get the minimum datetime and maximum datetime from this table. I want to give parameter for the program like this '2015-10-01 07:00:00' and '2015-10-31 05:00:00' and the minimum datetime and maximum datetime will show the result like this for all the month of that employee.

Heading    Minimum                 Maximum
-------------------------------------------------------------
16         2015-10-14 09:07:32.000 2015-10-15 18:02:20.000 
17         2015-10-14 08:55:56.000 2015-10-15 18:02:07.000 
18         2015-10-14 09:01:17.000 2015-10-15 11:37:10.000 
73         2015-10-14 07:51:25.000 2015-10-15 00:04:04.000 

And my parameter was '2015-10-14 07:00:00' and '2015-10-15 05:00:00' but I want to pass parameter of whole month '2015-10-01 07:00:00' and '2015-10-31 05:00:00' and get this way answer for whole month.

0
1

If I correct understand Your question this should work. I have provided a link to SQL FIDDLE:

declare @max_date datetime
declare @min_date datetime

select @min_date = '2015-10-01 07:00:00'
select @max_date = '2015-10-31 05:00:00'

SELECT EmployeeId
    , (SELECT TOP 1 CAST(CAST(CONVERT(DATE, CheckTime) AS VARCHAR(10)) + ' ' + CAST(MIN(CAST(CheckTime AS TIME)) AS VARCHAR(8)) AS DATETIME) FROM table_name AS t2 WHERE t2.EmployeeId = t1.EmployeeId GROUP By CheckTime ORDER BY CAST(CheckTime AS TIME) ) AS  MinDateTime
    , (SELECT TOP 1 CAST(CAST(CONVERT(DATE, CheckTime) AS VARCHAR(10)) + ' ' + CAST(MIN(CAST(CheckTime AS TIME)) AS VARCHAR(8)) AS DATETIME) FROM table_name AS t2 WHERE t2.EmployeeId = t1.EmployeeId GROUP By CheckTime ORDER BY CAST(CheckTime AS TIME) DESC ) AS MaxDateTime 
FROM table_name AS t1
WHERE CheckTime BETWEEN @min_date and @max_date GROUP BY EmployeeId;

SQL Fiddle - http://sqlfiddle.com/#!6/fd199/2 DATA:

1   2015-10-14 18:06:22
1   2015-10-28 23:06:40
2   2015-10-07 18:13:58
2   2015-10-14 11:14:15
1   2015-10-14 19:20:11
2   2015-10-08 10:20:34
3   2015-10-01 20:25:28
3   2015-10-30 10:26:04

RESULT:

1   2015-10-14 18:06:22.000 2015-10-28 23:06:40.000
2   2015-10-08 10:20:34.000 2015-10-07 18:13:58.000
3   2015-10-30 10:26:04.000 2015-10-01 20:25:28.000
8
  • what is concat_ws and date_format is not recognised date formate – user80536 Nov 16 '15 at 5:52
  • so what I use on that? – user80536 Nov 16 '15 at 5:52
  • Incorrect syntax near 'LIMIT'. and Incorrect syntax near the keyword 'ORDER' – user80536 Nov 16 '15 at 6:03
  • this is MySQL, I wrote - it will be little different for T-SQL or Oracle or PGSQL ... because no information about database in original post - I just take what was close but it very easy to adopt – a_vlad Nov 16 '15 at 6:06
  • example not good in comment, if want can make thread in sqlfidle sqlfiddle.com/#!9/f6dfa/1 check please – a_vlad Nov 16 '15 at 6:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.