Werte generieren

[Brodi87]

Ich habe eine Tabelle aus der (es geht um eine Artikelstatistik) das Jahr mit entsprechenden Monat ausgelesen werden sollen. Leider sind nicht alle Monate vorhanden. z.B. wurde ein Artikel nur im Mai und Juli verkauft.

Mache ich jetzt eine normale Abfrage, zeigt es mir (logisch) nur die Werte für Mai und Juli an.

Nun möchte ich aber das es jeden Monat anzeigt, aber mit entsprechenden 0-Werten in den Spalten.

Ich könnt es über eine Hilfstabelle joinen, aber das ist in diesem Fall auch nicht möglich.

Nun bastel ich an einer Schleife die das ganze generieren soll...leider bisher ohne Erfolg...

Habt ihr Tipps und vorschläge wie ich das realisier? :confused:

(MS SQL Server 2005)

[flashpixx]

Mache eine Tabelle in der die Monate stehen und verbinde diese via LEFT JOIN mit den Daten, ggf musst Du dann noch das NULL zu 0 ersetzen, was ich aber mit einem IF realisieren lässt

[Brodi87]

ja...aber genau das möchte ich nicht! Da ich aus verschiedenen Gründen keine "Hilfs"-Tabelle erstellen kann!

Ich suche deswegen eine andere Möglichkeit!

[flashpixx]

Als genereller Ansatz sollte man bei Datenbanken immer "mengenorientiert" arbeiten, da dies schneller abgearbeitet werden kann, als ein sequentieller Zugriff.

temporäre Tabellen oder Stored Procedures wären von der Datenbank eine Möglichkeit, das nachträgliche Einfügen durch die Anwendung halte ich nicht für sinnvoll

[dr.dimitri]

Eine andere Möglichkeit ist es, Dir die benötigte Menge über ein Subselect zur Verfügung zu stellen. Allerdings kenne ich die Datumsfunktionen von MSSQL nicht, aber in Oracle liefert dieses SQL z.B. alle Monate eines Jahres:

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YY'), ROWNUM - 1) datum

  FROM all_objects

 WHERE ROWNUM < 13

Dim

[Brodi87]

es gibt Dateadd...

leider weiß ich nicht wie man damit es realisieren könnte...

[streffin]

afaik wirste über eine temp tabelle nicht rum kommen in mssql.

Aber, es gibt unter mssql eine alternative zur #temp_table.

declare @tbl table (id int, dateval datetime, monthName varchar(50))


declare @i int

set @i = 0

while @i < 12

BEGIN

   insert into @tbl 

   Values (

                 @i, 

                 dateadd(month, @i, '2010-01-01'), 

                 datename(month, dateadd(month, @i, '2010-01-01'))

            )

   set @i = @i +1

END

Das würd dir die tabelle zum left / right joinen liefern, wofür du keine temporäre tabelle anlegen müsstest.

mit den table variablen kannst du arbeiten wie mit jeder anderen tabelle (auch syntaktisch), ich denke das würde sich für dich hier anbieten.

Ansonsten ...

das einzige was mir als alternative einfallen würde, wäre die tempdb, aber da du keine temporären tabellen anlegen möchtest, fällt das wohl aus.

Wobei ich das nicht ganz versteh, 12 rows mit 1-3 columns .... warum kannste das nich einfach in ne temp table schmeissen das ganze ?

(btw, die table variablen sind nicht wirklich was anders, werden halt schneller aus dem speicher geschmissen als tabellen in der tempdb)

Gruß

Sven

Bearbeitet 10. August 2010 von streffin

[streffin]

Hm eventuell hab ich das ganze falsch gelesen .....

Du sagst keine Hilfstabelle.....

Hilfstabelle ist für mich eine dauerhaft bestehende Tabelle in der Datenbank.

d.h.

Wenn ich das jetzt nochmal quer lese, dann kannst au auch eine temp Table erstellen.

Temp Tables unter Mssql sind Tabellen, die entweder nur für die aktuelle Session bestehen bleiben, oder "übergeordnete" Temp Tables, die nicht sofort weggeschmissen werden (ein paar minuten sind sie noch da in der Regel)

Den Unterschied in code :

create table #temp (id int, bla varchar(255))

insert into #temp

