• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • 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.

James.
0
JamesBell
Asked:
JamesBell
  • 3
  • 2
1 Solution
 
randy_knightCommented:
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
declare 
	@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
begin
	declare curOps cursor for 
		select OperatorName
		from dbo.t1 
		where @date between LeaveStart and LeaveEnd
	open curOps		
	while 1=1
	begin
		fetch next from curOps into @Operator		
		if @@fetch_status != 0
			break
 
 
		if exists(select * from Cover where CoverDate = @date)
			update Cover
				set Operators = coalesce(Operators,'') + ', ' + @Operator
			where CoverDate = @date
		else
			insert dbo.Cover 
			select @date, @Operator			
	end
	close curOps
	deallocate curOps
	
	set @date = dateadd(dd,1,@date)
end
 
	
	
	
select * from Cover
	

Open in new window

0
 
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?
0
 
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
randy_knightCommented:
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.
0
 
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!
0
 
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.

Regards

James
0
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

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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