SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Insert Into Select?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DaNuGai
Starting Member

10 Posts

Posted - 03/27/2013 :  10:00:53  Show Profile  Reply with Quote
Hi Guys,

I have a table with the following data: RoomID, BuildingID, MaxSeats, and Room Availability (5 columns, Mon-Fri, boolean fields).

I have another table with the list of employees and the days they are available. EmployeeID, ..., EmployeeAvailability (5 columns, Mon-Fri, boolean fields).

Now, in the third table, I'm looking to randomly assign (insert records) where Employee availability matches the Room availability, but also taking into consideration the maximum number of seats allowed in a given room.

Any ideas on how I can go about doing this in SQL? I know I can loop through the employee table, match the Emp Availability to Room Availability, and count the #OfEmp in that room before doign an insert. However, I don't know if that's the most efficient way of accomplishing this task. I'm hoping someone here can guide/help me accomplish this task in more of an efficient manner if possible.

Thanks in advance.

DaNuGai
Starting Member

10 Posts

Posted - 03/29/2013 :  08:10:11  Show Profile  Reply with Quote
Morning Guys,

Is there anyone here who could help me with this? I'd appreciate your help.

Thanks in advance.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/29/2013 :  08:18:43  Show Profile  Reply with Quote
Post the structure of 3rd table, sample data for first two tables and output for 3rd table based on your logic...
Its better to get quick response from anyone in the forum
Go to Top of Page

DaNuGai
Starting Member

10 Posts

Posted - 03/29/2013 :  08:43:35  Show Profile  Reply with Quote
Hi Bandi, I appreciate your reply. Here is the same table structure...


CREATE TABLE Participant (
	ParticipantID INT NOT NULL,
	ParticipantName VARCHAR(50) NOT NULL,
	BuildingID SMALLINT NOT NULL,
	AvailableDayA BIT NOT NULL,
	AvailableDayB BIT NOT NULL,
	AvailableDayC BIT NOT NULL,
	AvailableDayD BIT NOT NULL,
	AvailableDayE BIT NOT NULL,
   PRIMARY KEY(ParticipantID)
)
CREATE TABLE BuildingRoom (
	BuildingID SMALLINT NOT NULL,
	RoomNum VARCHAR(5) NOT NULL,
	TrainerID INT NOT NULL,
	MaxSeats SMALLINT NOT NULL,
	AvailableDayA BIT NOT NULL,
	AvailableDayB BIT NOT NULL,
	AvailableDayC BIT NOT NULL,
	AvailableDayD BIT NOT NULL,
	AvailableDayE BIT NOT NULL,
   PRIMARY KEY(BuildingID, RoomNum)
)
CREATE TABLE ParticipantEnrollment (
	ParticipantID INT NOT NULL,
	BuildingID SMALLINT NOT NULL,
	RoomNum VARCHAR(5) NOT NULL,
   PRIMARY KEY(ParticipantID, BuildingID, RoomNum)
)


Just so you know, I'm looking to auto populate ParticiantEnrollment table with the number of participants not exceeding the max seats limit

Edited by - DaNuGai on 03/29/2013 13:49:32
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/01/2013 :  05:54:00  Show Profile  Reply with Quote
May be this?

--INSERT INTO ParticipantEnrollment
SELECT ParticipantID, BuildingID, RoomNum
FROM (SELECT p.ParticipantID, p.BuildingID, b.RoomNum, COUNT(*) OVER(PARTITION BY p.ParticipantID, p.BuildingID, b.RoomNum) Cnt, MaxSeats
		FROM Participant p
		JOIN BuildingRoom b
		ON p.BuildingID = b.BuildingID AND CAST(RAND()*ParticipantID as INT) < MaxSeats
		WHERE( p.AvailableDayA = 1 OR p.AvailableDayB = 1 OR  p.AvailableDayC= 1 OR  p.AvailableDayD= 1 OR  p.AvailableDayE= 1 )
		AND ( b.AvailableDayA= 1 OR b.AvailableDayB= 1 OR b.AvailableDayC= 1 OR b.AvailableDayD= 1 OR b.AvailableDayE =1)
	)t
WHERE t.Cnt < = MaxSeats

Edited by - bandi on 04/01/2013 05:57:56
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000