Faktureringsunderlag och kvartalsrapport

Ilog Distribution
1
  1. Distribution statistik för kvartal

Miljö: distribution-data-int.ilog.se

Databas: ilogdistributiondb

Antal ordrar i viktklass

SELECT count(*) AS weight_class FROM consignments con 
LEFT JOIN groups g ON con.group_id = g.id 
LEFT JOIN terminal t ON g.terminal = t.code
WHERE deliverydate BETWEEN 'start_date' AND 'end_date' 
AND weight > min_weight AND weight <=max_weight 
GROUP BY t.code ORDER BY t.code;

Exempel: SELECT count(*) AS weight_class FROM consignments con LEFT JOIN groups g ON con.group_id = g.id LEFT JOIN terminal t ON g.terminal = t.code WHERE deliverydate BETWEEN '2026-01-01' AND '2026-03-31' AND weight > 0 AND weight <=30 GROUP BY t.code ORDER BY t.code;

Totala vikten i ton per distrikt

SELECT t.code, round(sum(weight)/1000) FROM consignments con 
LEFT JOIN groups g ON con.group_id = g.id 
LEFT JOIN terminal t ON g.terminal = t.code 
WHERE deliverydate  between 'start_date' and 'end_date' 
GROUP BY t.code ORDER BY t.code

Exempel: SELECT round(sum(weight)/1000) FROM consignments con LEFT JOIN groups g ON con.group_id = g.id LEFT JOIN terminal t ON g.terminal = t.code WHERE deliverydate between '2026-01-01' and '2026-03-31' GROUP BY t.code ORDER BY t.code;

Örebro BUDBILEN

SELECT count(*) FROM consignments WHERE group_id = 85 
AND deliverydate::date BETWEEN 'start_date' and 'end_date';

ilogintra excel-sheet

Förslag - vidareutveckling

Detta är förslag på mer generella utdrag som vi skulle kunna tillgängliggöra för alla åkerier och distrikt, med avgränsad åtkomst per åkeri.

Obs! Förslagen är i nuläget inte validerade!

Ordervolym per åkeri och månad - "Budbilen"


with month as (
  (
    with monthspan as (
      select
        generate_series(
          date_trunc(
            'month', now() - '4 month' :: interval
          ),
          date_trunc(
            'month', now() + '1 month' :: interval
          ),
          '1 month' :: interval
        ) as start
    )
    select
      date_trunc('month', start) as start,
      to_char(start, 'YYYY-MM') as isotext
    from
      monthspan
    order by
      start
  )
),
ordervolume as (
  select
    group_id as gid,
    to_char(deliverydate, 'YYYY-MM') as month,
    count(*) as order
  from
    consignments
  group by
    group_id,
    to_char(deliverydate, 'YYYY-MM')
)
select
  v.gid,
  g.terminal as ter,
  g.name as transporter,
  v.month,
  v.order as volume
from
  month m
  left join ordervolume v on m.isotext = v.month
  left join groups g on g.id = v.gid
order by
  terminal,
  g.name,
  v.month;

TODO viktspann, per intervall från värden

with weightgroup as (select *  from 
    (
      values 
        (0, 10, '1 - 10 kg'), 
        (10, 20, '11 - 20 kg'), 
        (20, 30, '21 - 30 kg')
    ) as weightgroup (start, "end", label)
) 
select * from  weightgroup /* left join consignments on ...  group by weightgroup.start .... */ order by  start;