retrieve names of users from sql server

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 2005 Forums

Other SQL Server Topics (2005)

retrieve names of users from sql server

Author

Topic

jaimin
Starting Member

21 Posts

Posted - 2007-05-24 : 08:35:40

how to retrieve names of the users between start time and end time using stored procedure in sql server??

i have a table which stores user names along with other details.
can anybody tell me how to get the names of the users between start time and end time??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 08:43:07

[code]Select user_name from Table
where login_time between @StartTime and @EndTime[/code]

Post your table structure along with sample data to get more accurate answers.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2007-05-24 : 09:13:22

thanx.
i have two tables- table_A and table_B. In table_A, i am inserting values at run time. table_B already has all the license details which can be used.
Now i want to check which license was used between start time and end time from table_A.
For the same time slot i cannot use same license again, so i want to check table_B that which licenses are free for that time slot?? so that i can use any of those free licenses.

i know its very complicated...that's why i need your help!!
thanx in advance.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 09:17:05

Okay, that was the start.
What is the joining field (common field) between the two tables?
How you determine the license is free for the time-slot or not?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2007-05-24 : 09:51:20

common fields between two tables is "userId".
i think i have confused you.

actually table_A has those license which are used between a particular startTime and endTime.
for ex. between startTime 10:00 and endTime 10:30, the license used are AA1, AA2 and AA3.
so for the this time slot( between 10:00 and 10:30), i cannot use licenses AA1,AA2,AA3 again.

table_B has licenses AA1,AA2,...,AA25.(all the license details are stored manually in this table).
Now i want to check that among licenses AA1,AA2,...,AA25 which licenses are not used between starttime 10:00 and endTime 10:30.
i have used AA1,AA2 and AA3, so now AA4,AA5,...,AA25 are free for this time slot.

Jaimin

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 09:58:45

Something like this may be then:

Select t2.License
from TableB t2 Left Join TableA t1 on t2.UserId = t1.UserId
Where t1.UserId Is Null and t1.StartTime = @starttime and endtime = @endtime

What is the datatype for starttime and endtime columns?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2007-05-24 : 10:02:21

thanx Harsh for your help..i really appreciate it.

Datatypes for both of them are varchar(50).

but am i making any mistake? should i use some other datatype?? or this will work?

Jaimin

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-24 : 10:06:23

Ideally, you should store time data in a Datetime column(even though you are not storing dates). This will simplify queries when fetching data for date or time range, as is the case with your query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2007-05-24 : 10:09:13

thanx for your valuable time harsha. you've solved the problem.
thanx again...

Jaimin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources