Pivot

SELECT 'Total Cost' as 'Total Accumulated Cost', [General], [Plumbing], [Gas Piping], [Electric], [Sprinkler], [Mechanical], [Total]
FROM
( SELECT       CASE WHEN ISNUMERIC(dbo.BCHCKBOX.B1_CHECKLIST_COMMENT)= 1 THEN convert(float,isnull(dbo.BCHCKBOX.B1_CHECKLIST_COMMENT,0)) ELSE 0 END AS B1_CHECKLIST_COMMENT, dbo.BCHCKBOX.B1_CHECKBOX_DESC
FROM         dbo.B1PERMIT INNER JOIN
                      dbo.BCHCKBOX ON dbo.B1PERMIT.SERV_PROV_CODE = dbo.BCHCKBOX.SERV_PROV_CODE
                      AND dbo.B1PERMIT.B1_PER_ID1 = dbo.BCHCKBOX.B1_PER_ID1 AND
                      dbo.B1PERMIT.B1_PER_ID2 = dbo.BCHCKBOX.B1_PER_ID2 AND dbo.B1PERMIT.B1_PER_ID3 = dbo.BCHCKBOX.B1_PER_ID3
WHERE     dbo.BCHCKBOX.B1_CHECKBOX_DESC IN ('General', 'Plumbing', 'Gas Piping', 'Electric', 'Sprinkler', 'Mechanical', 'Total')
) AS SourceTable
PIVOT
( AVG(B1_CHECKLIST_COMMENT)
FOR B1_CHECKBOX_DESC IN ([General], [Plumbing], [Gas Piping], [Electric], [Sprinkler], [Mechanical], [Total])
) AS PivotTable

AND OUTPUT

Total Accumulated Cost General Plumbing Gas Piping Electric Sprinkler Mechanical Total

Total Cost 11223.417092511 1516.51906158358 2334.05660377358 3149.06866927593 0 1109.60683012259 11558.0263052544

AND THIS OUTPUTS IT BY ID 

SELECT * --'Total Cost' as 'Total Accumulated Cost', [General], [Plumbing], [Gas Piping], [Electric], [Sprinkler], [Mechanical], [Total]
FROM
( SELECT      b1_alt_id, CASE WHEN ISNUMERIC(dbo.BCHCKBOX.B1_CHECKLIST_COMMENT)= 1 THEN convert(float,isnull(dbo.BCHCKBOX.B1_CHECKLIST_COMMENT,0)) ELSE 0 END AS B1_CHECKLIST_COMMENT, dbo.BCHCKBOX.B1_CHECKBOX_DESC
FROM         dbo.B1PERMIT INNER JOIN
                      dbo.BCHCKBOX ON dbo.B1PERMIT.SERV_PROV_CODE = dbo.BCHCKBOX.SERV_PROV_CODE 
                      AND dbo.B1PERMIT.B1_PER_ID1 = dbo.BCHCKBOX.B1_PER_ID1 AND 
                      dbo.B1PERMIT.B1_PER_ID2 = dbo.BCHCKBOX.B1_PER_ID2 AND dbo.B1PERMIT.B1_PER_ID3 = dbo.BCHCKBOX.B1_PER_ID3
WHERE     dbo.BCHCKBOX.B1_CHECKBOX_DESC IN ('General', 'Plumbing', 'Gas Piping', 'Electric', 'Sprinkler', 'Mechanical', 'Total')
) AS SourceTable
PIVOT
( AVG(B1_CHECKLIST_COMMENT)
FOR B1_CHECKBOX_DESC IN ([General], [Plumbing], [Gas Piping], [Electric], [Sprinkler], [Mechanical], [Total])

) AS PivotTable

Basic Query using Outer Apply

SELECT DISTINCT B1_ALT_ID
, B1_PARCEL_NBR
, ATTRIB_VALUE_1
, ATTRIB_VALUE_2
, ATTRIB_VALUE_3
, CASE
when LEFT(isnull(ATTRIB_VALUE_2,''),1) = ''
then 'No Designation'
when LEFT(isnull(ATTRIB_VALUE_2,''),1) = '1'
then 'Residential'
else 'Commercial'
END AS 'parcel category'
,(CASE
WHEN B1P."B1_PER_TYPE" = '12FAM'
THEN 'RESIDENTIAL'
WHEN B1P."B1_PER_TYPE" = 'Other'
THEN 'COMMERCIAL'
ELSE 'NO DESIGNATION'
END) AS 'Comm or Res by group'
, (convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Total Estimated Costs'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Job Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Work Value'),0))) AS 'Total Estimated Cost'
 
 FROM dbo.B1PERMIT B1P

 OUTER APPLY (SELECT top 1 isnull(B1_PARCEL_NBR,'') AS 'B1_PARCEL_NBR'
FROM "dbo"."B3PARCEL" AS "B3PARCEL"
WHERE "B3PARCEL"."SERV_PROV_CODE"=B1P."SERV_PROV_CODE"
AND "B3PARCEL"."B1_PER_ID1"=B1P."B1_PER_ID1"
AND "B3PARCEL"."B1_PER_ID2"=B1P."B1_PER_ID2"
AND "B3PARCEL"."B1_PER_ID3"=B1P."B1_PER_ID3" ) AS A

 OUTER APPLY (SELECT top 1 isnull(ATTRIB_VALUE_1,'') AS 'ATTRIB_VALUE_1'
, isnull(ATTRIB_VALUE_2,'') AS 'ATTRIB_VALUE_2'
, isnull(ATTRIB_VALUE_3,'') AS 'ATTRIB_VALUE_3'
FROM "dbo"."Parcel_address" AS "Parcel_address"
WHERE "Parcel_address"."L1_PARCEL_NBR"=B1_PARCEL_NBR) AS B

 WHERE  (b1_file_dd >= @BeginDate)
AND (b1_file_dd < DATEADD(day,1,@EndDate))
and isnull(B1_APPL_CLASS, 'na') <> 'INCOMPLETE TMP'
and isnull(B1_APPL_CLASS, 'na') <> 'INCOMPLETE EST'

AND B1P."B1_PER_GROUP" = 'building'
AND b1_per_sub_type != 'health'
AND B1_ALT_ID not like '%vncy%'

AND B1P."REC_STATUS"='A'
--AND B1_PER_SUB_TYPE != 'Temporary Permit'
AND B1P.b1_alt_id not like '%tmp%'
--AND "B1PERMIT".B1_PER_TYPE <> 'C of I'
--AND "B1PERMIT".B1_PER_TYPE <> 'ZBA'
                AND B1P."B1_PER_TYPE"<>'complaint'
AND B1P."B1_APPL_STATUS" != 'Cancelled'
AND (convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Total Estimated Costs'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Job Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Work Value'),0))
) >= @ATWHATVALUE

Accela Javascript Notes

Date Manipulation
var scheduledDate = aa.date.transToJavaUtilDate(new Date(scheduleDateStr));
var scriptScheduledDate = aa.date.getScriptDateTime(scheduledDate);

SSRS 2008 R2 Snippets

FILTERS

?


