Solved

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

Posted on 2008-10-06
6
196 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 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