165 lines
6.5 KiB
SQL
165 lines
6.5 KiB
SQL
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
SET statement_timeout = 0;
|
|
SET lock_timeout = 0;
|
|
SET idle_in_transaction_session_timeout = 0;
|
|
SET client_encoding = 'UTF8';
|
|
SET standard_conforming_strings = on;
|
|
SELECT pg_catalog.set_config('search_path', '', false);
|
|
SET check_function_bodies = false;
|
|
SET xmloption = content;
|
|
SET client_min_messages = warning;
|
|
SET row_security = off;
|
|
|
|
DROP VIEW IF EXISTS public."Arbeitszeiten - Woche";
|
|
DROP VIEW IF EXISTS public."Arbeitszeiten - Monat";
|
|
DROP VIEW IF EXISTS public."Arbeitszeiten - Jahr";
|
|
DROP TABLE IF EXISTS public."Arbeitszeiten";
|
|
|
|
--
|
|
-- Name: Arbeitszeiten; Type: TABLE; Schema: public; Owner: torsten
|
|
--
|
|
|
|
CREATE TABLE public."Arbeitszeiten"
|
|
(
|
|
"Datum" date not null,
|
|
"Arbeitszeit" interval(6) null,
|
|
"HomeOffice" boolean default FALSE not null,
|
|
constraint "Arbeitszeiten_pkey" primary key ("Datum")
|
|
);
|
|
|
|
|
|
ALTER TABLE public."Arbeitszeiten"
|
|
OWNER TO bruce;
|
|
|
|
--
|
|
-- Name: Arbeitszeiten - Jahr; Type: VIEW; Schema: public; Owner: torsten
|
|
--
|
|
|
|
CREATE OR REPLACE VIEW public."Arbeitszeiten - Jahr" AS
|
|
SELECT (date_part('year'::text, "Arbeitszeiten"."Datum"))::text AS "Jahr",
|
|
sum("Arbeitszeiten"."Arbeitszeit")::interval AS "Gesamtarbeitszeit",
|
|
count("Arbeitszeiten"."Datum")::bigint AS "Arbeitstage",
|
|
(sum("Arbeitszeiten"."Arbeitszeit") -
|
|
((count("Arbeitszeiten"."Datum"))::double precision * '08:00:00'::interval)) AS "Überstunden",
|
|
(count(*) filter (where "Arbeitszeiten"."HomeOffice"))::int as "Home-Office-Tage"
|
|
FROM public."Arbeitszeiten"
|
|
GROUP BY (date_part('year'::text, "Arbeitszeiten"."Datum"))
|
|
ORDER BY (date_part('year'::text, "Arbeitszeiten"."Datum"));
|
|
|
|
|
|
ALTER TABLE public."Arbeitszeiten - Jahr"
|
|
OWNER TO bruce;
|
|
|
|
--
|
|
-- Name: Arbeitszeiten - Monat; Type: VIEW; Schema: public; Owner: torsten
|
|
--
|
|
|
|
CREATE OR REPLACE VIEW public."Arbeitszeiten - Monat" AS
|
|
SELECT to_char(date_trunc('month'::text, ("Arbeitszeiten"."Datum")::timestamp with time zone),
|
|
'TMYYYY TMMonth'::text) AS "Monat",
|
|
sum("Arbeitszeiten"."Arbeitszeit")::interval AS "Gesamtarbeitszeit",
|
|
count("Arbeitszeiten"."Datum")::bigint AS "Arbeitstage",
|
|
(sum("Arbeitszeiten"."Arbeitszeit") -
|
|
((count("Arbeitszeiten"."Datum"))::double precision * '08:00:00'::interval)) AS "Überstunden",
|
|
(count(*) filter (where "Arbeitszeiten"."HomeOffice"))::int as "Home-Office-Tage"
|
|
FROM public."Arbeitszeiten"
|
|
GROUP BY (date_trunc('month'::text, ("Arbeitszeiten"."Datum")::timestamp with time zone))
|
|
ORDER BY (date_trunc('month'::text, ("Arbeitszeiten"."Datum")::timestamp with time zone));
|
|
|
|
|
|
ALTER TABLE public."Arbeitszeiten - Monat"
|
|
OWNER TO bruce;
|
|
|
|
--
|
|
-- Name: Arbeitszeiten - Woche; Type: VIEW; Schema: public; Owner: torsten
|
|
--
|
|
|
|
CREATE OR REPLACE VIEW public."Arbeitszeiten - Woche" AS
|
|
SELECT to_char(date_trunc('week'::text, ("Arbeitszeiten"."Datum")::timestamp with time zone),
|
|
'TMYYYY#WW'::text) AS "Woche",
|
|
sum("Arbeitszeiten"."Arbeitszeit")::interval AS "Gesamtarbeitszeit",
|
|
count("Arbeitszeiten"."Datum")::bigint AS "Arbeitstage",
|
|
(sum("Arbeitszeiten"."Arbeitszeit") -
|
|
((count("Arbeitszeiten"."Datum"))::double precision * '08:00:00'::interval)) AS "Überstunden",
|
|
(count(*) filter (where "Arbeitszeiten"."HomeOffice"))::int as "Home-Office-Tage"
|
|
FROM public."Arbeitszeiten"
|
|
GROUP BY (date_trunc('week'::text, ("Arbeitszeiten"."Datum")::timestamp with time zone))
|
|
ORDER BY (date_trunc('week'::text, ("Arbeitszeiten"."Datum")::timestamp with time zone));
|
|
|
|
|
|
ALTER TABLE public."Arbeitszeiten - Woche"
|
|
OWNER TO bruce;
|
|
|
|
--
|
|
-- Data for Name: Arbeitszeiten; Type: TABLE DATA; Schema: public; Owner: torsten
|
|
--
|
|
|
|
INSERT INTO public."Arbeitszeiten" ("Datum", "Arbeitszeit")
|
|
VALUES ('2020-01-07', '08:06:00'),
|
|
('2020-01-08', '08:16:00'),
|
|
('2020-01-09', '07:49:00'),
|
|
('2020-01-10', '07:30:00'),
|
|
('2020-01-13', '08:06:00'),
|
|
('2020-01-14', '08:04:00'),
|
|
('2020-01-15', '08:20:00'),
|
|
('2020-01-16', '07:13:00'),
|
|
('2020-01-17', '07:57:00'),
|
|
('2020-01-20', '07:53:00'),
|
|
('2020-01-21', '07:49:00'),
|
|
('2020-01-22', '07:56:00'),
|
|
('2020-01-23', '07:55:00'),
|
|
('2020-01-24', '07:08:00'),
|
|
('2020-01-27', '08:23:00'),
|
|
('2020-01-28', '07:20:00'),
|
|
('2020-01-29', '08:13:00'),
|
|
('2020-01-30', '08:43:00'),
|
|
('2020-01-31', '07:21:00'),
|
|
('2020-02-03', '07:56:00'),
|
|
('2020-02-04', '08:05:00'),
|
|
('2020-02-05', '07:58:00'),
|
|
('2020-02-06', '08:01:00')
|
|
;
|
|
|
|
INSERT INTO public."Arbeitszeiten" ("Datum", "Arbeitszeit", "HomeOffice")
|
|
VALUES ('2020-02-07', '07:59:00', FALSE),
|
|
('2020-02-10', '07:50:00', FALSE),
|
|
('2020-02-11', '08:01:00', FALSE),
|
|
('2020-02-12', '08:14:00', FALSE),
|
|
('2020-02-13', '08:12:00', FALSE),
|
|
('2020-02-14', '07:51:00', FALSE),
|
|
('2020-02-17', '07:59:00', FALSE),
|
|
('2020-02-18', '08:05:00', FALSE),
|
|
('2020-02-19', '07:34:00', FALSE),
|
|
('2020-02-20', '07:33:00', FALSE),
|
|
('2020-02-21', '07:56:00', FALSE),
|
|
('2020-02-24', '08:02:00', FALSE),
|
|
('2020-02-25', '08:13:00', FALSE),
|
|
('2020-02-26', '08:42:00', FALSE),
|
|
('2020-02-27', '07:49:00', TRUE),
|
|
('2020-02-28', '08:16:00', FALSE),
|
|
('2020-03-02', '08:22:00', FALSE),
|
|
('2020-03-03', '08:20:00', FALSE),
|
|
('2020-03-04', '08:12:00', FALSE),
|
|
('2020-03-05', '08:32:00', FALSE),
|
|
('2020-03-06', '04:41:00', FALSE),
|
|
('2020-03-09', '07:05:00', FALSE),
|
|
('2020-03-10', '07:34:00', FALSE),
|
|
('2020-03-11', '07:39:00', TRUE),
|
|
('2020-03-12', '07:50:00', FALSE),
|
|
('2020-03-13', '08:26:00', FALSE),
|
|
('2020-03-16', '07:51:00', FALSE),
|
|
('2020-03-17', '07:50:00', FALSE),
|
|
('2020-03-18', '07:19:00', FALSE),
|
|
('2020-03-19', '07:55:00', FALSE),
|
|
('2020-03-20', '05:43:00', TRUE),
|
|
('2020-03-23', '08:05:00', FALSE),
|
|
('2020-03-24', '08:21:00', FALSE),
|
|
('2020-03-25', '08:10:00', FALSE),
|
|
('2020-03-26', '10:31:00', FALSE),
|
|
('2020-03-27', '04:55:00', FALSE),
|
|
('2020-03-30', '08:21:00', FALSE),
|
|
('2020-03-31', '08:01:00', FALSE)
|
|
; |