Expression Date Time in M, d yyyy hh:mm AM/PM format =MonthName(Month(Fields!Most_Recent_Inspection_Date.Value),false)
& " " &
Day(Fields!Most_Recent_Inspection_Date.Value)
& ", " &
Year(Fields!Most_Recent_Inspection_Date.Value)
& " at " &
Switch(
Hour(Fields!Most_Recent_Inspection_Date.Value)=12,Hour(Fields!Most_Recent_Inspection_Date.Value) & ":" & IIF(Minute(Fields!Most_Recent_Inspection_Date.Value)<10,"0" & Minute(Fields!Most_Recent_Inspection_Date.Value) & " PM",Minute(Fields!Most_Recent_Inspection_Date.Value) & " PM"),
Hour(Fields!Most_Recent_Inspection_Date.Value)=0,"12:" & IIF(Minute(Fields!Most_Recent_Inspection_Date.Value)<10,"0" & Minute(Fields!Most_Recent_Inspection_Date.Value) & " AM",Minute(Fields!Most_Recent_Inspection_Date.Value) & " AM"),
Hour(Fields!Most_Recent_Inspection_Date.Value)>12,Hour(Fields!Most_Recent_Inspection_Date.Value)-12 & ":" & IIF(Minute(Fields!Most_Recent_Inspection_Date.Value)<10,"0" & Minute(Fields!Most_Recent_Inspection_Date.Value) & " PM",Minute(Fields!Most_Recent_Inspection_Date.Value) & " PM"),
Hour(Fields!Most_Recent_Inspection_Date.Value)<12,Hour(Fields!Most_Recent_Inspection_Date.Value) & ":" & IIF(Minute(Fields!Most_Recent_Inspection_Date.Value)<10,"0" & Minute(Fields!Most_Recent_Inspection_Date.Value) & " AM",Minute(Fields!Most_Recent_Inspection_Date.Value) & " AM"))


Owner Full Name
COALESCE(CASE
WHEN isnull(dbo.B3OWNERS.B1_OWNER_FNAME, '') + isnull(B3OWNERS.B1_OWNER_MNAME, '') + isnull(B3OWNERS.B1_OWNER_LNAME, '') <> ''
THEN CASE
WHEN isnull(B3OWNERS.B1_OWNER_MNAME, '') <> ''
THEN CASE
WHEN ISNULL(dbo.B3OWNERS.B1_OWNER_TITLE, '') <> ''
THEN dbo.B3OWNERS.B1_OWNER_TITLE
ELSE ''
END + B3OWNERS.B1_OWNER_FNAME + ' ' + B3OWNERS.B1_OWNER_MNAME + ' ' + B3OWNERS.B1_OWNER_LNAME
ELSE B3OWNERS.B1_OWNER_FNAME + ' ' + B3OWNERS.B1_OWNER_LNAME
END
ELSE NULL
END, CASE
WHEN isnull(B3OWNERS.B1_OWNER_FULL_NAME, '') <> ''
THEN B3OWNERS.B1_OWNER_FULL_NAME
ELSE NULL
END) AS 'Owner Full Name'

Contact Full Name
COALESCE(CASE
WHEN isnull(dbo.B3CONTACT.B1_FNAME, '') + isnull(B3CONTACT.B1_MNAME, '') + isnull(B3CONTACT.B1_LNAME, '') <> ''
THEN CASE
WHEN isnull(B3CONTACT.B1_MNAME, '') <> ''
THEN CASE
WHEN ISNULL(dbo.B3CONTACT.B1_TITLE, '') <> ''
THEN dbo.B3CONTACT.B1_TITLE
ELSE ''
END + B3CONTACT.B1_FNAME + ' ' + B3CONTACT.B1_MNAME + ' ' + B3CONTACT.B1_LNAME
ELSE B3CONTACT.B1_FNAME + ' ' + B3CONTACT.B1_LNAME
END
ELSE NULL
END, CASE
WHEN isnull(B3CONTACT.B1_FULL_NAME, '') <> ''
THEN CASE
WHEN ISNULL(dbo.B3CONTACT.B1_TITLE, '') <> ''
THEN dbo.B3CONTACT.B1_TITLE
ELSE ''
END + B3CONTACT.B1_FULL_NAME
ELSE NULL
END, CASE
WHEN ISNULL(dbo.B3CONTACT.B1_BUSINESS_NAME,'') <> '' THEN dbo.B3CONTACT.B1_BUSINESS_NAME ELSE NULL END) AS 'Contact Full Name'

SSRS 2008 R2 Snippets

Today's Date
Inside an Internal Parameter as a Default Value:
=MonthName(Month(Now()),false) & " " & Day(Now()) & ", " & Year(Now())

Format Currency
=Format(10000 - First(Fields!Fee_Total.Value, "HousingDeptWFee"),"C")

