Solved

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

Posted on 2008-10-06
6
204 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

628 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