Solved

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

Posted on 2008-10-06
6
179 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 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

23 Experts available now in Live!

Get 1:1 Help Now