Sum Columns and Convert to Rows in MSSQL 2005

Hey all,

I have set up a transaction that runs once per day and collects downtimes and places each downtime into a Column. Every row in the table is a days worth of times, which is what I wanted to do. Here is what the table looks like:


Now I want to make a downtime report but the charts on the report don’t seem to like this table format, I can sum all of the columns but I’m having a hard time getting it to display correctly in the report. How do I sum the columns and then turn them into rows? so instead of:

E-stop 1 E-stop 2 E-stop 3 E-stop 4 etc…
1 2 1 4
2 3 5 2

I would like:

E-stop 1 3
E-stop 2 5
E-stop 3 6
E-stop 4 6

I’m no SQL guru and I can’t figure out how to accomplish this task. Any help or tips would be appreciated, thanks!

This isn’t well supported in SQL, but you can write a simple script to do this for you. Something like:

rawData = system.db.runQuery("SELECT sum(Minutes_Estop__1_Active), sum(Minutes_Estop__2_Active), sum(Minutes_Estop__3_Active), ...") header = ["E-stop", "sum"] newData = [] newData.append([ "E-stop 1", rawData[0][0] ]) newData.append([ "E-stop 2", rawData[0][1] ]) newData.append([ "E-stop 3", rawData[0][2] ]) finishedData = system.dataset.toPyDataSet(header, newData)

Cool, thank you!

I put that code into the property change event handler on a date selector but now I’m getting some kind of date format error.

With this code:

start = event.source.date stop = event.source.parent.getComponent('FlowStop').date rawData = system.db.runQuery("SELECT SUM(Minutes_Estop__10_Active), SUM(Minutes_Estop__11_Active),SUM(Minutes_Estop__1_Active), SUM(Minutes_Estop__2_Active),SUM(Minutes_Estop__3_Active), SUM(Minutes_Estop__4_Active),SUM(Minutes_Estop__5_Active), SUM(Minutes_Estop__6_Active),SUM(Minutes_Estop__7_Active), SUM(Minutes_Estop__8_Active),SUM(Minutes_Estop__9_Active), SUM(Minutes_Hydraulic_Timeout_Active),SUM(Minutes_Landing_Conveyor_Down), SUM(Minutes_Left_Loin_Puller_Down),SUM(Minutes_Right_Loin_Puller_Down) FROM Cutfloor_EandD WHERE t_stamp >= '" + start + "' and t_stamp <= '" + stop + "'")

I get this error:

Traceback (innermost last):

File “event:propertyChange”, line 3, in ?

TypeError: add nor radd defined for these operands

Ignition v7.2.8 (b178)
Java: Sun Microsystems Inc. 1.6.0_24

I know this is somekind of typecase error, so I tried making the start date a string by doing str(start), but then I get a different error:

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Gateway Error 500: Conversion failed when converting datetime from character string.

So now I’m stuck again… I can’t figure out how to pass the date from a date selection into the SQL query. :frowning:

It’s saying that you cannot add a string to a date. Try using python to stuff the date into the string like this:

start = event.source.date stop = event.source.parent.getComponent('FlowStop').date rawData = system.db.runQuery("SELECT SUM(Minutes_Estop__10_Active), SUM(Minutes_Estop__11_Active),SUM(Minutes_Estop__1_Active), SUM(Minutes_Estop__2_Active),SUM(Minutes_Estop__3_Active), SUM(Minutes_Estop__4_Active),SUM(Minutes_Estop__5_Active), SUM(Minutes_Estop__6_Active),SUM(Minutes_Estop__7_Active), SUM(Minutes_Estop__8_Active),SUM(Minutes_Estop__9_Active), SUM(Minutes_Hydraulic_Timeout_Active),SUM(Minutes_Landing_Conveyor_Down), SUM(Minutes_Left_Loin_Puller_Down),SUM(Minutes_Right_Loin_Puller_Down) FROM Cutfloor_EandD WHERE t_stamp >= '%s' and t_stamp <= '%s'" %(start, stop))

Tried that, same thing. It looks like it’s actually a MSSQL problem. For some reason it doesn’t like the datetime coming from the date property:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting datetime from character string.

This is what comes up in the error dialog box for the where clause:

WHERE t_stamp >= ‘Sun Aug 28 08:49:38 EDT 2011’ and t_stamp <= ‘Wed Aug 31 08:49:38 EDT 2011’, IgnitionDB, )

Doesn’t look any different to me than using that propery binding in a SQL query…

Yeah, the problem is that you get a format like ‘Sun Aug 28 08:49:38 EDT 2011’ out of your start and end date components. You can convert it to this format before putting it into the SQL query: ‘2011-08-28 08:49:38’. The easiest way to do this is to create a dynamic property (string) and use the dateFormat() expression function.

That worked, thanks!