Need query --- between two times

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need query --- between two times

Author

Topic

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-21 : 05:26:20

I am having these two tables
monitor_info
TranID Created_By Created_Date
1 G165 2011-12-20 15:26:01.560
2 G165 2011-12-20 16:22:20.720
3 G165 2011-06-30 13:20:17.993
9 S394 2011-06-30 16:48:02.727
4 S394 2011-06-30 16:55:53.313
2 S394 2011-06-30 16:56:09.433
11 G165 2011-12-20 15:26:01.560
11 G165 2011-07-05 17:54:31.360
11 G165 2011-12-20 16:22:20.720
11 G165 2011-12-20 16:22:20.720
12 G165 2011-12-20 15:26:01.560
12 G165 2011-12-20 16:22:20.720

srl_no emp_id login_date login_time logout_time
15 G165 2011-12-20 14:48:22.760 14:48:22.0000000 14:49:54.0000000
16 G165 2011-12-20 14:53:55.050 14:53:55.0000000 15:09:51.0000000
17 G165 2011-12-20 15:20:21.603 15:20:21.0000000 15:25:52.0000000
18 G165 2011-12-20 15:26:01.560 15:26:01.0000000 15:34:02.0000000
19 G165 2011-12-20 15:38:03.920 15:38:03.0000000 15:38:40.0000000
20 G165 2011-12-20 16:13:49.247 16:13:49.0000000 16:15:02.0000000
21 G165 2011-12-20 16:15:12.090 16:15:12.0000000 16:16:25.0000000
22 G165 2011-12-20 16:16:38.597 16:16:38.0000000 16:20:21.0000000
23 G165 2011-12-20 16:20:48.257 16:20:48.0000000 16:21:28.0000000
24 G165 2011-12-20 16:22:20.720 16:22:20.0000000 16:23:16.0000000
25 G165 2011-12-20 16:23:58.863 16:23:58.0000000 16:24:23.0000000

I need to findout the number of (count(tranID)) process
in a particular login

need to check the monitor_info table created_date field based on login and logout time

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:07:52

so what should be output out of above data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-26 : 05:32:24

Emp_id Login Time Logout_time Processed application
G165 14:48:22 1 4:49:54 0
G165 14:53:55 15:09:51 0
G165 15:20:21 15:25:52 0
G165 15:26:01 15:34:02 1
G165 15:38:03 15:38:40 0
G165 16:13:49 16:15:02 0
G165 16:15:12 16:16:25 0
G165 16:16:38 16:20:21 0
G165 16:20:48 16:21:28 0
G165 16:22:20 16:23:16 1
G165 16:23:58 16:24:23 0

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-26 : 05:45:59

Is it possible ? to compare time duration in a column (that contains date and time)(created_date in monitor_info) with other two columns (login,log_out in login_table)

Im expecting the followin result

No of enteries in monitor_info table (based on created_date field) between login,logout times of each rows in login table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 05:51:20

[code]
SELECT e.Emp_id, e.Login Time, e.Logout_time,CASE WHEN m.Cnt> 0 THEN 1 ELSE 0 END AS [Processed application]
FROM EMployeeLoginTable e
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM monitor_info
WHERE Created_By = e.Emp_Id
AND Created_Date = e.Login_Date
)m
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-26 : 06:04:33

Thanks for your reply

I lastly executed the above query against my db which contains the following rows

Tran_ ID Created_By Created_Date
1552 TB0012 2011-12-26 16:28:33.613
1551 TB0012 2011-12-26 16:28:50.733
1549 TB0012 2011-12-26 16:29:12.013

srl_no emp_id login_date login_time logout_time
53 TB0012 2011-12-26 16:27:43.217 16:27:43.0000000 16:29:18.0000000

but i got this result

Emp_id login_time Logout_time Processed application
TB0012 16:27:43.0000000 16:29:18.0000000 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 06:39:47

i gave suggestion based on your posted query in first post. Is that not your real scenario?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-26 : 06:48:13

Both are same scenarios..
Latest one is directly copied from the output ..
TB0012 is also an Employee id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 06:51:50

ok..and is that not your expected result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-26 : 06:54:20

Lastly I tested my application in this login time (16:27:43.0000000 16:29:18.0000000 ) and I have worked out 3 process (in monitor_info) .. I need the count that I worked in the last login

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 07:01:53

so what should be the output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-26 : 07:05:52

Emp_id login_time Logout_time Processed application
TB0012 16:27:43.0000000 16:29:18.0000000 3

This is my most expected output

Thanks for your response

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 07:19:35

what are datatypes of login_time, logout_time fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-26 : 07:22:02

both are in time (sql 2008)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 11:12:57

then my logic should work fine.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-27 : 01:09:22

quote:

Originally posted by visakh16


SELECT e.Emp_id, e.Login Time, e.Logout_time,CASE WHEN m.Cnt> 0 THEN 1 ELSE 0 END AS [Processed application] 
FROM EMployeeLoginTable e
OUTER APPLY (SELECT COUNT(*) AS Cnt
             FROM monitor_info
             WHERE Created_By = e.Emp_Id
             AND Created_Date = e.Login_Date
            )m

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Your logic is fine ..
Created_Date = e.Login_Date these two dates are not matched ..
Because both are date&time fields... It checks for the time also
If we compare only the dates means it will work..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 02:59:06

quote:
Originally posted by jafrywilson

quote:
Originally posted by visakh16

SELECT e.Emp_id, e.Login Time, e.Logout_time,CASE WHEN m.Cnt> 0 THEN 1 ELSE 0 END AS [Processed application]
FROM EMployeeLoginTable e
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM monitor_info
WHERE Created_By = e.Emp_Id
AND Created_Date = e.Login_Date
)m

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Your logic is fine ..
Created_Date = e.Login_Date these two dates are not matched ..
Because both are date&time fields... It checks for the time also
If we compare only the dates means it will work..

but your sample data showed both of them as having same date and time parts.
anyways use below modified suggestion


SELECT e.Emp_id, e.Login Time, e.Logout_time,CASE WHEN m.Cnt> 0 THEN 1 ELSE 0 END AS [Processed application] 
FROM EMployeeLoginTable e
OUTER APPLY (SELECT COUNT(*) AS Cnt
             FROM monitor_info
             WHERE Created_By = e.Emp_Id
             AND DATEADD(dd,0,Created_Date) = DATEADD(dd,0,e.Login_Date)
            )m

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2011-12-28 : 02:36:18

It also returns the same result ...

I tried this
DATEADD(D, 0, DATEDIFF(D, 0, Created_Date)) = DATEADD(D, 0, DATEDIFF(D, 0, e.Login_Date)) instead of

DATEADD(dd,0,Created_Date) = DATEADD(dd,0,e.Login_Date)

Then it results the no of applications as 1.. But my expected result is 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 03:39:49

[code]
SELECT e.Emp_id, e.Login Time, e.Logout_time,m.Cnt AS [Processed application]
FROM EMployeeLoginTable e
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM monitor_info
WHERE Created_By = e.Emp_Id
AND DATEADD(dd,0,Created_Date) = DATEADD(dd,0,e.Login_Date)
AND CAST(Created_Date as time) >= e.login_time
AND CAST(Created_Date as time) <= e.logout_time)m

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jafrywilson
Constraint Violating Yak Guru

379 Posts

Posted - 2012-01-10 : 00:15:18

I modified the query as like this ...

SELECT e.Emp_id, e.Login_Time, e.Logout_time,m.Cnt AS [Processed application]
FROM login_history e
OUTER APPLY (SELECT COUNT(*) AS Cnt
FROM monitor_info
WHERE Created_By = e.Emp_Id
and
Year(Created_Date) = Year(Login_Date)
AND Month(Created_Date) = Month(Login_Date)
AND Day(Created_Date) = Day(Login_Date)
)m

thank you so much visakh....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 04:01:06

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources