Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-06
6
Medium Priority
?
212 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 500 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 93

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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 possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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