<< Click to Display Table of Contents >> Navigation:  Appendix > WinVRS export scripts > WinVRS Ticket export script

Tickets are stored in a table prefixed with the letters TRAN which are then followed by a number (e.g. TRAN1).

 

There can be multiple Ticket tables named TRAN (e.g. TRAN1, TRAN2) so a UNION can be performed to pull all of the data into one result set.

 

The example SELECT statement below will need to be modified according to the number of Tickets table you have. Or, you can simply recall the data one table at a time and create separate export files.

 

Once the data have been retrieved, MDB Admin can save the data as an Excel spreadsheet.

 

Here is an example of the SQL statement that will retrieve the data from four Ticket tables: TRAN1, TRAN2, TRAN3 and TRAN4:

 

SELECT

trannum AS serial_number,

trannum AS ticket_number,

FORMAT(timeout, "YYYY-MM-DD") AS ticket_date_date,

FORMAT(timeout, "YYYY-MM-DD HH:MM:SS") AS ticket_date,

vehid AS truck_id,

'NS' AS vehicle_type,

actid AS customer_id,

actid AS contract_id,

comment AS purchase_order,

mtlid AS item,

mtlid AS material_code,

'1' AS source,

gnid1 AS placed_at,

Clng(grosswt) AS gross,

Clng(tarewt) AS tare,

Clng(gross) - Clng(tare) AS net,

gross AS ticket_gross,

tare AS ticket_tare,

net AS ticket_net,

net / 2000 AS net_3,

1.0 AS conversion_factor_1,

0.0005 AS conversion_factor_2,

'lb' AS ticket_unit

FROM tran1

WHERE void = false

ORDER BY trannum

 

UNION

SELECT

trannum AS serial_number,

trannum AS ticket_number,

FORMAT(timeout, "YYYY-MM-DD") AS ticket_date_date,

FORMAT(timeout, "YYYY-MM-DD HH:MM:SS") AS ticket_date,

vehid AS truck_id,

'NS' AS vehicle_type,

actid AS customer_id,

actid AS contract_id,

comment AS purchase_order,

mtlid AS item,

mtlid AS material_code,

'1' AS source,

gnid1 AS placed_at,

Clng(grosswt) AS gross,

Clng(tarewt) AS tare,

Clng(gross) - Clng(tare) AS net,

gross AS ticket_gross,

tare AS ticket_tare,

net AS ticket_net,

net / 2000 AS net_3,

1.0 AS conversion_factor_1,

0.0005 AS conversion_factor_2,

'lb' AS ticket_unit

FROM tran2

WHERE void = false

 

UNION

SELECT

trannum AS serial_number,

trannum AS ticket_number,

FORMAT(timeout, "YYYY-MM-DD") AS ticket_date_date,

FORMAT(timeout, "YYYY-MM-DD HH:MM:SS") AS ticket_date,

vehid AS truck_id,

'NS' AS vehicle_type,

actid AS customer_id,

actid AS contract_id,

comment AS purchase_order,

mtlid AS item,

mtlid AS material_code,

'1' AS source,

gnid1 AS placed_at,

Clng(grosswt) AS gross,

Clng(tarewt) AS tare,

Clng(gross) - Clng(tare) AS net,

gross AS ticket_gross,

tare AS ticket_tare,

net AS ticket_net,

net / 2000 AS net_3,

1.0 AS conversion_factor_1,

0.0005 AS conversion_factor_2,

'lb' AS ticket_unit

FROM tran3

WHERE void = false

 

UNION

SELECT

trannum AS serial_number,

trannum AS ticket_number,

FORMAT(timeout, "YYYY-MM-DD") AS ticket_date_date,

FORMAT(timeout, "YYYY-MM-DD HH:MM:SS") AS ticket_date,

vehid AS truck_id,

'NS' AS vehicle_type,

actid AS customer_id,

actid AS contract_id,

comment AS purchase_order,

mtlid AS item,

mtlid AS material_code,

'1' AS source,

gnid1 AS placed_at,

Clng(grosswt) AS gross,

Clng(tarewt) AS tare,

Clng(gross) - Clng(tare) AS net,

gross AS ticket_gross,

tare AS ticket_tare,

net AS ticket_net,

net / 2000 AS net_3,

1.0 AS conversion_factor_1,

0.0005 AS conversion_factor_2,

'lb' AS ticket_unit

FROM tran4

WHERE void = false