quick notes from a program I’m working on to track expenses

summarize the payments per employee:

select laborers.last_name as last,
       sum(labor_payments.amount) as total
    from (income.labor_payments inner join income.laborers on
       labor_payments.payee = laborers.id)
    group by last_name
    order by total desc;

Get payments by employee from db:

 SELECT
   labor_payments.amount,
   labor_payments.check_number,
   labor_payments.date,
   laborers.first_name,
   laborers.last_name
 FROM
   income.labor_payments,
   income.laborers
 WHERE
   labor_payments.payee = laborers.id
 ORDER BY last_name;

income schema

CREATE TABLE labor_payments (
    id integer NOT NULL,
    payee integer,
    date date,
    amount money,
    check_number integer
);

CREATE TABLE laborers (
    first_name character varying(32),
    last_name character varying(32),
    id integer NOT NULL
);

And from another program (using web2py’s default auth schema), to determine last login time per account.

select u.last_name, u.first_name, max(e.time_stamp) as ts
from auth_user as u inner join auth_event as e
on u.id = e.user_id
group by u.id
order by ts;

Unrelated. My first plpgsql function, just return 42.

CREATE OR REPLACE FUNCTION fortytwo()
  RETURNS integer AS
  $BODY$
  declare
	quantity integer := 42;
  begin	 
  	 raise notice 'Quantity is %', quantity; -- prints 42
         return quantity;
  end;
  $BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;

Quarterly unemployment report

select first_name, last_name, sum(state_ui)
from employees.paychecks
join employees.employees on (paychecks.emp_id = employees.emp_id)
where paydate >= '2016-01-01'::date
and paydate <= '2016-04-01'::date
group by employees.emp_id
order by sum(state_ui) desc;