Selecting records - Please Help.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting records - Please Help.

Author

Topic

nm_lytton
Starting Member

6 Posts

Posted - 2011-03-20 : 07:03:58

Hello Guys,

I have a Table named tbl_Test with some records like -

StaffID LogDate LogTime

1 2011/09/01 08:30
2 2011/09/01 08:15
3 2011/09/01 08:20
1 2011/09/01 16:10
2 2011/09/01 16:25
3 2011/09/01 16:15

From this table I need to select records which result should be -

StaffID LoginDate LogINTime LogOUTTime

1 2011/09/01 08:30 16:10
2 2011/09/01 08:15 16:25
3 2011/09/01 08:20 16:15

--------------------
Here LogINTime=MIN(LogTime) and LogOUTTIME=MAX(LogTime)

Please advice me with sample.
Thanks in advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-20 : 07:45:25

For your test data, it would seem like you can do following:

select
   staffID,
   cast(LogDate as Date) as LogDate,
   cast(min(LogDate) as Time) as LogINTime,
   cast(max(LogDate) as Time) as LogOUTTime
from
   YourTable
group by
   staffId,
   cast(LogDate as Date)

BUT you would want to know:
a) can a user log in and log out multiple times in a single day?
b) can a user have multiple simultaneous login sessions?

You would want to answer those questions and modify your logic to account for those scenarios.

nm_lytton
Starting Member

6 Posts

Posted - 2011-03-20 : 23:26:30

Hello sunitabeck,

Thank you very much for your prompt reply. I have tried your script but it gives the following error -

Msg 243, Level 16, State 1, Line 1
Type Date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type Date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type Time is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type Time is not a defined system type.

I am using SQL Server 2005 Express version.

Please advice me what to do.
Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-21 : 00:41:44

I think I know the answer to this...but what are the datatypes for LogDate and LogTime?

nm_lytton
Starting Member

6 Posts

Posted - 2011-03-21 : 02:06:20

They are "smalldatetime"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-21 : 03:11:15

quote:

Originally posted by sunitabeck

select
   staffID,
   cast(LogDate as Date) as LogDate,
   cast(min(LogDate) as Time) as LogINTime,
   cast(max(LogDate) as Time) as LogOUTTime
from
   YourTable
group by
   staffId,
   cast(LogDate as Date)

KH
[spoiler]Time is always against us[/spoiler]

nm_lytton
Starting Member

6 Posts

Posted - 2011-03-21 : 04:26:24

@ khtan,

Thanks for your advice. Your script results all records, where as I need result like-

StaffID LoginDate LogINTime LogOUTTime

1 2011/09/01 08:30 16:10
2 2011/09/01 08:15 16:25
3 2011/09/01 08:20 16:15

Result should be per StaffID for per LoginDate with LogINTime and LogOUTTime.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-21 : 04:36:55

isn't this what the query is doing ? Can you post the query that you used ?

KH
[spoiler]Time is always against us[/spoiler]

nm_lytton
Starting Member

6 Posts

Posted - 2011-03-21 : 05:32:29

I tried:

SELECT
staffID,AccessDate,MIN(AccessTime)AS LogINTime ,MAX(AccessTime) AS LogOUTTime
FROM
tbl_Test
GROUP BY
staffId,AccessDate

Result Shows:

2011-03-09 08:37:00 0003131977 2011-03-21 08:37:00 2011-03-21 08:37:00
2011-03-09 16:32:00 0003131977 2011-03-21 04:32:00 2011-03-21 04:32:00
2011-03-10 08:40:00 0003131977 2011-03-21 08:40:00 2011-03-21 08:40:00
2011-03-10 16:36:00 0003131977 2011-03-21 04:36:00 2011-03-21 04:36:00
2011-03-12 08:39:00 0003131977 2011-03-21 08:39:00 2011-03-21 08:39:00
2011-03-12 16:48:00 0003131977 2011-03-21 04:48:00 2011-03-21 04:48:00
2011-03-13 08:26:00 0003131977 2011-03-21 08:26:00 2011-03-21 08:26:00
2011-03-13 17:03:00 0003131977 2011-03-21 05:03:00 2011-03-21 05:03:00
2011-03-14 08:42:00 0003131977 2011-03-21 08:42:00 2011-03-21 08:42:00
2011-03-14 17:02:00 0003131977 2011-03-21 05:02:00 2011-03-21 05:02:00
2011-03-15 08:33:00 0003131977 2011-03-21 08:33:00 2011-03-21 08:33:00
2011-03-15 16:35:00 0003131977 2011-03-21 04:35:00 2011-03-21 04:35:00

I need:

2011-03-09 08:37:00 0003131977 2011-03-21 08:37:00 2011-03-21 04:32:00
2011-03-10 08:40:00 0003131977 2011-03-21 08:40:00 2011-03-21 04:36:00
2011-03-12 08:39:00 0003131977 2011-03-21 08:39:00 2011-03-21 04:48:00
2011-03-13 08:26:00 0003131977 2011-03-21 08:26:00 2011-03-21 05:03:00
2011-03-14 08:42:00 0003131977 2011-03-21 08:42:00 2011-03-21 05:02:00
2011-03-15 08:33:00 0003131977 2011-03-21 08:33:00 2011-03-21 04:35:00

Please help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-21 : 06:10:43

oh so you only have a column AccessDate and not 2 separate column of LogDate & LogTime


SELECT staffID,
       dateadd(day, datediff(day, 0, AccessDate), 0) as LogDate,
       MIN(AccessTime) AS LogINTime ,
       MAX(AccessTime) AS LogOUTTime
FROM   tbl_Test
GROUP BY staffID, dateadd(day, datediff(day, 0, AccessDate), 0)

KH
[spoiler]Time is always against us[/spoiler]

nm_lytton
Starting Member

6 Posts

Posted - 2011-03-21 : 06:35:50

@ khtan,

YES, it is working now :-), that is what I required.

You Guys are rally great.
THANKS A LOT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources