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
[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.
