Appendix > SQL snipits

Find duplicates rows

SELECT

customer_id, COUNT(customer_id),

contract_id, COUNT(contract_id)

FROM

dsptch30_contract

GROUP BY

customer_id, contract_id

HAVING

COUNT(customer_id) > 1 AND COUNT(contract_id) > 1

SELECT

ticket_number, COUNT(ticket_number)

FROM

shipment_ticket

GROUP BY

ticket_number

HAVING

COUNT(ticket_number) > 1

SELECT t2.customer_id, t1.contract_id, item, COUNT(*) AS duplicate_count

FROM dsptch30_contract_item t1

LEFT JOIN dsptch30_contract t2 ON t2.row_id = t1.contract_row_id

WHERE t2.customer_id IS NOT NULL

GROUP BY t2.customer_id, t1.contract_id, item

HAVING COUNT(*) > 1;

Test Date/Time macros

SELECT

{ fn CURDATE() } AS "CURDATE()",

{ fn CURRENT_DATE() } AS "CURRENT_DATE()",

{ fn CURRENT_TIME(6) } AS "CURRENT_TIME()",

{ fn CURRENT_TIMESTAMP() } AS "CURRENT_TIMESTAMP()",

{ fn CURTIME() } AS "CURTIME()",

{ fn DAYNAME({ fn CURRENT_DATE() }) } AS "DAYNAME()",

{ fn DAYOFMONTH({ fn CURRENT_DATE() }) } AS "DAYOFMONTH()",

{ fn DAYOFYEAR({ fn CURRENT_DATE() }) } AS "DAYOFYEAR()",

{ fn HOUR({ fn CONVERT({ fn CURRENT_TIME() }, TIME) }) } AS "HOUR()",

{ fn MINUTE({ fn CONVERT({ fn CURRENT_TIME() }, TIME) }) } AS "MINUTE()",

{ fn MONTH({ fn CURRENT_DATE() }) } AS "MONTH()",

{ fn MONTHNAME({ fn CURRENT_DATE() }) } AS "MONTHNAME()",

{ fn NOW() } AS "NOW()",

{ fn QUARTER({ fn CURRENT_DATE() }) } AS "QUARTER()",

{ fn SECOND({ fn CONVERT({ fn CURRENT_TIME() }, TIME) }) } AS "SECOND()",

{ fn WEEK({ fn CURRENT_DATE() }) } AS "WEEK()",

{ fn YEAR({ fn CURRENT_DATE() }) } AS "YEAR()",

{ fn TIMESTAMPADD(SECOND, 120, { fn CURRENT_TIMESTAMP() }) } AS "TIMESTAMPADD()",

{ fn TIMESTAMPDIFF(SECOND, { fn CURRENT_TIMESTAMP() }, { fn TIMESTAMPADD(SECOND, 120, { fn CURRENT_TIMESTAMP() }) }) } AS "TIMESTAMPDIFF()"

Update Order Item pricing

UPDATE dsptch30_contract_item SET unit_price = (SELECT unit_price FROM dsptch30_material WHERE dsptch30_material.row_id = material_row_id)

Delete incomplete Retail tickets

DELETE FROM "&table_name_item" WHERE parent_serial_number IN (SELECT serial_number FROM "&table_name" WHERE parent_serial_number = -1);

DELETE FROM "&table_name" WHERE parent_serial_number = -1

Delete unused Materials

DELETE FROM dsptch30_material WHERE material_code NOT IN (SELECT DISTINCT material_code FORM "&table_name" WHERE material_code IS NOT NULL ORDER BY material_code)

PostgreSQL: Create user

CREATE ROLE jeffw with LOGIN PASSWORD 'm8w1z4';

GRANT ALL ON ALL TABLES IN SCHEMA PUBLIC TO jeffw;

Clear taxes

UPDATE dsptch30_customer SET delivery_taxes_payable = NULL, material_taxes_payable = NULL;

UPDATE dsptch30_contract SET delivery_taxes_payable = NULL, material_taxes_payable = NULL;

UPDATE dsptch30_contract_item SET taxes_payable = NULL;

UPDATE dsptch30_material SET taxes_payable = NULL;

UPDATE dsptch30_haul_rate SET taxes_payable = NULL;

UPDATE dsptch30_zone SET taxes_payable = NULL;

PostgreSQL: Cleanup database after importing Tickets

-- This gets rid of all the rows that are created by triggers after Tickets have been imported.

DELETE FROM dsptch30_customer;

DELETE FROM dsptch30_material;

DELETE FROM dsptch30_vehicle_type;

DELETE FROM dsptch30_zone;

DELETE FROM dsptch30_truck;

DELETE FROM dsptch30_contract;

DELETE FROM dsptch30_contract_item;

DELETE FROM dsptch30_haul_rate;

DELETE FROM dsptch30_placed_at;

DELETE FROM dsptch30_station;

-- reset autoincrement values after doing and import