My Solution to the Coconuts and Monkey Problem

This applies to the problem located here http://orion.math.iastate.edu/burkardt/puzzles/coconut_solution.html.  I guess I could have made the function recursive but I want to move onto other things.  This was done in java.

public class main {

public static void main(String[] args) {

// TODO Auto-generated method stub

float total = 0;

math_functions mf = new math_functions();

for(float x=20000 ; x>0 ; x--){

total = (mf.coconuts(mf.coconuts(mf.coconuts(mf.coconuts(mf.coconuts(mf.coconuts(x)))))));

if(total%1 == 0 && total > 0){

System.out.println("total left " + total + " starting coconuts " + (x));

//break;

}

}

}

public float coconuts(float x) { float total = (float)(4*((x/5)-.2)); if(total%1 != 0){ return 0; }else{ return total; } }

} 

Javascript Change Date Time to Military Format

No leading 0 for hour, always leading zero for minute on the input time.

function COSconvertto24hourtime(otime,ampm){

var newtime = '';
if (typeof otime != 'undefined') {
var hh = otime.substring(0, otime.indexOf(':'));
var mm = otime.substring(otime.indexOf(':')+1, otime.indexOf(':') + 3);
if(hh == 12){
hh = 0;
}
if (ampm.indexOf('PM') >= 0) {
hh = 12 + parseFloat(hh);
}
if (hh < 10) {
hh = '0' + hh;
}
newtime = '' + hh + ':' + mm + ':00';
} else {
newtime = NULL;
}
return newtime;
}

Handy Powershell Scripts for Moving and Renaming Files

Move Photos
Move Sketches

and this program helped - PowerGUI

Accela Renewal and Expiration Scripts

Annual Renewal Script
Expiration Script (after x number of days)

Handy MsSQL Snippets

Force Items To Top Of Order By
ORDER BY
    CASE
        WHEN guide_item_text like '%condemnation%' then '1' + guide_item_text
ELSE '2' + guide_item_text
    END DESC

Handy Crystal Report Snippets

For Formatting Month Name

if ToText({%Month}) = 'Dec' then 'December'
else if ToText({%Month}) = 'Jan' then 'January'
else if ToText({%Month}) = 'Feb' then 'February'
else if ToText({%Month}) = 'Mar' then 'March'
else if ToText({%Month}) = 'Apr' then 'April'
else if ToText({%Month}) = 'May' then 'May'
else if ToText({%Month}) = 'Jun' then 'June'
else if ToText({%Month}) = 'Jul' then 'July'
else if ToText({%Month}) = 'Aug' then 'August'
else if ToText({%Month}) = 'Sep' then 'September'
else if ToText({%Month}) = 'Oct' then 'October'
else if ToText({%Month}) = 'Nov' then 'November'

Great Links

http://www.ascii.cl/htmlcodes.htm

Accela Add New Event

1. Enable event in AA Admin --- Events --- Events and associate it with the appropriate script
2. Add EMSE activation code to AA Admin --- Standard Choices --- Event Name EMSE to EMSE 
1. ContactAddAfter
2. true ^ branch("CAA:" + appTypeArray[0] + "/*/*/*")
3. true ^ branch("CAA:" + appTypeArray[0] + "/" + appTypeArray[1] + "/*/*")
4. true ^ branch("CAA:" + appTypeArray[0] + "/" + appTypeArray[1] + "/" + appTypeArray[2] + "/*")
3. Add standard Choice catch --- CAA:Enforcement/Vacant Property/NA/NA
1. true ^ showDebug = false, showMessage = false;
2. isTaskActive("Property Remediation") && contactType =="Property Remediation Vendor" ^ updateTask("Property Remediation","Vendor Contacted","Closed by CAA Script","Closed by CAA Script");  updateAppStatus("Remediation","Updated by CAA Script");

Static Variables for Report Drop Down Selections

select top 1 'New' as New
from dbo.B1PERMIT

union

select top 1 'Renewal' as Renewal
from dbo.B1PERMIT

union

select top 1 'Update' as Update
from dbo.B1PERMIT

Accela Functions

ASI
dbo.FN_GET_APP_SPEC_INFO("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",'ASI NAME'')

Parcel Number
dbo.FN_GET_PARCEL_NBR ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3")

Full Address
dbo.FN_GET_PRI_ADDRESS_FULL ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3")

Another Address
dbo.FN_GET_ADDRESS_PARTIAL_ALL ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",'lall','')

Unit Street Address
dbo.FN_GET_ADDRESS_INFO(a.SERV_PROV_CODE, a.B1_PER_ID1, a.B1_PER_ID2, a.B1_PER_ID3,'Y','UNIT_STREETINFO')

Number of Units (parcel)
dbo.FN_GET_PARCEL_NBR_ATTRIBUTE("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",dbo.FN_GET_PARCEL_NBR ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3"),'Living Units')

Property Description
dbo.FN_GET_PARCEL_INFO("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",'Legal Description')

Inspection Result Comment
dbo.FN_GET_INSP_LATEST("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', 'Comment', '', '', '', '', '','Inspection Result Comment')

Latest resulted inspection
dbo.FN_GET_INSP_LATEST("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'', 'Result', '', '', 'RESULTED', '', '','')

Latest Inspector
dbo.FN_GET_INSP_LATEST("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', 'Inspector', '', '', '', '', '', '')

Next Scheduled Inspection
dbo.FN_GET_INSP_LATEST("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'', 'Insp Date', '', '', 'SCHEDULED', '', '','')

Building Commissioner's Name
dbo.FN_GET_STDCHOICE_VALUEDESC('COS','BLD Commissioners Name','BLD Commissioner Name')

Housing Director's Name
,dbo.FN_GET_STDCHOICE_VALUEDESC('COS','HSG Deputy Dir Name','HSG Director Name')

Housing Director's Title
,dbo.FN_GET_STDCHOICE_VALUEDESC('COS','HSG Deputy Dir Name','HSG Director Title')

Owner Address Full
dbo.FN_GET_OWNER_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', 'MFullAddr_Block', '', 'U' )

Contact Address
dbo.FN_GET_CONTACT_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", 'TENANT', '', 'N', 'FullAddr_Line','','U' )

Contact Name
dbo.FN_GET_CONTACT_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", 'TENANT', '', 'N', 'FullName','','U' )

Licensed Professional Name
dbo.FN_GET_LICPROF_INFO("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",'FULLNAME', 'FML','','PLUMBING')

Licensed Professional Full Address
dbo.FN_GET_LICPROF_INFO("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3",'FULLADDR_BLOCK', 'FML','','PLUMBING')

contact or owner address
case
when isnull(dbo.[FN_GET_CONTACT_INFO]("B1PERMIT"."SERV_PROV_CODE","B1PERMIT"."B1_PER_ID1","B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'Contact', '', '', 'FullAddr_Block', '', ''),'') = '' then dbo.FN_GET_OWNER_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', 'MFullAddr_Block', '', 'U' )
else
dbo.[FN_GET_CONTACT_INFO]("B1PERMIT"."SERV_PROV_CODE","B1PERMIT"."B1_PER_ID1","B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'Contact', '', '', 'FullAddr_Block', '', '')
end

contact or owner
case
when isnull(dbo.[FN_GET_CONTACT_INFO]("B1PERMIT"."SERV_PROV_CODE","B1PERMIT"."B1_PER_ID1","B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'Contact', '', '', 'FullName', '', ''),'') = ' ' then dbo.FN_GET_OWNER_INFO ("B1PERMIT"."SERV_PROV_CODE", "B1PERMIT"."B1_PER_ID1", "B1PERMIT"."B1_PER_ID2", "B1PERMIT"."B1_PER_ID3", '', '', '', 'U' )
else dbo.[FN_GET_CONTACT_INFO]("B1PERMIT"."SERV_PROV_CODE","B1PERMIT"."B1_PER_ID1","B1PERMIT"."B1_PER_ID2","B1PERMIT"."B1_PER_ID3",
'Contact', '', '', 'FullName', '', '')
end

Accela Contact Name and Full Multiline Address

COALESCE(CASE WHEN isnull(C.B1_FNAME,'') + isnull(C.B1_MNAME,'') + isnull(C.B1_LNAME,'') <> '' THEN CASE WHEN isnull(C.B1_MNAME,'') <> '' THEN C.B1_FNAME + ' ' + C.B1_MNAME + ' ' + C.B1_LNAME ELSE C.B1_FNAME + ' ' + C.B1_LNAME END ELSE NULL END
,CASE WHEN isnull(C.B1_FULL_NAME,'') <> '' THEN C.B1_FULL_NAME ELSE NULL END
,CASE WHEN isnull(C.B1_BUSINESS_NAME,'') <> '' THEN C.B1_BUSINESS_NAME ELSE NULL END) AS 'Tenant Name'

,CASE WHEN isnull(C.B1_ADDRESS1,'') <> '' THEN C.B1_ADDRESS1 + char(13)+char(10) ELSE '' END + 
 CASE WHEN isnull(C.B1_ADDRESS2,'') <> '' THEN C.B1_ADDRESS2 + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull(C.B1_ADDRESS3,'') <> '' THEN C.B1_ADDRESS3 + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull(C.B1_CITY,'') <> '' THEN C.B1_CITY + ', ' ELSE '' END +
 CASE WHEN isnull(C.B1_STATE,'') <> '' THEN C.B1_STATE + ' ' ELSE '' END +
 CASE WHEN isnull(C.B1_ZIP,'') <> '' THEN C.B1_ZIP ELSE '' END
AS 'Full Tenant Address'

OWNER NAME (Crystal Format)
COALESCE(CASE WHEN isnull("B3OWNERS"."B1_OWNER_FNAME",'') + isnull("B3OWNERS"."B1_OWNER_MNAME",'') + isnull("B3OWNERS"."B1_OWNER_LNAME",'') <> '' THEN CASE WHEN isnull("B3OWNERS"."B1_OWNER_MNAME",'') <> '' THEN "B3OWNERS"."B1_OWNER_FNAME" + ' ' + "B3OWNERS"."B1_OWNER_MNAME" + ' ' + "B3OWNERS"."B1_OWNER_LNAME" ELSE "B3OWNERS"."B1_OWNER_FNAME" + ' ' + "B3OWNERS"."B1_OWNER_LNAME" END ELSE NULL END
 ,CASE WHEN isnull("B3OWNERS"."B1_OWNER_FULL_NAME",'') <> '' THEN "B3OWNERS"."B1_OWNER_FULL_NAME" ELSE NULL END

 ,CASE WHEN isnull("B3OWNERS"."B1_OWNER_TITLE",'') <> '' THEN "B3OWNERS"."B1_OWNER_TITLE" ELSE NULL END)

OWNER ADDRESS (Crystal Format)
CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS2",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS2" + char(13)+char(10) ELSE '' END + 
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS3",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS3" + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ADDRESS1",'') <> '' THEN "B3OWNERS"."B1_MAIL_ADDRESS1" + char(13)+char(10) ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_CITY",'') <> '' THEN "B3OWNERS"."B1_MAIL_CITY" + ', ' ELSE '' END +
 CASE WHEN isnull("B3OWNERS"."B1_MAIL_STATE",'') <> '' THEN "B3OWNERS"."B1_MAIL_STATE" + ' ' ELSE '' END +

 CASE WHEN isnull("B3OWNERS"."B1_MAIL_ZIP",'') <> '' THEN "B3OWNERS"."B1_MAIL_ZIP" ELSE '' END

Reset Mysql Permissions

C:\mysql\bin\mysqld-nt --init-file=C:\\mysql-init.txt

UPDATE mysql.user SET Password=PASSWORD('city11') WHERE User='root';
FLUSH PRIVILEGES;