JamesBell
asked on
How do I use a startdate & enddate field to create multiple records with incrementing date?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
>>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.
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.
ASKER
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!
ASKER
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
Thanks to all for your help.
Regards
James
ASKER
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?