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

Posted on 2013-10-08
Medium Priority
Last Modified: 2016-02-11

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.
Question by:susnewyork
LVL 66

Accepted Solution

Jim Horn earned 2000 total points
ID: 39556753
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

LVL 41

Expert Comment

by:Kyle Abrahams
ID: 39556810
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, l.name 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, l.name from #temp t
cross join #locations l

drop table #temp
drop table #locations

Open in new window


Author Closing Comment

ID: 39557045
Thanks. This was perfect.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question