I'm no expert, but try this
substring(convert(char(256),CMServiceEventDetail.Comments),0,255),
substring(@NewLine + SPACE(53) +convert(char(256),
CMServiceEventDetail.Comments),256,511),
substring(@NewLine + SPACE(53) +convert(char(256),
CMServiceEventDetail.Comments),512,767),
substring(@NewLine + SPACE(53) +convert(char(256),
CMServiceEventDetail.Comments),768,1023)
On Thu, Jul 30, 2015 at 8:31 AM, David McSpadden <[email protected]> wrote:
> Can someone direct me or help me?
>
> I need the following query to break the Comments field into 4 pieces and
> then put them one below each other:
>
>
>
> set nocount on
>
> declare @NewLine char(55) = CHAR(13) + CHAR(10)
>
> Print 'Change # Submitted Date Completed Date Type of Change Assigned to
> Description of Change'
>
> Print '________ ______________ ______________ ______________ ___________
> _____________________'
>
> Print ' '
>
> select convert(char(8),CMServiceEventMain.MessageID),
>
> convert(char(10),CMServiceEventMain.DateAdded,101),
>
> convert(char(10),CMServiceEventDetail.DateClosed,101),
>
> convert(char(15),ServiceItem_DetailEntry_ValueLists.
> DetailEntryListValue),
>
> convert(char(4),CMServiceEventMain.OfficerAdded),
>
> substring(convert(char(256),CMServiceEventDetail.Comments),0,255),
> @NewLine + SPACE(53) +
>
> substring(convert(char(256),CMServiceEventDetail.Comments),256,
> 511), @NewLine + SPACE(53) +
>
> substring(convert(char(256),CMServiceEventDetail.Comments),512,
> 767), @NewLine + SPACE(53) +
>
> substring(convert(char(256),CMServiceEventDetail.Comments),768,
> 1023)
>
>
>
> from CMServiceEventDetail, ServiceItem_DetailEntry,
> ServiceItem_DetailEntry_ValueLists, ServiceEvent_UDValues,
> CMServiceEventMain
>
> where OfficerAssignedTo='Q_ChangeControl'
>
> and CMServiceEventDetail.MessageID=ServiceEvent_UDValues.EventID
>
> and CMServiceEventMain.MessageId=ServiceEvent_UDValues.EventID
>
> and ServiceEvent_UDValues.UDItemEntryID=ServiceItem_DetailEntry.
> DetailEntryID
>
> and ServiceEvent_UDValues.Value=ServiceItem_DetailEntry_ValueLists.
> DetailEntryListValueID
>
> Order by CMServiceEventMain.MessageID
>
>
>
> So we should have:
>
> Change # Submitted Date Completed Date Type of Change Assigned to
> Description of Change
>
> ________ ______________ ______________ ______________ ___________
> _____________________
>
> 1 xx/xx/xxxx yy/yy/yyyy software me comment1
>
> Comment2
>
> 2 xx/xx/xxxx yy/yy/yyyy software me comment1
>
> Comment2
>
> Comment3
>
>
>
> Etc..
>
> What am I doing wrong with the newline that all the comment2 through 4 are
> left justified and appear in the Change # area (Or it looks that way.)
>
> My space(53) is not padding the line to push the comments up under each
> other.
>
>
>
>
>
> *David McSpadden*
>
> Systems Administrator
>
> Indiana Members Credit Union
>
> P: 317.554.8190 | F: 317.554.8106
>
> [image: Description: imcu email icon] <http://imcu.com/> [image:
> Description: facebook email icon]
> <https://www.facebook.com/IndianaMembersCU> [image: Description: twitter
> email icon] <https://twitter.com/IndMembersCU>
>
>
>
> [image: Description: email logo]
>
> [image: mcp2]
>
>
>
> This e-mail and any files transmitted with it are property of Indiana
> Members Credit Union, are confidential, and are intended solely for the use
> of the individual or entity to whom this e-mail is addressed. If you are
> not one of the named recipient(s) or otherwise have reason to believe that
> you have received this message in error, please notify the sender and
> delete this message immediately from your computer. Any other use,
> retention, dissemination, forwarding, printing, or copying of this email is
> strictly prohibited.
>
> Please consider the environment before printing this email.
>
