Archiv der Kategorie: SQL

IDENTITY_INSERT MSSQL

Explizit einen spezifischen Datensatz in einer Tabelle mit eingeschalteter IDENTITY eintragen.

SET IDENTITY_INSERT dbo.str_fachgruppe ON
 
INSERT INTO	dbo.str_fachgruppe
(fachgruppe_id, bereich_id, fachgruppe)		
VALUES 
(638, 141,'EHD Manager') 
 
SET IDENTITY_INSERT  dbo.str_fachgruppe OFF

MSSQL while Loop

--Declare variable to store values returned by FETCH
DECLARE @f_id VARCHAR (10);
 
DECLARE myCursor CURSOR FOR
	SELECT     form_id
	FROM         dbo.str_formular
	WHERE     (bereich_id = 46) AND (workflow = 499)
		AND	form_id NOT IN (SELECT form_id
							FROM   dbo.str_formular_bereich
							WHERE     (bereichs_id = 46));
 
OPEN myCursor;
 
FETCH NEXT FROM myCursor
INTO @f_id;
 
WHILE @@FETCH_STATUS = 0
	BEGIN
		Print 'f_id: ' + @f_id
 
		INSERT INTO dbo.str_formular_bereich
		(form_id, bereichs_id)
		VALUES
		(@f_id, 46);
 
		FETCH NEXT FROM myCursor
		INTO @f_id;
 
	END
CLOSE myCursor;
DEALLOCATE myCursor;

Alle NICHT in der Lookup Tabelle (str_formular_baum) vorhandenen Einträge nachtragen.

--Declare variable to store values returned by FETCH
DECLARE @f_id VARCHAR (10);
DECLARE @f_name VARCHAR (100);
DECLARE myCursor CURSOR FOR
	SELECT     form_id, formname
	FROM         dbo.str_formular
	WHERE     (bereich_id = 46) AND (workflow = 499)
		AND	form_id NOT IN (SELECT form_id
							FROM   dbo.str_formular_baum
							WHERE     (parent_id = 623));
 
OPEN myCursor;
 
FETCH NEXT FROM myCursor
INTO @f_id, @f_name;
 
WHILE @@FETCH_STATUS = 0
	BEGIN
		Print 'f_id: ' + @f_id + 'f_name ' +@f_name
 
		INSERT INTO dbo.str_formular_baum
		(form_id, parent_id)
		VALUES
		(@f_id, 623);
 
		FETCH NEXT FROM myCursor
		INTO @f_id, @f_name;
 
	END
CLOSE myCursor;
DEALLOCATE myCursor;

Datensätze anhand des Datums eingrenzen

Das folgende Statement gibt das Aktuelle Jahr aus.

SELECT YEAR(GETDATE()) AS "Year"

Mit dem folgenden Statement werden alle Datensätze der Tabelle hlp_historie von den Jahr 2011 angezeigt.

SELECT * 
FROM hlp_historie 
WHERE YEAR(datum) = 2011

SQL Tutorial

SQL text zu money casten

Systembedingt liegt eine Tabelle mit einem Feld vom Typ Text vor. In diesem Feld ist ein Zahlenwert bspw. 100,40 gespeichert.

Wie kann man mit diesem Wert rechnen? Also bspw. summieren.

SELECT SUM(CAST(CAST(feld7 AS VARCHAR(10)) AS money)) AS  sumPreis
FROM	str_formular_daten
WHERE	unterauftrag_id = 230790 
	AND	form_id = 816

SQL id aus einem Textfeld

Ausgangslage:
Ich haben 2 DB-Tabellen die ich “joinen” möchte. In Tabelle A ist ein Feld welches aus historischen und architektonischen Gründen vom Typ “Text” ist und den Fremdschlüssel auf Tabelle B enthält. Ja ich bin mir sicher das in dem Feld auf jeden Fall nur ein int drin steht.
Wenn man nun versucht das Feld zu einem int zu casten dann meldet der MS-Sql Server :”Die explizite Konvertierung des text-Datentyps in int ist nicht zulässig.
Folgender kleiner Trick führt dennoch zum Erfolg.

  1. Caste das Feld zum varchar()
  2. Caste das Ergebnis zu int

Dann Schluckt der SQL-Server das auch und ich kann die Tabelle B dran joinen.

SELECT	form_id, feld5 AS artikel_id, feld7 AS cnt, b.preis
FROM 	tabelleA a
JOIN 	tabelleB b ON b.id = CAST(CAST(feld5 AS VARCHAR(10)) AS INT)
WHERE 	form_id = 1164

Resource:

http://msdn.microsoft.com/de-de/library/ms187928.aspx

Rechnen mit SQL

Mit sql kann man auch prima (SQL)-Serverseitig rechnen. So kann Rechenpower auf den SQLServer verlagern und vor allem die Datenmenge die zurückgegeben wird verringern. Weiterhin kann dadurch die Komplexität des ausführenden Codes verringert werden. Angenommen wir haben eine Tabelle die Daten über kostenstellen (kst), Menge in kg von Produkt x und der Kg-Preis enthält. Die folgende Abfrage:

SELECT   id, kst, menge, kg_preis, materialkosten, auftrag_id, lieferdatum
FROM     dbo.hlp_han_trockeneis_stats
ORDER BY kst

liefert bspw. diese Ausgangsdatensätze.
181 2000 1400 5 7000 201012665 2010-06-26 00:00:00.000
203 2000 800 10 8000 201019020 2010-07-02 00:00:00.000
202 2001 800 5 4000 201018583 2010-06-17 00:00:00.000
145 2001 500 5 2500 201013950 2010-06-17 00:00:00.000
151 2003 800 5 4000 201019455 2010-07-01 00:00:00.000
156 2003 1200 5 6000 201017137 2010-06-19 00:00:00.000
187 2004 1200 5 6000 201010679 2010-07-05 00:00:00.000
127 2005 1500 5 7500 201019704 2010-06-24 00:00:00.000

Zählen der Datensätze zu einer kst.

SELECT kst, COUNT(*) AS cnt
FROM hlp_han_trockeneis_stats
GROUP BY kst

Die Summe der Spalte Menge pro Kostenstelle ermitteln.

SELECT kst, SUM (menge) AS sum_Menge
FROM hlp_han_trockeneis_stats
GROUP BY kst

Jetzt brauchen wir noch den Durchschnitt des KgPreises. Also die Summe des Kg-Preises durch die Anzahl der Datensätze pro kst.

SELECT kst, SUM(kg_preis) / COUNT(*) AS sum_kg_preis
FROM hlp_han_trockeneis_stats
GROUP BY kst

Und nun alles zusammen.

SELECT kst, COUNT(*) AS cnt, SUM(menge) AS sum_menge, SUM(kg_preis) / COUNT(*) AS sum_kg_preis, SUM(materialkosten) sum_material
FROM dbo.hlp_han_trockeneis_stats
GROUP BY kst

Erzeugt folgende Datensätze

2000    2    2200    7,5    15000

2001    2    1300    5    6500

2003    2    2000    5    10000

2004    1    1200    5    6000

2005    1    1500    5    7500