CTE in temp Table speichern

[Ruediger007]

Hallo!

Ich habe mir folgende CTE zusammengebaut in der jedes Datum von X bis heute aufgelistet wird.

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME
 
SET @startDate = '2013-01-01'
SET @endDate = GETDATE()
 
 
;WITH CTE_Dates AS
(
    SELECT @startDate AS DT
    UNION ALL
    SELECT DATEADD(DD,1,DT) FROM CTE_Dates
    WHERE DT <= @endDate
)
SELECT * FROM CTE_Dates
OPTION (MAXRECURSION 0)

Jetzt muss ich aber mit der Tabelle in verschiedener Art und Weise in einer Stored Procedure weiterarbeiten und müsste sie dafür in eine temporäre Tabelle kopieren. Ich weiss nur nicht wie. Mein Ansatz war:

CREATE TABLE #DATUMS_TABELLE(Datum as DATETIME)
DECLARE @endDate DATETIME
 
SET @startDate = '2013-01-01'
SET @endDate = GETDATE()
INSERT INTO #DATUMS_TABELLE
;WITH CTE_Dates AS
(
    SELECT @startDate AS DT
    UNION ALL
    SELECT DATEADD(DD,1,DT) FROM CTE_Dates
    WHERE DT <= @endDate
)
SELECT * FROM CTE_Dates
OPTION (MAXRECURSION 0)

Aber das funktioniert nicht. Bei dem Aufruf

 SELECT * FROM #DATUMS_TABELLE

bekomme ich die Fehlermeldung Incorrect syntax near the keyword 'FROM.
Kann mir hier vielleicht jemand weiterhelfen?

 

[akretschmer]

Jetzt muss ich aber mit der Tabelle in verschiedener Art und Weise in einer Stored Procedure weiterarbeiten und müsste sie dafür in eine temporäre Tabelle kopieren. Ich weiss nur nicht wie. Mein Ansatz war:

Geht nicht einfach ein create table as ... oder ein insert into ... select ...?

Das geht in PG:

test=# create table my_tmp_table as select '2013-01-01'::date + s * '1day'::interval as datum from generate_series(0,5) s;
SELECT 6
test=*# select * from my_tmp_table ;
        datum
---------------------
 2013-01-01 00:00:00
 2013-01-02 00:00:00
 2013-01-03 00:00:00
 2013-01-04 00:00:00
 2013-01-05 00:00:00
 2013-01-06 00:00:00
(6 rows)

und

test=*# create table my_tmp_table (d date);
CREATE TABLE
test=*# insert into my_tmp_table select  '2013-01-01'::date + s * '1day'::interval from generate_series(0,5) s;
INSERT 0 6
test=*# select * from my_tmp_table ;
     d
------------
 2013-01-01
 2013-01-02
 2013-01-03
 2013-01-04
 2013-01-05
 2013-01-06
(6 rows)

Sollte doch auch in M$SQL so gehen, oder?

Andreas

 

[Ruediger007]

Hallo Andreas,

ich habe versucht den Code durchlaufen zu lassen, aber da bekomme ich zig Fehler. Leider kenne ich mich gerade mal ein wenig mit MSSQL aus, so dass ich nicht in der Lage bin, den Code anzupassen.

Aber danke trotzdem!

EDIT:
Ok, die Lösung war:
Mache aus

SELECT * FROM CTE_Dates

die Zeile

SELECT * INTO #DATUMS_TABELLE FROM CTE_Dates

 

[ukulele]

Was für einen Zweck verfolgst du eigentlich und warum willst du die Tabelle mit allen Tagen jedesmal neu erstellen? Nur so aus Neugierde

 

[Ruediger007]

Hallo Ukulele!

Ich führe einfach mal aus, was ich mache. Es wäre natürlich super, wenn du mir dazu einen Kommentar hinterlassen würdest. Ich bin nämlich sehr neu in SQL und Datenbanken und habe mir das alles selbst zusammengereimt.

Ich arbeite an einem time-recording programm mit vb.net als benutzerschnittstelle und sql-server.

Meine Zieltabelle soll so aussehen:
Datum, Monatsname, Datum_Name (also Freitag, Samstag usw.), Kalenderwoche, Mitarbeiter, Soll-Arbeitsstunden, Ist-Arbeitsstunden.

Daraus generiere ich dann für jeden Mitarbeiter (läuft über stored procedure mit windows authentication, also jeder Mitarbeiter kann nur seine Sachen abfragen):
- Monatsübersichten (Im Frontet gibt es Tabs für jeden Monat mit allen Einträgen für den Monat)
- Eine Übersicht je Kalenderwoche (Soll, Ist und Überstunden pro Woche summiert)
- Eine Übersicht je Monat (Soll, Ist und Überstunden pro Monat summiert)

Da die Mitarbeiter auch am Wochenende arbeiten können und es natürlich Feiertage gibt (Feiertagstabelle ist angelegt) arbeite ich auf Tagesbasis. Ein weiterer Grund hierfür ist, dass manche Mitarbeiter nur Teilzeit arbeiten (Mo 4 Std, Di nicht, Mi 7 Std) es aber auch möglich ist, dass sie an anderen Tagen arbeiten .

Aus diesem Grund habe ich mir überlegt die o.g. Tabelle zu erstellen und daraus die jeweiligen Subtabellen abzurufen.

Und bei diesem Prozess brauche ich dann (glaube ich) die Tabelle.

VG

Ruediger

 

[Tommi]

Hi,

die Frage von ukulele ist nicht ganz unberechtigt. Es ist natürlich wesentlich performanter, wenn man die Tage und davon abhängigen Werte schon in einer Tabelle vorhält.
Dennoch schrecken davor viele zurück, da natürlich auch für die zukünftigen Jahre die Daten vorgehalten werden müssen. Oft kann man nicht abschätzen, ob denn 10 oder 20 Jahre dafür angesetzt werden müssen/sollen.

Generell gibt es aber natürlich auf dem SQL-Server die Möglichkeit eine solche Tabelle über eine Tabellenwertfunktion so bereitzustellen, dass diese dynamisch den gewünschten Zeitraum abdeckt.
Ich habe hierzu mal eine Funktion geschrieben die wie folgt aussieht:

CREATE FUNCTION dbo.fct_Tage_Zeitraum(@vonDatum datetime, @bisDatum datetime) RETURNS @RET TABLE
 ( Datum datetime,
  Jahr as YEAR(Datum),
  QuartalNr as DATEPART(quarter, Datum),
  Quartal as 'Quartal '+CAST(DATEPART(quarter, Datum) as varchar(10)),
  MonatNr as MONTH(Datum),
  Monat as DATENAME(month, Datum),
  KW as DATEPART(ISO_WEEK, Datum),
  Wochentag as DATENAME(weekday, Datum)
 )
AS
BEGIN
 WHILE @vonDatum<[EMAIL]=@bisDatum[/EMAIL]
 BEGIN
  INSERT INTO @RET (Datum) VALUES (@vonDatum)
  SET @vonDatum=DATEADD(day, 1, @vonDatum)
 END
 RETURN
END

Die Abfrage dieser Funktion erfolgt dann über diese Syntax:

SELECT *
FROM dbo.fct_Tage_Zeitraum('01.01.2009', '31.12.2013')

Diese Ermittlung ist aber wesentlich unperformanter, da auf eine Tabellenwertfunktion kein Index gesetzt werden kann, wie das bei einer fixen Tabelle der Fall wäre.
In einer festen Tabelle sind die Daten auch jederzeit verfügbar und müssen nicht erst über eine Schleife (ob jetzt wie bei mir mit WHILE oder über die WITH-Funktion) erstellt werden,



Viele Grüße,
Tommi

 

[ukulele]

Ich glaube die Lösung von Tommi gabs hier erst vor ein paar Tagen und diente auch zur Darstellung von Auswertungen in einem ähnlichen Fall.

Ich würde glaube ich auch mit einer (fixen) Tabelle arbeiten in der einfach alle Tage stehen allein der einfacheren Handhabung wegen. Wenn du allerdings in deinen Auswertungen immer nur Tage oder Wochen aufführst, in denen auch Zeiten aufgelaufen sind, würde ich nur mit GROUP BY etc. arbeiten, dann bräuchtest du keine derartige Tabelle.

 

[Ruediger007]

Hallo!
Ich werden mir jetzt mal Gedanken machen, ob ich das mit Group by etc. hinbekommen und ansonsten die fixe Tabelle nutzen.
Herzlichen Danke für die Tips!
Viele Grüße
Ruediger

 

[Ruediger007]

Hallo! ich habe nochmal eineFrage, die mit der Tabelle zutun hat, die wir oben hergeleitet haben.

Ich habe die jetzt als fixe Tabelle hinzugefuegt.

Felder:
TAB_ID, Mitarbeiter_ID, DATUM, KW, MONAT, WOCHENTAG usw.
1,1,01.01.2013,1,Januar,Dienstag
2,1,01.02.2013.1,Januar,Mittwoch
...
Für jeden Mitarbeiter hat also für jeden Tag in einem Jahr einen Eintrag.

Die Tabelle ist mit der Mitarbeiter Tabelle verbunden über die Mitarbeiter_ID. Diese Tabelle ist mit der Verträge Tabelle über die Mitarbeiter_ID verbunden und diese wiederum über die Zeiten Tabelle über die Vertrags_ID. In dieser ZeitenTabelle habe ich die Felder:

TAB2_ID, Vertrags_ID, Arbeitszeiten in Stunden, Tag
1, 1, 8, Montag
2,1,8,Dienstag
3,1,8,Mittwoch
4,1,8,Donnerstag
5,1,8,Freitag
6,2,4,Monatag
7,2,Null,Dienstag
8,2,6,Mittwoch
9,2,NULL,Donnerstag
10,2,5,Freitag

Das Ziel ist jetzt eigentlich, dass ich einen Join machen in dem die Felder aus Tabelle 1 drin stehen und pro Tag die Informationen über die zu arbeitenden Stunden mit drinstehen. also

TAB_ID, Mitarbeiter_ID, DATUM, KW, MONAT, WOCHENTAG usw.
1,1,01.01.2013,1,Januar,Dienstag,8
2,1,01.02.2013.1,Januar,Mittwoch,8

Ich denke, dass muss irgendwie durchgeloopt werden für jeden Mitarbeiter, hänge aber gerade. Damit ihr nicht denkt, ich möchte andere die Arbeit machen lassen, poste ich was ich bis jetzt habe, aber ich glaube, das ist irgendwie ein falscher Ansatz.

SET @LETZTER_MA =  (SELECT MAX(MA_ID)  FROM Mitarbeiter)
PRINT @LETZTER_MA
SET @i = 1
 
WHILE @i <= @LETZTER_MA
BEGIN
    Select    TU.Datum, TU.Jahr, TU.Kalenderwoche, TU.MA_ID,
            TU.Monat, TU.MonatNr, TU.Wochentag,
            (xxx) as Arbeitsstunden
           
    from dbo.Tagesuebersicht as TU
    LEFT JOIN dbo.Mitarbeiter Mit
    on TU.MA_ID = Mit.MA_ID
    LEFT JOIN dbo.Vertrag Vert
    on Mit.MA_ID = Vert.MA_ID
    LEFT JOIN dbo. ZeitenTabelle Arb
    on Vert.Vertrag_ID = Arb.Vertrag_ID
   
    print @i
    SET @i = @i +1
END
 
--select * from tagesuebersicht

Wäre super, wenn ihr mir nochmal helfen könntet.

VG Ruediger

 

[akretschmer]

Hallo! ich habe nochmal eineFrage, die mit der Tabelle zutun hat, die wir oben hergeleitet haben.

Ich habe die jetzt als fixe Tabelle hinzugefuegt.

Felder:
TAB_ID, Mitarbeiter_ID, DATUM, KW, MONAT, WOCHENTAG usw.
1,1,01.01.2013,1,Januar,Dienstag
2,1,01.02.2013.1,Januar,Mittwoch
...
Für jeden Mitarbeiter hat also für jeden Tag in einem Jahr einen Eintrag.

Bitte sowas vermeiden, das ist ja hochgradig redundant. Aus einem Datum die KW, den Monat und den Wochentag zu berechnen ist erheblich sinnvoller als dieses zu speichern.
Aus solch einer Tabelle und einer Tabelle der Mitarbeiter eine Tabelle je Mitarbeiter und Tag zu machen auch.

Auch wenn ich Dir mit M$SQL nicht weiter helfen kann: das, was Du machst, schmerzt erheblich.

 

[Ruediger007]

Du hast natürlich recht, die Tagesübersicht habe ich schon reduziert bekommen. Bei der anderen Tabelle weiss ich noch nicht, wie ich die Binden soll, ohne die Mitarbeiter_ID. Mal sehen, ob die Redundanz da noch raus bekomme. Danke für den Hinweis. Des weiteren habe ich das andere Problem zum Glück auch endlich in den Griff bekommen.

 

[Tommi]

Hi,

deine Tabelle Tagesübersicht würde ich allerdings ohne Angabe der Mitarbeiter als eigenständige Tabelle stehen lassen.
Da du diese Tabelle ja für Auswertungen nutzt, ist es hier auch völlig legitim bereits errechnete Werte direkt hinen zu schreiben, damit diese nicht bei jeder Abfrage neu berechnet werden.

Das Arbeiten mit dieser Tabelle, in der die Werte bereits ermittelt enthalten sind, bietet für Auswertunge zwei erhebliche Vorteile:
1. es kann hier ein Index erstellt werden, mit dem die Abfrageperformance deutlich erhöht werden kann.
Dieser kann entpsrechend die vorliegenden Werte direkt mitliefern
2. Eine solche Tabelle erleichtert es, Gruppierungen über Zeiträume vorzunehmen (Jahr, Monat, KW) und entsprechende Grenzwerte gleich mit zu ermitteln.
(Anfang oder Ende eines Monats bzw. auch eine KW - was manchmal wichtige Informationen für Abfrageverknüpfungen sind)

Die Angaben der Mitarbeiter kannst du dann mit einem CROSS JOIN hinzufügen.

Ein Beispiel (in eckigen Klammern stehen optionale Statements):

SELECT [...]
FROM dbo.Tagesübersicht TU
CROSS JOIN dbo.Mitarbeiter Mit
LEFT OUTER JOIN dbo.Vertrag Vert
    ON Mit.MA_ID=Vert.MA_ID
    [AND TU.Datum BETWEEN Vert.Datum_Von AND ISNULL(Vert.Datum_Bis, GETDATE()]
LEFT OUTER JOIN dbo.ZeitenTabelle Arb
    ON Arb.Vertrag_ID=Vert.Vertrag_ID
    AND Arb.Datum=Vert.Datum (--> ??)
[WHERE TU.Datum BETWEEN @vonDatum AND @bisDatum]

Eine Bearbeitung über eine Schleife ist aus meiner Sicht nicht notwendig.

Viele Grüße,
Tommi

 

[Ruediger007]

Hi Tommi!

Erstmal herzlichen Dank für die erneute sehr hilfriche Unterstützung! Ich habe alles umgebaut und es läuft einwandfrei. Ich habe noch eine Verständnisfrage.

1. es kann hier ein Index erstellt werden, mit dem die Abfrageperformance deutlich erhöht werden kann.
Dieser kann entpsrechend die vorliegenden Werte direkt mitliefern

Könntest du mir vielleicht den Punkt noch etwas erläutern, ich verstehe ihn nämlich nicht so richtig. Meinst du damit, dass ab dem Moment, wo ich den Cross Join durchführe die Daten in der neuen Tabelle an einen Index gebunden sind und somit alle Abfragen auf die Tabelle schneller durchgeführt werden können?

Beste Grüße
Ruediger

 

[Tommi]

Hi,

ein Index ist Bestandteil einer Tabelle und muss für diese explizit angelegt werden.
Um es mal einfach auszudrücken ist ein Index ein sortierter Katalog, über den Tabelleneinträge schnell und sicher identifiziert werden können.

Indizes sind ganz wichtiger Bestandteil bei der Implementierung von Datenbanken.
Genauere Informationen hierzu bekommst du natürlich aus dem Internet.

Ich habe hier ein paar interessante Links, in denen das Thema Indizes noch einmal deutlicher beschrieben wird:
http://www.szweb.de/Datenbankperformance/Indexplanung-Seite-225.html
http://msdn.microsoft.com/de-de/library/ms190197(v=sql.105).aspx
http://msdn.microsoft.com/de-de/library/ms188783(v=sql.90).aspx

und natürlich

http://de.wikipedia.org/wiki/Datenbankindex (dies aber nur Bedingt - kein guter Artikel, gibt aber ein wenig Überblick)

Aber generell ging es mit eher darum die Vorteile einer statischen Tabelle mit bereits vorberechneten Daten aufzuführen.
Du wirst für deine kleine Auswertung einen solchen Index nicht benötigen, aber es ist natütlich wichtig zu wissen, dass diese Möglichkeiten vorhanden sind!

Ob du wirklich einen Index für eine Abfrage benötigst, kannst du aus dem Ausführungsplan ermitteln, den du die im SQL Server Management Studio anzeigen lassen kannst.
Hier wird ein fehlender Index sogar mit einer CREATE-Befehl angezeigt und vorgeschlagen.


Viele Grüße,
Tommi

 

[ukulele]

Im wesentlichen kann man sagen ein Index auf einer bestimmten Spalte macht immer dann Sinn, wenn du im WHERE Teil oder im ORDER BY Teil einer Abfrage Bezug auf die Werte einer Spalte nimmst und die Menge der Daten nicht unerheblich ist. Wenn du deine Abfrage mal mit mehreren kompletten Jahren testest solltest du das relativ schnell herausfinden können.