Create row values based on each day of the month and by location


I have a need to create a script that inserts values into two columns. These columns will need to include each day of the current month and each location in my company.

Example: If my company has 3 locations and this month is October then the table would need to look something like this:

DayOfMonth                                      Location
Monday, October 7, 2013                           Lennox Hill
Tuesdayy, October 8, 2013                       Lennox Hill
Wednesday, October 9, 2013                   Lennox Hill
Thursday, October 10, 2013                      Lennox Hill
Friday, October 11, 2013                           Lennox Hill
Saturday, October 12, 2013                        Lennox Hill
Sunday, October 13, 2013                           Lennox Hill
Monday, October 7, 2013                           Bronx 1
Tuesdayy, October 8, 2013                         Bronx 1
Wednesday, October 9, 2013                    Bronx 1
Thursday, October 10, 2013                     Bronx 1
Friday, October 11, 2013                         Bronx 1
Saturday, October 12, 2013                       Bronx 1
Sunday, October 13, 2013                      Bronx 1
Monday, October 7, 2013                      Brooklyn 1
Tuesdayy, October 8, 2013                     Brooklyn 1
Wednesday, October 9, 2013                Brooklyn 1
Thursday, October 10, 2013                  Brooklyn 1
Friday, October 11, 2013                      Brooklyn 1
Saturday, October 12, 2013                  Brooklyn 1
Sunday, October 13, 2013                   Brooklyn 1

So, I need to have each day of the month in it's own separate row with a column that shows each location. We, in fact, have 50+ locations and this will need to be dynamic so that as the months change, so does the data in the table. This will be part of an SSIS package.

I appreciate your help on this. Any help that steers me in the right direction would be great.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Here's everything except for the date formatting..
-- Set your start date, end date, and number of days
Declare @StartDt date = '2013-03-01'
Declare @EndDt date = DATEADD(M, 1, @StartDt)
declare @Days int = DATEDIFF(d, @StartDt, @EndDt) 

SELECT @StartDt, @EndDt, @Days

Declare @i int = 0

WHILE @i < @Days

	INSERT INTO YourTable (DayOfMonth, Location) 
	SELECT DATEADD(d, @i, @StartDt), Location
	FROM the_location_table
	SET @i = @i + 1

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperCommented:
This creates a list of days in a temporary table, then cross joins with a locations table (temporary in my case, but you can update)

the final result set could be inserted into a temporary table:

eg:  truncate table tempMonths
insert into tempmonths
select t.wd, from #temp t
cross join #locations l

which could then be used later on.

--setup temp table
select cast(' ' as varchar(500)) wd, getdate() dt into #temp
delete from #temp

declare @startDate  datetime
declare @endDate  datetime
set @startDate = DATEADD(month, DATEDIFF(month, 0, getdate()), 0)
set @endDate = dateadd(month, 1, @startdate)
--select @startDate , @endDate  DEBUG

--create table
while @startdate < @endDate
insert into #temp
		datename(Weekday, @startdate) + ', ' +
		datename(mm, @startdate) + ' ' +
		datename(day, @startdate) + ', ' +
		datename(year, @startdate)
set @startdate = dateadd(dd, 1, @startdate)

-- DEBUG select * from #temp

--temporary locations table
select 'BK' name into #locations
insert into #locations select 'BX'

--the combination
select t.wd, from #temp t
cross join #locations l

drop table #temp
drop table #locations

Open in new window

susnewyorkAuthor Commented:
Thanks. This was perfect.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.