need some sql, willing to invest

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

need some sql, willing to invest

Author

Topic

Shavad
Starting Member

1 Post

Posted - 2012-03-26 : 19:40:04

working with a dotnetnuke module that builds reports based on sql.

i have the basic report/table working but need some advanced tactics on the last few requirements. previous developer failed me so i am in a pinch to get this today.

two parts

1. for the concatinated name and address field i need to build out the address fields without any blank spaces when one of the fields is empty.

2. for the amount fields, i need to chech and see if a coresponding account zero column is false and if it is i need to hidethe label and the amount.

i can give more if interested. please email me at purchases@myfathersoffice.com

==============================
Select

[Source],

'<center><A href="' + 'http://www.LainFaulkner.com/Templates/Document/' + [FileName] + '.pdf' + '">' + CAST([POCNum] AS varchar(5)) + '</A></ center>' AS 'Claim No. - Doc Link',

CONVERT(date, [POC_Date], 1) AS 'Date Filed',

'' + [CMailing_name1] + ' ' +

[CMailing_name2] + ' ' +
[CMailing_name3] + ' ' +
[CMailing_address1] + ' ' +
[CMailing_address2] + ' ' +
[CMailing_address3] + ' ' +
[CCity] + CHAR(13) + ' ' +
[CState] + ', ' +
CAST([CZip] AS varchar(10)) + ' ' +
[Catten]AS 'Name and Address',

'<table width="100%"><tr><td> Secured Amount: </td><td align="right"> $' + CONVERT(varchar(20), CAST([Secured_Amt] AS money) , 1) + '</td></tr>' +

'<tr><td>Priority Amount: </td><td align="right">$' + CONVERT(varchar(20), CAST([Priority_Amt] AS money) , 1) + '</td></tr>' +

'<tr><td>Admin Amount: </td><td align="right"> $' + CONVERT(varchar(20), CAST([Admin_Amt] AS money) , 1) + '</td></tr>' +

'<tr><td>Unsecured Amount: </td><td align="right"> $' + CONVERT(varchar(20), CAST( [Unsecured_Amt] AS money) , 1) + '</td></tr>' +

'<tr><td colspan="2">__________________________________________ </td></tr>' +

'<tr><td> TOTAL Amount: </td><td align="right">$' + CONVERT(varchar(20), CAST( [Total_Amt] AS money) , 1) + '</td></tr></table>' AS 'Claim Amount'

FROM LFReg
=========================

other fields in the db

Total_Amt,
Total_Zero_Amt,
Secured_Amt,
Secured_Zero_Amt,
Priority_Amt,
Priority_Zero_Amt,
Admin_Amt,
Admin_Zero_Amt,

Shane Adam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 12:21:48

1.use COALESCE or ISNULL to handle NULL and make it blank instead
something like


...
COALESCE([CMailing_name2] + '<br />','') +
COALESCE([CMailing_name3] + '<br />','') + 
COALESCE([CMailing_address1] + '<br />','') + 
...

2. use case when for check like
CASE WHEN [Priority_Amt]>0 THEN '<tr><td>Priority Amount: </td><td align="right">$' + CONVERT(varchar(20), CAST([Priority_Amt] AS money) , 1) + '</td></tr>' ELSE '' END +

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources