Dokumentation för TMS statistik

iLog TMS
1
  1. Aktiva Åkerier som använder Towern

Databas: ilogtowerndb

SELECT groups.name AS group_name, 
COUNT(DISTINCT ic.id) AS consignment_count, SUM(weight) AS weight_sum, 
SUM(estimated_weight) AS estimated_weight_sum
FROM groups, invoices
JOIN invoices_consignments ic ON invoices.id=ic.invoice_id
LEFT JOIN divisions d ON invoices.division_id=d.id
LEFT JOIN customers c ON invoices.customer_id=c.id
LEFT JOIN users u ON invoices.created_by=u.username
WHERE COALESCE(d.group_id, c.group_id, u.group_id)=groups.id 
AND invoice_date>='start_date' AND invoice_date<'end_date'
GROUP BY group_name
ORDER BY group_name;

Antal ordrar i Towern per åkeri

Databas: ilogtowerndb

SELECT groups.name AS group_name, COUNT(*) AS invoice_count, 
SUM(total) AS total_sum FROM groups, invoices
LEFT JOIN divisions d ON invoices.division_id=d.id
LEFT JOIN customers c ON invoices.customer_id=c.id
LEFT JOIN users u ON invoices.created_by=u.username
WHERE COALESCE(d.group_id, c.group_id, u.group_id)=groups.id AND 
invoice_date>='start_date' AND invoice_date<'end_date'
GROUP BY group_name
ORDER BY group_name;

ilogintra excel-sheet

  1. Vilka åkeri och hur många bokningar varje åkeri har skickat samt tagit emot

Databas: btfdb

Skickat

SELECT count(*), g.name FROM bourseitems bi LEFT JOIN groups g 
ON bi.sendergroup_id = g.id WHERE received::date between 'start_date' 
AND 'end_date' GROUP BY g.name;

Tagit emot

SELECT count(*), g.name FROM bourseitems bi 
LEFT JOIN groups g ON bi.receiver_transporternumber = g.transporternumber 
WHERE received::date BETWEEN 'start_date' AND 'end_date' group by g.name;

ilogintra excel-sheet

  1. Fleet

Antal aktiva åkerier och aktiva användare

Databas: btfdb

commandtype 524 - getLatestGeopositions

SELECT g.name, COUNT(*),  COUNT(distinct user_id) FROM userlogs ul 
LEFT JOIN users u ON ul.user_id = u.id 
LEFT JOIN groups g ON g.id = u.group_id 
WHERE time::date BETWEEN 'start_date' AND  'end_date'  
AND commandtype_id = 524 GROUP BY g.name ORDER BY COUNT(*) DESC;

ilogintra excel-sheet

  1. Antal Eget gods Bokningar vid EDI in per Åkeri och per integration

Databas: btfdb

SELECT g.name, count(*), source FROM consignments con 
LEFT JOIN groups g ON con.group_id = g.id  
WHERE source NOT IN ('ilog-sped', '') 
AND pickupdatefrom::date BETWEEN 'start_date' AND 'end_date' 
GROUP BY g.name, source;

ilogintra excel-sheet