I think it is the data in the comments field This looks good. Still not working though. Results: Change # Submitted Date Completed Date Type of Change Assigned to Description of Change ________ ______________ ______________ ______________ ___________ _____________________ EV40022 07/13/2015 Hardware 2521 ***Added by: David McSpadden - 2521 -- 7404 On: 7/13/2015 10:01:58 AM*** Last Test
***Added by: Brad Herbert - 2552 -- 7404 On: 7/13/2015 9:59:18 AM*** Test ***Added by: Brad Herbert - 2552 -- 7404 On: 7/13/2015 9:57:26 AM*** Test again... From: [email protected] [mailto:[email protected]] On Behalf Of Kevin Lundy Sent: Thursday, July 30, 2015 8:45 AM To: [email protected] Subject: Re: [NTSysADM] SQL question 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]<mailto:[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<tel:317.554.8190> | F: 317.554.8106<tel:317.554.8106> [Description: imcu email icon]<http://imcu.com/> [Description: facebook email icon] <https://www.facebook.com/IndianaMembersCU> [Description: twitter email icon] <https://twitter.com/IndMembersCU> [Description: email logo] [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. 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.
