MSSQL query sum value display in ui_text

[sahsha]

Need help to display only numeric value. I'm getting value with brackets/special characters [{" ": value }].

Can anyone please help.

flow given below:

[{"id":"ff3a0683.417658","type":"MSSQL","z":"6741e784.e28a18","mssqlCN":"7aaf39f5.525778","name":"","query":"SELECT SUM([OK PRODUCTION] ) FROM dbo.SAP_SCADA_12 WHERE DATE BETWEEN '2021-04-01'AND '2021-04-30' AND [MACHINE CODE]='M06CN119'","outField":"payload","x":400,"y":120,"wires":[["797268d5.514638","9d52565b.d095d8"]]},{"id":"8010ce5d.29058","type":"inject","z":"6741e784.e28a18","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":230,"y":120,"wires":[["ff3a0683.417658"]]},{"id":"9d52565b.d095d8","type":"debug","z":"6741e784.e28a18","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":590,"y":140,"wires":[]},{"id":"797268d5.514638","type":"ui_text","z":"6741e784.e28a18","group":"b0f97496.a4d498","order":7,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":570,"y":100,"wires":[]},{"id":"7aaf39f5.525778","type":"MSSQL-CN","tdsVersion":"7_3_B","name":"MT06","server":"192.168.26.172","port":"1433","encyption":false,"database":"MT06","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"},{"id":"b0f97496.a4d498","type":"ui_group","name":"testing","tab":"152250d2.cae22f","order":1,"disp":true,"width":24,"collapse":false},{"id":"152250d2.cae22f","type":"ui_tab","name":"Testing","icon":"dashboard","order":14,"disabled":false,"hidden":false}]

[sahsha]

solved it myself

[TotallyInformation]

Please share so that others can benefit as well. Thanks.

[sahsha]

@TotallyInformation what I figured by going through numerous forums was to use AS command in SQL query.

then I added a function node.

check flow given below:

[{"id":"3ef514cb.fef16c","type":"MSSQL","z":"6741e784.e28a18","g":"19628c76.b466d4","mssqlCN":"898d2a6f.9fb7b8","name":"MACHINE SHIFT TIME (X)(Min.)","query":"SELECT \nSUM([MACHINE SHIFT TIME (X)(Min.)]) \nAS runtime\nFROM dbo.SHIFT_DATA \nWHERE \ndatepart(mm,DATE) =month(getdate())   \nAND datepart(yyyy,DATE) =year(getdate()) \nAND [MACHINE CODE]='M02CN001'","outField":"payload","x":480,"y":300,"wires":[["42e425ef.16fa0c"]]},{"id":"2355426d.56cbee","type":"MSSQL","z":"6741e784.e28a18","g":"19628c76.b466d4","mssqlCN":"898d2a6f.9fb7b8","name":"AVAILABILITY LOSS TIME(Min.)","query":"SELECT \nSUM([AVAILABILITY LOSS TIME(Min.)]) \nAS avalosstime \nFROM dbo.SHIFT_DATA \nWHERE \ndatepart(mm,DATE) =month(getdate())   \nAND datepart(yyyy,DATE) =year(getdate()) \nAND [MACHINE CODE]='M02CN001'","outField":"payload","x":480,"y":340,"wires":[["601fe5cb.24124c"]]},{"id":"432295a9.4b8f1c","type":"debug","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":870,"y":260,"wires":[]},{"id":"b6b56e3c.13f4c","type":"debug","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":870,"y":360,"wires":[]},{"id":"54cafb53.6f3a44","type":"debug","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1030,"y":260,"wires":[]},{"id":"44ec235d.acc4fc","type":"debug","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","active":true,"tosidebar":false,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1170,"y":360,"wires":[]},{"id":"42e425ef.16fa0c","type":"function","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","func":"msg.payload = msg.payload[0].runtime;\nmsg.payload = Number(msg.payload);\nmsg.topic='runtime';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":700,"y":300,"wires":[["432295a9.4b8f1c","a799de6e.8fd11"]]},{"id":"601fe5cb.24124c","type":"function","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","func":"msg.payload = msg.payload[0].avalosstime;\nmsg.payload = Number(msg.payload);\nmsg.topic='avalosstime';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":700,"y":340,"wires":[["b6b56e3c.13f4c","a799de6e.8fd11"]]},{"id":"a799de6e.8fd11","type":"join","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":850,"y":320,"wires":[["54cafb53.6f3a44","592118bc.a2f798"]]},{"id":"592118bc.a2f798","type":"function","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"","func":"msg.payload = ((msg.payload.runtime - msg.payload.avalosstime)/msg.payload.runtime)*100;\nmsg.payload=(msg.payload).toFixed(0);\nmsg.topic='availability';\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1000,"y":320,"wires":[["44ec235d.acc4fc","dfe7a836.499c28"]]},{"id":"667f118b.bf02f","type":"comment","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"Availability CN001","info":"","x":350,"y":260,"wires":[]},{"id":"dfe7a836.499c28","type":"link out","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"sql A output CN001","links":["cc29ba1e.dff0d8","1ed3423d.8ddb1e","2cbdde5.80a4b22","92e30c3.40a9df","7afe71ee.825d4"],"x":1115,"y":320,"wires":[]},{"id":"31d9dfdc.7720d","type":"link in","z":"6741e784.e28a18","g":"19628c76.b466d4","name":"sql A input CN001","links":["8e80bcc7.0ccfc"],"x":275,"y":320,"wires":[["3ef514cb.fef16c","2355426d.56cbee"]]},{"id":"898d2a6f.9fb7b8","type":"MSSQL-CN","tdsVersion":"7_3_B","name":"MT02","server":"192.168.21.169\\SCADASQL","port":"1433","encyption":false,"database":"MT02","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"}]