February 18, 2009 at 10:56 pm
hello sir/ madam,
hope U will help ,I need ur help urgently,
I have a table in which I have log employee login and logout time like:
RecID LoginOwner Token LoginDateTime LogoutDateTime
----------- ---------- -------------------------------------------------- --
1 admin Failed 2009-01-12 15:05:50.217 2009-01-12 15:05:50.217
2 Admin M1C200911215822984R0 2009-01-12 15:08:45.700 2009-01-12 15:09:43.293
3 TestHW M2C2009112151039468R5 2009-01-12 15:11:02.187 2009-01-12 15:29:10.780
4 Admin M1C2009112152858640R0 2009-01-12 15:29:21.373 2009-01-12 15:30:21.500
5 TestHW M2C200911215307812R5 2009-01-12 15:30:30.543 2009-01-12 15:34:14.890
6 Admin M1C200911215342281R0 2009-01-12 15:34:25.030 2009-01-13 12:54:33.980
7 Admin M1C2009112162256531R0 2009-01-12 16:23:19.343 2009-01-12 16:24:35.293
8 TestHW M2C2009112162425765R5 2009-01-12 16:24:48.577 2009-01-13 12:54:33.980
9 TestHW M2C2009112163813359R5 2009-01-12 16:38:36.187 2009-01-13 12:54:33.980
means a user can login and logout multiple time in a day
so I need to track user's first login and last logout time of the day for a month so that i can calculate total hours working in a months of that perticular user.
Please its urgent...
February 19, 2009 at 1:16 am
Something like
select LoginOwner, min(LoginDateTime) as min_time, max(LogoutDateTime) as max_time
from table
group by LoginOwner,
dateadd(day,datediff(day,LoginDateTime,0),0),
dateadd(day,datediff(day,LogoutDateTime,0),0)
Failing to plan is Planning to fail
February 19, 2009 at 2:35 am
No its not working properly if I execute this query it will return result like:
LoginOwner min_time max_time
---------- ----------------------- -----------------------
Admin 2009-02-19 13:44:00.513 NULL
Admin 2009-02-19 12:12:19.280 2009-02-19 14:59:52.123
Admin 2009-02-18 18:23:43.437 2009-02-19 12:12:19.357
Admin 2009-02-18 11:55:37.763 2009-02-18 20:23:28.187
Admin 2009-02-17 18:39:21.483 2009-02-18 11:55:37.840
Admin 2009-02-17 15:59:53.793 2009-02-17 19:38:37.483
Admin 2009-02-16 17:29:20.450 2009-02-17 15:59:54.043
Admin 2009-02-16 11:38:50.500 2009-02-16 20:56:16.030
Admin 2009-02-14 16:10:58.467 2009-02-16 10:57:22.327
Admin 2009-02-14 11:18:25.530 2009-02-14 18:04:10.217
Admin 2009-02-13 19:56:39.077 2009-02-14 11:18:25.780
Admin 2009-02-13 12:34:36.170 2009-02-13 20:52:52.903
Admin 2009-02-12 17:39:47.670 2009-02-13 12:34:36.357
Admin 2009-02-12 12:17:43.357 2009-02-12 17:49:46.890
Admin 2009-02-11 17:29:45.530 2009-02-12 12:03:35.560
Admin 2009-02-11 11:28:14.200 2009-02-11 19:15:39.340
Admin 2009-02-10 17:02:46.920 2009-02-11 11:28:14.357
Admin 2009-02-10 11:09:31.607 2009-02-10 18:48:44.980
Admin 2009-02-09 17:47:57.077 2009-02-10 11:09:31.827
Admin 2009-02-09 11:10:27.467 2009-02-09 19:27:26.687
Admin 2009-02-07 17:42:49.780 2009-02-09 11:10:27.687
Admin 2009-02-07 15:01:58.217 2009-02-07 17:42:49.793
Admin 2009-02-06 16:37:34.280 2009-02-07 15:01:58.437
Admin 2009-02-06 11:07:07.640 2009-02-06 18:35:55.670
Admin 2009-02-05 17:38:20.483 2009-02-06 11:07:08.060
Admin 2009-02-05 10:58:34.293 2009-02-05 19:12:08.873
Admin 2009-02-04 20:33:30.060 2009-02-05 10:58:34.623
Admin 2009-02-04 11:01:56.247 2009-02-04 21:02:49.623
Admin 2009-02-03 18:38:48.903 2009-02-04 11:01:56.420
Admin 2009-02-03 11:03:30.997 2009-02-03 20:34:08.717
Admin 2009-02-02 19:54:26.013 2009-02-03 11:03:31.187
Admin 2009-02-02 15:21:17.280 2009-02-02 19:53:26.450
Admin 2009-01-31 13:16:57.747 2009-01-31 19:03:45.200
Admin 2009-01-30 20:02:16.890 2009-01-31 13:16:57.903
Admin 2009-01-30 18:20:36.360 2009-01-30 19:58:46.280
Admin 2009-01-29 17:18:45.763 2009-01-30 18:20:36.467
Admin 2009-01-29 12:20:50.577 2009-01-29 17:41:19.170
Admin 2009-01-28 18:00:01.390 2009-01-28 18:07:28.123
Admin 2009-01-27 17:32:15.217 2009-01-28 18:00:01.497
Admin 2009-01-27 11:22:43.187 2009-01-27 18:23:51.810
Admin 2009-01-23 21:30:16.950 2009-01-27 11:22:43.420
Admin 2009-01-23 13:40:32.060 2009-01-23 21:18:04.343
Admin 2009-01-22 17:04:44.233 2009-01-23 13:40:32.153
Admin 2009-01-22 12:09:07.687 2009-01-22 17:04:44.247
Admin 2009-01-21 18:56:56.183 2009-01-22 12:09:07.827
Admin 2009-01-21 10:48:44.890 2009-01-21 20:10:36.170
Admin 2009-01-20 18:52:04.810 2009-01-21 10:48:44.983
Admin 2009-01-20 13:24:53.543 2009-01-20 19:52:04.293
Admin 2009-01-19 17:21:22.950 2009-01-20 13:24:53.687
Admin 2009-01-19 11:25:24.950 2009-01-19 17:47:16.793
Admin 2009-01-16 17:00:01.437 2009-01-19 11:25:25.043
Admin 2009-01-16 12:13:26.513 2009-01-16 17:35:19.280
Admin 2009-01-15 13:31:24.093 2009-01-15 20:08:23.700
Admin 2009-01-14 21:15:13.140 2009-01-15 13:31:24.170
Admin 2009-01-14 11:05:10.983 2009-01-14 21:31:40.810
Admin 2009-01-13 17:58:43.997 2009-01-14 11:05:11.123
Admin 2009-01-13 12:54:33.887 2009-01-13 19:07:10.513
Admin 2009-01-12 15:34:25.030 2009-01-13 12:54:33.980
Admin 2009-01-12 15:05:50.217 2009-01-12 16:24:35.293
testAnA 2009-02-13 12:37:42.123 2009-02-13 12:43:10.513
testDsg 2009-02-13 12:44:31.327 2009-02-13 12:44:36.920
TestHW 2009-02-09 16:26:54.000 2009-02-09 16:34:15.747
TestHW 2009-01-13 16:12:09.077 2009-01-13 16:12:23.187
TestHW 2009-01-12 16:24:48.577 2009-01-13 12:54:33.980
TestHW 2009-01-12 15:11:02.187 2009-01-12 17:26:26.890
testMgr 2009-02-16 10:57:21.547 2009-02-16 14:47:19.797
testMgr 2009-02-14 17:39:01.607 2009-02-14 17:39:31.250
testMgr 2009-02-13 21:01:57.123 2009-02-14 11:18:25.793
means It shows maltiple row at a time like if u see the row
Admin 2009-02-18 18:23:43.437 2009-02-19 12:12:19.357
Admin 2009-02-18 11:55:37.763 2009-02-18 20:23:28.187
it shows that two row but i need only one row of date 18 means I have to show first login time which is (11:55:37.763 ) and last logout time (20:23:28.187)
Thanks
February 19, 2009 at 3:47 am
How about this one?
SELECTLoginOwner, DATEADD( DAY, 0, DATEDIFF( DAY, 0, LoginDateTime ) ) AS LogDate,
MIN( LoginDateTime ) AS MinTime,
MAX( LogoutDateTime ) AS MaxTime
FROMSomeTable
GROUP BY LoginOwner, DATEADD( DAY, 0, DATEDIFF( DAY, 0, LoginDateTime ) )
ORDER BY LoginOwner, LogDate
--Ramesh
February 19, 2009 at 4:38 am
Thanks Ramesh ,Its Working 😀
February 20, 2009 at 9:48 pm
So, does it actually work for...
Admin 2009-02-18 18:23:43.437 2009-02-19 12:12:19.357
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply