SQLite Abfrageergebnis weiterverwerten

Computer: Software & Programmierung SQL & Datenbank - Programmierung
#1

Lieber SQLite experte,

Mein Problem, bei dem Du mir evtl. helfen könntest:

In einer Datenbank mit weit über 1.000 Einträgen habe ich Personaldaten erfasst.

CREATE TABLE planung ( Bereich TEXT NOT NULL, Abteilung TEXT NOT NULL, Name TEXT NOT NULL, Vorname TEXT NOT NULL, EUR_Std INTEGER, Std_Mon INTEGER NOT NULL, Beginn INTEGER NOT NULL, Ende INTEGER NOT NULL, id INTEGER PRIMARY KEY AUTOINCREMENT, CONSTRAINT einmalig UNIQUE (Abteilung, Name, Vorname ,Std_Mon, Beginn , Ende ) )

Ich muss ermitteln, welche Kosten je Monat anfallen werden. Dazu verwende ich

CREATE VIEW „v_2011_10“ AS SELECT
Bereich ,
Abteilung,
Name,
Vorname ,
EUR_Std ,
Std_Mon ,
CASE WHEN Beginn „2011-12-31“ THEN „2011-12-31“ ELSE Ende END AS Ende
FROM planung
WHERE Beginn = „2011-12-01“
AND Name NOT NULL

Und dann

CREATE VIEW monatsweise AS SELECT
Bereich ,
Abteilung ,
Name ,
Vorname ,
EUR_Std ,
Std_Mon ,
strftime(’%Y-%m’,Beginn) AS KalMon ,
Beginn,
Ende,
julianday(Ende)-julianday(Beginn) +1 AS Tage , round(„€/Std“ * „Std/Mon“ * (julianday(Ende)-julianday(Beginn) +1) / strftime(’%d’,Beginn,‚start of month‘,’+1 month’,’-1 day’),2) AS Gehalt
FROM „v_2011_10“
UNION ALL
und das ganze noch und noch und noch einmal

Das finde ich aber viel zu umständlich.

Die betroffenen Monate kann ich ermitteln:

CREATE VIEW perioden AS SELECT
DISTINCT(date(Beginn,‚start of month‘)) AS mstart ,
date(Beginn,‚start of month‘,’+1 month’,’-1 day’) AS mende
FROM planung
ORDER BY mstart

Wie kann ich es in sqlite errreichen, dass ich die Monate aus perioden abarbeite und mir die obigen Daten automatisch in einen view einträgt?

In den beiden obigen Abfragen müsste dann irgendwie die Ergebnisse von mstart und mende verwendet werden können und dieser view abgearbeitet (Schleife) werden.

Ich bin jedenfalls schon einige Zeit mit meinem Latein am Ende.

ich hoffe ich habe mich nicht zu umständlich ausgedrück.

Leider kann ich nur den sqlite-Browsser in Firefox verwenden. ich darf keine Datenbank offiziell anwenden :-{

Du bist übrigens der einzige in wer-weiss-was mit sqlite.

gechse

#2

Wie kann ich es in sqlite errreichen, dass ich die Monate aus
perioden abarbeite und mir die obigen Daten automatisch in
einen view einträgt?

Hallo gechse,

in SQLite gibt es keine Möglichkeiten, Schleifen zu durchlaufen. Wenn man es also iterativ lösen wollte, müsste man eine Programmiersprache verwenden - aber wenn das bei dir nicht geht, musst du eben mit Subqueries arbeiten. (Machst du über die Views ja im Grunde auch.)

Ich bin mir nich 100% sicher, was das gewünschte Endergebnis ist. Da du von Kosten sprichst, vermutlich sowas in dieser Form:

MONAT | KOSTEN IN EURO
2012-01 | 1200.00
2012-02 | 1114.21
2012-03 | 2012.11
2012-04 | 50.12
usw.

Es sollte vermieden werden, für jeden Monat eigene Views anzulegen, weil dadurch viel manuelle Arbeit nötig wird.

Ich würde daher erst einmal eine Tabelle „monate“ anlegen und mit Daten füllen.

CREATE TABLE monate (Monat INTEGER, Jahr INTEGER, MonatsString VARCHAR(7), PRIMARY KEY (Monat, Jahr));

INSERT INTO monate VALUES (1, 2012, '2012-01');
INSERT INTO monate VALUES (2, 2012, '2012-02');
INSERT INTO monate VALUES (3, 2012, '2012-03');
...
INSERT INTO monate VALUES (11, 2012, '2012-11');
INSERT INTO monate VALUES (12, 2012, '2012-12');
...


    
    
    Das Ziel sollte sein, dass man für zusätzliche Monate keine Views oder Abfragen neu schreiben muss, sondern nur den Monat in diese Tabelle einfügen muss.
    
    Als erstes kann man dann recht einfach ermitteln, welche Personen/Posten der Planungs-Tabelle in welchem Monat relevant sind:
    
    
    
        
        SELECT
         Bereich,
         Abteilung,
         Name,
         Vorname,
         EUR\_Std,
         Std\_Mon,
         MonatsString,
         Beginn,
         Ende,
         CASE WHEN strftime('%Y-%m', p.Beginn) m.MonatsString THEN date(m.MonatsString || '-01', '+1 month', '-1 day') ELSE Ende END AS RechenEnde 
        FROM
         planung p,
         monate m
        WHERE
         strftime('%Y-%m', p.Beginn) = m.MonatsString;
    
    
    
    
    Dann kann man drumherum die Abfrage bauen, die die Kosten ermittelt:
    
    
    
        
        SELECT 
         Bereich,
         Abteilung,
         Name,
         Vorname,
         EUR\_Std,
         Std\_Mon,
         MonatsString,
         RechenBeginn,
         RechenEnde,
         round(EUR\_Std \* Std\_Mon \* (julianday(Ende)-julianday(Beginn) +1) / strftime('%d',Beginn,'start of month','+1 month','-1 day'),2) AS Gehalt
        FROM (
         SELECT
         Bereich,
         Abteilung,
         Name,
         Vorname,
         EUR\_Std,
         Std\_Mon,
         MonatsString,
         Beginn,
         Ende,
         CASE WHEN strftime('%Y-%m', p.Beginn) m.MonatsString THEN date(m.MonatsString || '-01', '+1 month', '-1 day') ELSE Ende END AS RechenEnde 
         FROM
         planung p,
         monate m
         WHERE
         strftime('%Y-%m', p.Beginn) = m.MonatsString
        )
        ORDER BY MonatsString, Name, Vorname;
    
    
    
    
    (In der Gehaltsberechnung steckt noch ein Fehler, da musst du halt mal schauen...)
    
    Daraus könntest du jetzt natürlich ein View machen und darauf abfragen oder direkt wieder die Summenbildung als Drumherumgeschachtelte Query machen:
    
    
    
        
        SELECT
         MonatsString,
         SUM(Gehalt)
        FROM (
         SELECT 
         Bereich,
         Abteilung,
         Name,
         Vorname,
         EUR\_Std,
         Std\_Mon,
         MonatsString,
         RechenBeginn,
         RechenEnde,
         round(EUR\_Std \* Std\_Mon \* (julianday(Ende)-julianday(Beginn) +1) / strftime('%d',Beginn,'start of month','+1 month','-1 day'),2) AS Gehalt
         FROM (
         SELECT
         Bereich,
         Abteilung,
         Name,
         Vorname,
         EUR\_Std,
         Std\_Mon,
         MonatsString,
         Beginn,
         Ende,
         CASE WHEN strftime('%Y-%m', p.Beginn) m.MonatsString THEN date(m.MonatsString || '-01', '+1 month', '-1 day') ELSE Ende END AS RechenEnde 
         FROM
         planung p,
         monate m
         WHERE
         strftime('%Y-%m', p.Beginn) = m.MonatsString
         )
        )
        GROUP BY MonatsString
        ORDER BY MonatsString;
    
    
    
    
    Ich hoffe, das hilft weiter. Falls nicht, melde dich nochmal.
    
    Viele Grüße
    Stefan
#3

Hallo Stefan,

zuerst einmal vielen Dank.

Leider habe ich mich bei meiner Anfrag etwas … ausgedrückt.

Zuerst einmal einige Hintergrundinfos.
Unter anderem muss ich die zu erwartenden monatlichen Kosten von sagen wir einmal „Tagelöhnern“ berechnen. Diese können an jedem beliebigen Tag eingestellt und an jedem beliebigen Tag das Beschäftigungsverhältnis beenden (passiv und aktiv).

Die Daten zu den Beschäftigungsverträgen gebe ich in eine Liste ein:

 Bereich , 
 Abteilung, 
 Name, 
 Vorname , 
 EUR\_Std , 
 Std\_Mon ,
 Beginn ,
 Ende

Mit diesen Angaben ermittele ich in einer Tabellenkalkulation ganz grob:

  • Beschäftigungsmonate
  • Gesamtstunden
  • Gehalt ohne AG-Zulage
  • AG-Zulage
  • Gehalt mit AG-Zulage

Natürlich wollen die Bereich und Abteilungen wissen, was in welchem Monat auf sie zukommt. Deshalb exportiere ich die Zahlen in meiene sqlite-Datenbank (außer Access darf ich offiziell keine Datenbank verwenden. Und Access habe ich nie verstanden. Ich kapiere die ganzen Assistenen nicht)

Bisher habe ich mir dann mit Datenverarbeitung per Krückstock für jeden Monat einen View angelegt (also die entsprechenen Monatsanfänge und -enden von Hand für einen neuen View eingetragen)
Diese Views habe ich wiederum in einen neuen View mit UNION ALL zusammengefasst.
Dadurch konnte ich dann einfachen Abfragen ermitteln, welcher Bereich/Abteilung/Person/Monat Kosten angefallen sind bzw. anfallen. Vergangenheit und Zukunft.

Ich bin jedoch der Ansicht, dass das auch automatisiert laufen könnte, also der Krückstock wegfallen kann.
Über einen ersten View lasse ich mir alle Monate ausgeben, welche von allen Arbeitsverträgen betroffen sind.

CREATE VIEW monate as select 
distinct(date(Beginn,'start of month')) AS mstart , 
date(Beginn,'start of month','+1 month','-1 day') AS mende 
FROM hiwiplanung ORDER BY mstart

Das was ich jetzt suche ist eine Möglichkeit den View monate in Verbindung mit der Gesamtliste für die weitere Berechnung zu verwenden.

Bei meinen Recherchen habe ich gefunden, dass es so etwas wie ein Define oder Set in der Abfrage geben soll. Nur verstehe ich noch nicht, wie das richtig funktioniert.

WITH SELECT mstart FROM monate AS start,
WITH SELECT mende FROM monat AS ende,
SELECT
Bereich,
Abteilung,
Name,
Vorname,
Euro pro Stunde lt. Arbeitsvertrag,
Stunden im Monat lt. Arbeitsvertrag,
Monat für den diese Berechnung erfolgt ermitelt mit start und ende aus obigen WITH,
Erster individueller Arbeitstag im Monat in Abhängigkeit vom start),
letzter individueller Arbeitstag im Monat (in Abhängigkeit von ende),
individuelle Stundenzahl in diesem Monat (STD lt. AV / Tage im Monat \* individuelle Arbeitstage im Monat),
Gehalt ohne AG-Zulage
AG-Zulage
Gehalt mit AG-Zulage

Mein großes Problem ist die WITH Zeilen irgendwie hier einzubauen und abarbeiten zu lassen.
Die Berechnungen sind mir klar.

Kannst Du mir da weiterhelfen?

Zugegeben, ein ganz schöner Brocken.

Vielen Dank
gechse

#4

Hallo Stefan,

nocheinmal ich :smile:

ich habe die Lösung gefunden:
ein neuer View über einen einfachen Join

CREATE VIEW "persmonat" AS SELECT 
hiwiplanung.Bereich, 
hiwiplanung.Name, 
hiwiplanung.Vorname , 
hiwiplanung.Beginn , 
hiwiplanung.Ende , 
monate.mstart , 
monate.mende 
FROM hiwiplanung, monate 
WHERE hiwiplanung.Beginn = monate.mstart

Klar noch mit einigen weiteren Spalten aber es funktioniert :smile:

Darauf gekommen bin ich als ich Deinen Text noch einmal mit Textmarker zerlegt habe - Danke Dir

gechse

#5

Bisher habe ich mir dann mit Datenverarbeitung per Krückstock
für jeden Monat einen View angelegt (also die entsprechenen
Monatsanfänge und -enden von Hand für einen neuen View
eingetragen)
Diese Views habe ich wiederum in einen neuen View mit UNION
ALL zusammengefasst.
Dadurch konnte ich dann einfachen Abfragen ermitteln, welcher
Bereich/Abteilung/Person/Monat Kosten angefallen sind bzw.
anfallen. Vergangenheit und Zukunft.

Habe ich verstanden. Dasselbe könntest du mit meinem Vorschlag erreichen, OHNE dass du für jeden Monat eine View anlegen musst. Du musst nur die Monate, die dich interessieren, in die Tabelle „monate“ eintragen - fertig.

Bei meinen Recherchen habe ich gefunden, dass es so etwas wie
ein Define oder Set in der Abfrage geben soll. Nur verstehe
ich noch nicht, wie das richtig funktioniert.

Gibt es in SQLite meines Wissens nicht (evtl. in anderen SQL-Derivaten).

WITH SELECT mstart FROM monate AS start,
WITH SELECT mende FROM monat AS ende,
SELECT
Bereich,
…

Mein großes Problem ist die WITH Zeilen irgendwie hier
einzubauen und abarbeiten zu lassen.

SQLite sieht die „WITH …“ Syntax nicht vor, insofern hast du mit dieser Herangehensweise keine Chance.

Siehe: http://www.sqlite.org/lang.html
und insbesondere: http://www.sqlite.org/lang_select.html

Ein ganz anderer Lösungsweg könnte sein, über Excel und VB-Scripting zu gehen. Excel scheinst du ja einsetzen zu „dürfen“.

Viele Grüße
Stefan