Values (1, 'bla)


create table ##andere_art_temp (id int, bla varchar(255))

etc

# = "lokale" temp table, nur für die aktuelle Session ## = "globale" temp table, is ein paar minuten da Die Tabellen kannst du benutzen wie jede andere Table auch. Der Unterschied ist, die #temp ist nach der aktuellen Session in der du sie erstellst weg. Komplett mit inhalt und allem. die ##temp wird für kurze Zeit vorgehalten, was heist, dass du in z.b. SSIS Packages da IDs drin speichern kannst, die dann von anderen Steps im Package benutzt werden können. Aber die ##temp Tabellen sind nach ein paar Minuten auch im Datennirvana. Also wenn dein einziges Problem darin besteht, dass du keine feste dauerhafte Hilfstabelle für die Monate erstellen kannst / darfst (wär au recht ja .... doof), kannste dir auch genausogut ne #temp erstellen, und mit der Tabelle in deinem query umgehen wie mit jeder anderen Tabelle in der Datenbank. Mit der besonderheit, dass du nicht sicher sein kannst, ob die Tabelle eventuell nich besteht, oder nicht mehr.

IF object_id('tempdb.dbo.##temp') IS NOT NULL

BEGIN

        DROP TABLE ##temp

END

Würd ich daher zu Begin des Querys anraten.

Ansonsten, die table variablen sind in der Hinsicht sehr unkompliziert, und ich glaub du hasts damit einfacher.

Das war jetzt eher der Vollständigkeit halber der Post.

Ps :

Ich hab recht bescheidenen Arbeitszeiten, ich fang spät an, und komm sehr spät heim, daher verschiebt sich mein Schlafrythmus a bissl ins Arbeitslose

Gruß

Sven

Bearbeitet 10. August 2010 von streffin

[Brodi87]

okay...das habe ich soweit verstanden und das ist denk ich auch das was ich suche. Nur ist mir jetzt irgendwie unklar wie du in dieser Temp-Tabelle mehrere Zeilen schreiben willst und diese dann anschließend in der eigentlichen Abfrage einbinden willst.

Das ist mit meinem Anfängerwissen noch etwas zu hoch!

Also gehen wir mal von einer simplen Ausgangsabfrage aus:

 select Artikelnummer, Datum, sum(Menge)


from Table1


where Jahr = '2009'


group by Artikelnummer, Datum

[streffin]

mehrere Zeilen schreibst du entweder 12 einzelne inserts, oder ne schleife.

declare @tbl table (id int, dateval datetime, monthName varchar(50))

declare @i int

set @i = 0

while @i < 12

BEGIN

   insert into @tbl 

   Values (

                 @i, 

                 dateadd(month, @i, '2010-01-01'), 

                 datename(month, dateadd(month, @i, '2010-01-01'))

            )

   set @i = @i +1

END

Was deine einfache Abfrage angeht, ohne genau zu wissen was du vor hast :

select tab.Artikelnummer, temp.monthName, tab.sum(Menge)

from @tbl temp

   LEFT JOIN Table1 tab on MONTH(tab.datum) = MONTH(temp.dateval)

where tab.Jahr = '2009'

group by tab.Artikelnummer, temp.monthName, MONTH(tab.datum)

ORDER BY MONTH(tab.datum)

[Brodi87]

ich erklär es mal grob...

Man soll einen Artikel und ein Jahr auswählen können! Dann werden alle Monate des Jahres mit den entsprechenden Umsatzzahlen ausgegeben.

Aber es soll auch die Monate anzeigen, wo nichts passiert ist. Und in der Menge steht dann eben '0'! Da diese Monate, mit Umsatz '0' nicht auftauchen, müssen diese Monate, nur für diese Abfrage, künstlich generiert werden.

so...aber es funktioniert leider immer noch nicht wie gewünscht, da es immer noch nicht alle Monate ausgibt...

[streffin]

Ich bau jetzt keine komplette DB nach, aber das hier sollte zumindest ne ganz brauchbare Vorlage für dich darstellen.

DECLARE @monate TABLE(datum datetime, NAME VARCHAR(50))

DECLARE @table TABLE(artikel VARCHAR(50),  datum DATETIME, menge INT)



DECLARE @i INT = 0

WHILE @i < 12

BEGIN

	INSERT INTO @monate 

	VALUES (

				DATEADD(MONTH, @i, '2010-01-01'), 

				DATENAME(MONTH,DATEADD(MONTH, @i, '2010-01-01'))

			)

	set @i = @i +1 

END


INSERT INTO @table

VALUES ('aepfel', '2010-01-01', 5),

	   ('brinen', '2010-01-01', 3),

	   ('aepfel', '2010-01-01', 2),

	   ('orangen', '2009-01-01',5),

	   ('aepfel', '2010-03-09', 1)


DECLARE @jahr INT

DECLARE @artikel VARCHAR(50) 


SET @jahr = 2010

SET @artikel = 'aepfel'


SELECT YEAR(mon.datum) [Jahr], mon.[NAME] [Monat], @artikel [Artikel],  isnull(sub_query.menge,0) [Anzahl] 

FROM @monate mon 

	LEFT JOIN 

	(

		SELECT SUM(menge) [menge], datum

		FROM @table

		WHERE YEAR(datum) = @jahr

		AND	  artikel = @artikel

		GROUP BY artikel, datum

	) sub_query ON YEAR(sub_query.datum) = YEAR(mon.datum)

				AND MONTH (sub_query.datum) = MONTH(mon.datum)

Das kannst du dir so anpassen, wie du es dann brauchst.

Also über die Artikel ID gehen usw.

Nebenbei, das bietet sich eigentlich an daraus eine Stored Procedure zu machen, mit den 2 übergabewerten Jahr und Artikel.

Gruß

Sven

[Brodi87]

Das ist es! :uli

Wow...Dank dir vielmals!

PS: Dafür das du "arbeitslos" bist, hast du es aber echt drauf!

[streffin]

Bin ich nicht, ich hab nur ********ene Arbeitszeiten, was zu führt dass wie ein Arbeitsloser schlafen geh

(so gegen 3uhr früh, da is normal alles was in Lohn und Brot steht schon halb am wieder aufstehn)