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

The idea here is that you have recreated your database by importing all of your Tickets.

When you did that, triggers automatically created a whole bunch of data that you may no longer want.

For example, it's 2026 and you have carefully gone in and deleted all the Trucks not used since 2024. However, you imported all of your Tickets from 2021 and every single Truck that you used in 2021 will be created once again. This happens for Customers, Orders, Order Items, Materials, Zones...

These commands will clean up all that data.

-- 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)

PostgreSQL: 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$;

PostgreSQL: Start, restart and the server on Linux

sudo systemctl start postgresql

sudo systemctl restart postgresql

sudo systemctl stop postgresql

PostgreSQL: Status of the server on Linux

sudo systemctl status postgresql

Find all invoice numbers

SELECT invoice_number, MAX(ticket_date_date) "invoice_date", '&table_name' "table_name"

FROM shipment_ticket

--FROM "&table_name"

WHERE invoice_number IS NOT NULL

GROUP BY invoice_number, table_name

PostgreSQL: Set all tickets Complete

ALTER TABLE "&table_name" DISABLE TRIGGER ALL;

UPDATE "&table_name" SET parent_serial_number = -2;

ALTER TABLE "&table_name" ENABLE TRIGGER ALL;

Order item totals

SELECT t1.contract_row_id, t1.contract_id, t1.item, COUNT(net_3), SUM(net_3)

FROM public.dsptch30_contract_item t1

LEFT JOIN "&table_name" t2 ON t2.contract_id = t1.contract_id AND t2.item = t1.item

GROUP BY t1.contract_row_id, t1.contract_id, t1.item

PostgreSQL: Copy signature

INSERT INTO receipt_ticket_image_driver_signature (serial_number, ml_uuid, last_modified, image_driver_signature)

SELECT

23439,

ml_uuid,

last_modified,

image_driver_signature

FROM receipt_ticket_image_driver_signature

WHERE serial_number = 22654

Johnston Bros. royalty report query

The value 1.25 must be edited according to the sources royalty rate.

WITH base AS (

SELECT

t1.ticket_date_date,

t1.item,

t1.source,

t1.net_3,

1.25 AS rate,

ROUND(t1.net_3 * 1.25, 2) AS sub_total

FROM &table_name t1

&where_clause

)

SELECT

ticket_date_date,

item,

source,

SUM(net_3) AS tonnes,

MAX(rate) AS "Rate",

SUM(sub_total) AS "Sub-Total",

SUM(ROUND(sub_total * 0.13, 2)) AS "HST",

SUM(ROUND(sub_total * 1.13, 2)) AS "Total"

FROM base

GROUP BY

ticket_date_date,

item,

source

ORDER BY

ticket_date_date,

item;