Need a little tip for SQL query..

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

Need a little tip for SQL query..

Author

Topic

spradhan01
Starting Member

6 Posts

Posted - 2012-02-20 : 20:54:19

I was working on a software which assigns the seats in a given office.
Its an ASP based application. For example, there are two employee
where one works from 01/01/12 to 01/10/12 and other one work from 01/16/12.
So, I need to comeup with a query which finds the seats open between that time.i.e it needs to find a seat between dates 01/10 and 01/16.
How can I achieve that considering there are 200+ employee with different start date and close date?(any gap between dates would be considered open seats)

I hope to get some help from here.

Thank You

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-02-21 : 01:37:02

Do you have a table(s) DDL , so we can construct a sql statement?
Do you want to find the seat per employee?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

Azhar Khan
Starting Member

3 Posts

Posted - 2012-02-21 : 03:50:48

To construct the SQL code i need structure of the table so that i can easily help you out from this.

Azhar khan

spradhan01
Starting Member

6 Posts

Posted - 2012-02-21 : 16:35:33

Primary Key
Emp Name [PK1]
Emp_Seat_ID [PK2]
D_EMP_Seat_frm [PK3]

Non-Key Attributes
D_Emp_Seat_Thru

I just need to find the open seats.

Say Seat1, there are three employees
A works Jan 1-5
B works Jan 10-15
C works Jan 20-25
Seat 2, there are two employees
X works from Jan 1-5
Y works from Jan 10-6
So, when I run the query it should show me the seats which are open.
If I run the query on Jan 6, then it should show what seats are open on that day. In above case, it should show Seat 1 and Seat 2 open.
Now there are like 250 seats where the comparision should be done.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 17:27:29

you mean this?


SELECT t.Emp_Seat_ID
FROM table t
WHERE NOT EXISTS(SELECT 1 
FROM table
WHERE Emp_Seat_ID = t.Emp_Seat_ID
AND @datevlaue >=D_EMP_Seat_frm
AND @datevlaue <_Emp_Seat_Thru + 1
)

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

spradhan01
Starting Member

6 Posts

Posted - 2012-02-21 : 18:07:12

Looks good!
I need to make some modifications.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-21 : 19:30:25

pass a value for @datevalue and check the output

it basically gives you details of seats which are not present in table with a user for any date range including that date

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-22 : 00:22:56

quote:
Originally posted by spradhan01

Looks good!
I need to make some modifications.

obviously you need to
i just gave a stub for you to start on and you need to change to make sure it suits your requirements

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources