Historical data extract from Old version of supportworks

Status:	Published
Version:	1.0
Authors:	HTL QA
Applies to:	any Supportworks ESP Version

If you have upgraded Supportworks form a previous version on a separate server and would like a spreadsheet of all the historical data for the tickets that were raised in a previous version of Supportworks, you could set up a data look-up instead of a comma separated spreadsheet of historical calls as it is better structured and can be easily configured for you to access from your current Supportworks instance ( eg from a v8 instance)

Please contact us on [email protected] to discuss this further.

Alternatively, you can use the database to export the information.

The SQL below will do this via Interactive SQL on an out-of-the-box Supportworks installation. There is also a way of doing it in MS SQL but will not be explained here:

SELECT
	swdata.opencall.*
	, swdata.updatedb.*
INTO OUTFILE 'D:/Hornbill/XYZ.csv'
FIELDS ENCLOSED BY '"'
	TERMINATED BY ','
LINES TERMINATED BY '\r\n'
FROM
	swdata.opencall
    , swdata.updatedb
WHERE
	swdata.opencall.callref = swdata.updatedb.callref
ORDER BY
	swdata.opencall.callref, swdata.updatedb.udindex;

Please note that the above does NOT provide you with any field headers. IF you want to limit the data to specific fields you are interested in (which I highly recommend) you would need to specify those fields individually (see sample below). Please note that headers will still not be in the .csv file, BUT you will know which field is which as the order you SELECT-ed will be maintained.

SELECT
	swdata.opencall.callref
	, swdata.opencall.priority
	, swdata.opencall.logdatex
	, swdata.opencall.suppgroup
	, swdata.opencall.closedatex
	, swdata.updatedb.udindex
	, swdata.updatedb.aaid
	, swdata.updatedb.updatetimex
INTO ...

Also, important to mention, one can amend the WHERE-clause to limit to the calls you might be interested in.