SELECT setval('dsptch30_carrier_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_carrier) + 1);

SELECT setval('dsptch30_company_info_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_company_info) + 1);

SELECT setval('dsptch30_destination_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_destination) + 1);

SELECT setval('dsptch30_measurement_unit_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_measurement_unit) + 1);

SELECT setval('dsptch30_source_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_source) + 1);

SELECT setval('dsptch30_tax_rate_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_tax_rate) + 1);

SELECT setval('dsptch30_ticket_number_printer_row_id_seq',(SELECT MAX(row_id) FROM dsptch30_ticket_number_printer) + 1);

SELECT setval('dsptch30_truck_row_id_seq',(SELECT MAX(row_id) FROM dsptch30_truck) + 1);

SELECT setval('dsptch30_material_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_material) + 1);

SELECT setval('dsptch30_vehicle_type_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_vehicle_type) + 1);

SELECT setval('dsptch30_zone_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_zone) + 1);

SELECT setval('dsptch30_customer_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_customer) + 1);

SELECT setval('dsptch30_contract_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_contract) + 1);

SELECT setval('dsptch30_contract_item_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_contract_item) + 1);

SELECT setval('dsptch30_haul_rate_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_haul_rate) + 1);

SELECT setval('dsptch30_placed_at_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_placed_at) + 1);

SELECT setval('dsptch30_station_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_station) + 1);

SELECT setval('shipment_ticket_serial_number_seq', (SELECT MAX(serial_number) FROM shipment_ticket) + 1);

SELECT setval('receipt_ticket_serial_number_seq', (SELECT MAX(serial_number) FROM receipt_ticket) + 1);

SELECT setval('dsptch30_ticket_cash_sale_row_id_seq', (SELECT MAX(row_id) FROM dsptch30_ticket_cash_sale) + 1);

UPDATE dsptch30_ticket_number SET next_ticket_number = (SELECT COALESCE(MAX(ticket_number) + 1, 1) FROM receipt_ticket) WHERE table_name = 'receipt_ticket';

UPDATE dsptch30_ticket_number SET next_ticket_number = (SELECT COALESCE(MAX(ticket_number) + 1, 1) FROM shipment_ticket) WHERE table_name = 'shipment_ticket';

Update the overload column

UPDATE shipment_ticket

SET overload = (

SELECT iif( (ifnull(agw, 0) = 0 AND

ifnull(agw, 0) = 0), 0, iif(rgw > agw, agw, rgw) ) < gross

FROM dsptch30_truck t1

WHERE t1.truck_id = truck_id

)

WHERE truck_id IS NOT NULL;

PostgreSQL: Replace \r\n with correct control code

UPDATE dsptch30_contract set description = regexp_replace(description, '\\r\\n', chr(10), 'g')

PostgreSQL: ticket_date as seconds

CAST(EXTRACT(EPOCH FROM "t1"."ticket_date") AS INTEGER)

Average net weight by truck

SELECT

truck_id,

AVG(net),

-- clean up AVG so we don't get a resul like 16482.5

(CAST(AVG(net) AS INTEGER) + 5) / 10 * 10 "Average",

CAST(AVG(net) AS INTEGER) "Step 1: Average AS INTEGER",

CAST(AVG(net) AS INTEGER) + 5 "Step 2: + 5",

(CAST(AVG(net) AS INTEGER) + 5) / 10 "Step 3: / 10",

(CAST(AVG(net) AS INTEGER) + 5) / 10 * 10 "Step 4: * 10"

FROM "&table_name" t1

&where_clause

GROUP BY truck_id

ORDER BY truck_id

Unique Zones

SELECT DISTINCT DistinctZone_.zone

FROM

(

SELECT DISTINCT "zone" FROM {IF ASA}"dba".{FI}{IF MSSQL}"dba".{FI}dsptch30_zone

UNION SELECT DISTINCT "zone" FROM {IF ASA}"dba".{FI}{IF MSSQL}"dba".{FI}dsptch30_haul_rate

) DistinctZone_

ORDER BY "zone"

Update Order Item pricing

UPDATE dsptch30_contract_item SET (unit_price, taxes_payable, additional_fee, minimum_charge) = (SELECT unit_price, taxes_payable, additional_fee, minimum_charge FROM dsptch30_material WHERE dsptch30_material.material_code = dsptch30_contract_item.item)

Set the next ticket number for each table

CREATE OR REPLACE FUNCTION set_next_ticket_number () RETURNS VOID

STRICT

LANGUAGE PLPGSQL

$plpgsql$

DECLARE

_ticket_table RECORD;

BEGIN

FOR _ticket_table IN SELECT "table_name" FROM dsptch30_ticket_number

LOOP

EXECUTE format('UPDATE dsptch30_ticket_number SET next_ticket_number = (SELECT COALESCE(MAX(ticket_number) + 1, 1) FROM %I) WHERE table_name = $1', _ticket_table.table_name) USING _ticket_table.table_name;

END LOOP;

END;

$plpgsql$;