Solved

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

Posted on 2008-10-06
6
169 Views
Last Modified: 2010-04-21
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
Comment
Question by:JamesBell
  • 3
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
randy_knight earned 125 total points
ID: 22656500
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
 

Author Comment

by:JamesBell
ID: 22656656
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22659427
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Expert Comment

by:randy_knight
ID: 22663241
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
 

Author Closing Comment

by:JamesBell
ID: 31503703
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
 

Author Comment

by:JamesBell
ID: 22664009
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

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now