November 23, 2009 at 10:28 am
I have a table of users. I have a table of weekending dates. I have a main table that I would like to insert a row into based on the other two tables.
What I'd like to do is write a query that will insert 1 row for each user for each of the 52 weeks in the weekending table into the main table.
I wrote something like this do to each user indivudually. I just change the dnnID each time to insert a new person's 52 weeks.
-----------------------
declare @dnnID int
set @dnnID = 127 /* this is their userid in the WeeklyScheduleUser table */
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',94)
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',95)
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',96)
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',97)
insert into weeklyschedule (userID, day1, day2, day3, day4, day5, weekEnding) values (@dnnID, 'Unknown','Unknown','Unknown','Unknown','Unknown',98)
.....
------------------------
they're is 52 of the insert statements and I just change the dnnID each time. Needless to say, it's a lot of inserts. I'd really like to find a way to write something that would look at the user and insert a new row for each of the 52 weeks of the year into the main table.
I had thought to use for each, but I'm unsure how to get started.
while(select seq from weeklyscheduleUser)<128 (127 is the last sequence number in that table)
begin
while(select seq from weeklyscheduleSaturdays) < 146 (146 is the first full week of 2010)
begin
INSERT INTO WeeklySchedule (userID, day1, day2, day3, day4, day5, weekending) values (<want to use weeklyscheduleUser.seq here>, 'In Building' ,'In Building' ,'In Building' ,'In Building' ,'In Building' ,<want to use weeklyscheduleSaturday.seq here>)
break
continue
end
November 24, 2009 at 7:24 am
I think you want to look at a CROSS JOIN. A CROSS JOIN creates a Cartesian product, one row for each combination of data. In your situation I think something like this will work:
INSERT INTO weeklyschedule
(
userID,
day1,
day2,
day3,
day4,
day5,
weekEnding
)
SELECT
U.userID,
WE.day1,
WE.day2,
WE.day3,
WE.day4,
WE.day5
WE.weekending
FROM
users AS U CROSS JOIN
weekending AS WE
It is hard to give an accurate solution because there is limited information available. If you read the top 2 articles linked in my signature and post as recommended you will likely get a tested and complete solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 24, 2009 at 8:37 am
I get what you mean in the first two articles. To tell the truth, I was looking more for a shove in the right direction as to what to use so I could work some of it out myself. Your reply was spot on, I didn't realize you could do that in an insert.
Thanks for the shove, and for the tips on posting.
November 24, 2009 at 8:41 am
I'm actually looking for some advice as well. I designed this really quickly to put out a fire a year or so ago, but what the point of the app was at the time was to be able to track where people were during the week. That way the regional people know where their people are, Office - Vacation - Holiday - Building Name, etc., as well as the building folx know when the execs are expecting to show up.
I built an app that will allow each individual to update their own info.
The biggest issue is that I have to run this insert at the end of the year to prepopulate the tables so people can update them. Should I have used some kind of custom code to see if they had a row for that week, and if not, insert one for them, if they did, update it?
just curious.
November 24, 2009 at 9:07 am
Never having built a scheduling app, I'm not sure what the best way would be. I would likely do something with a structure like this:
CREATE TABLE [dbo].[calendar](
[the_date] [datetime] NOT NULL PRIMARY KEY CLUSTERED,
[the_year] [smallint] NOT NULL,
[the_quarter] [tinyint] NOT NULL,
[the_month] [tinyint] NOT NULL,
[month_name] [varchar](9) NOT NULL,
week_ending_date SMALLDATETIME NOT NULL,
[the_week] [tinyint] NOT NULL,
[the_day] [tinyint] NOT NULL,
[day_name] [varchar](9) NOT NULL,
[the_day_of_week] [tinyint] NOT NULL,
[the_day_of_the_year] [smallint] NOT NULL,
[is_holiday] [bit] NOT NULL,
[is_weekend] [bit] NOT NULL,
[is_business_day] [bit] NOT NULL
)
CREATE TABLE dbo.schedule
(
the_date DATETIME,
person_id INT,
location_id INT,
status_id INT
)
CREATE TABLE dbo.persons
(
person_id INT IDENTITY(1,1),
first_name VARCHAR(15),
last_name VARCHAR(25)
)
CREATE TABLE dbo.locations
(
location_id INT IDENTITY(1,1),
location_name VARCHAR(25)
)
CREATE TABLE dbo.statuses
(
status_id INT,
status_name VARCHAR(15)
)
Then entry would be a simple screen where the person selects their name (it could be selected automatically), enters the date, and selects the location and status for that day. Then when your query would be this:
SELECT
C.week_ending_date,
S.the_date,
P.first_name,
P.last_name,
L.location_name,
S2.status_name
FROM
dbo.calendar AS C JOIN
dbo.schedule AS S
ON C.the_date = S.the_date JOIN
dbo.persons AS P
ON S.person_id = person_id JOIN
dbo.statuses AS S2
ON S.status_id = S2.status_id JOIN
dbo.locations AS L
ON S.location_id = L.location_id
WHERE
C.week_ending_date = 'Some Date'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply