• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

How do I use a startdate & enddate field to create multiple records with incrementing date?

Hi there

I have created a leave application list in sharepoint with an OperatorName, LeaveStartDate, LeaveEndDate and LeaveApplicationDate. I can extract the data I need from sharepoint and end up with a table in SQL Server 2005 like this:

OperatorName      LeaveStartDate     LeaveEndDate     LeaveApplicationDate
Bob                       04-Sep-2008         08-Sep-2008       01-Aug-2008
Fred                      05-Sep-2008         05-Sep-2008       01-Jun-2008
Joe                        07-Sep-2008         09-Sep-2008       16-Aug-2008

I would like to create a table / view that looks like the following, putting the operators in a single field in the order of the leave application date:

CoverDate        Operator(s)
04-Sep-2008     Bob
05-Sep-2008     Fred, Bob
06-Sep-2008     Bob
07-Sep-2008     Bob, Joe
08-Sep-2008     Bob, Joe
09-Sep-2008     Joe

The reason I would like the data in this format is that I want to import it into Excel and perform a vlookup on this table.

Thank you for your time.

  • 3
  • 2
1 Solution
Here is one way.  There is likely a more elegant way to do it if I thought about it some more:

if object_id('dbo.Cover') is null 
	create table Cover (
		CoverDate	datetime,
		Operators	varchar(50)
truncate table Cover
	@date datetime,
	@start datetime,
	@end datetime,
	@Operator varchar(50)
select @start = min(LeaveStart), @end = max(LeaveEnd) from dbo.t1
set @date = @start
while @date <= @end
	declare curOps cursor for 
		select OperatorName
		from dbo.t1 
		where @date between LeaveStart and LeaveEnd
	open curOps		
	while 1=1
		fetch next from curOps into @Operator		
		if @@fetch_status != 0
		if exists(select * from Cover where CoverDate = @date)
			update Cover
				set Operators = coalesce(Operators,'') + ', ' + @Operator
			where CoverDate = @date
			insert dbo.Cover 
			select @date, @Operator			
	close curOps
	deallocate curOps
	set @date = dateadd(dd,1,@date)
select * from Cover

Open in new window

JamesBellAuthor Commented:
Wow, thanks that does work. It is a bit confusing for me as I am not familiar with cursors.

However while it works it also gives me this error:

Msg 8152, Level 16, State 14, Procedure ReturnToExcel, Line 160
String or binary data would be truncated.
The statement has been terminated.

Is there any way to avoid / suppress this error?
Patrick MatthewsCommented:
JamesBell said:
>>Msg 8152, Level 16, State 14, Procedure ReturnToExcel, Line 160
>>String or binary data would be truncated.

That usually indicates that you are trying to fit a size 12 foot into a size 10 shoe :)

What is the text of line 160 in your SQL?  My guess is that you are trying to insert a value into a column
that is too large for the column's data type.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Make your operators column bigger.  I made it varchar(50) in my example but you probably have a lot more for that string in real life.
JamesBellAuthor Commented:
Thanks for the solution! I obviously need more training so I can understand the code better, but it solves my problem so I'm happy!
JamesBellAuthor Commented:
I adjusted my operator column size but that didn't seem to help. I then spotted that my test data was incorrect, with one operator having selected leave for the same day several times. I changed this and all is well now.

Thanks to all for your help.


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

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now