ComputerBase Menü
Aktuelles

SQL MSSQL-Tabelle erweitern mit Werten aus einer Schleife und Variable

Agt.Romanoff

Agt.Romanoff

Lt. Junior Grade
Registriert
Aug. 2011
Beiträge
288
Hallo Leute,

durch ein Auswertungstool muss ich eine Abfrage erweitern mit bestimmten Zahlen.
Es sollen die Pauschaleinnahmen und dagegen gebuchten Stunden verglichen werden.
Die Tabelle enthält folgende Spalten:
- Vorgangsnummer
- netto-gesamt
- pauschale-beginnt
und pauschale-Ende

Ich muss die Abfrage nun so erweitern, dass er mir für einen Vorgang jeden Monat eine Zeile ausgibt mit dem gleichen Werten. Dazu am besten noch eine zusätzliche Spalte mit Abrechnungsmonat.

Das is die ungefähre Ausgangstabelle

[table="width: 500, class: grid"]
[tr]
[td]Vorgang[/td]
[td]netto_gesamt[/td]
[td]pauschale_beginnt[/td]
[td]pauschale_ende[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[/tr]
[tr]
[td]2[/td]
[td]300[/td]
[td]01.08.2016[/td]
[td]28.02.2017[/td]
[/tr]
[tr]
[td]3[/td]
[td]450[/td]
[td]01.10.2016[/td]
[td]31.01.2017[/td]
[/tr]
[/table]

aus dem ganzen soll dann ungefähr sowas werden:

[table="width: 500, class: grid"]
[tr]
[td]Vorgang[/td]
[td]netto_gesamt[/td]
[td]pauschale_beginnt[/td]
[td]pauschale_ende[/td]
[td]Abrechnungsmonat[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]04.2016[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]05.2016[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]06.2016[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]07.2016[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]08.2016[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]09.2016[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]10.2016[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]11.2016[/td]
[/tr]
[tr]
[td]1[/td]
[td]100[/td]
[td]01.04.2016[/td]
[td]31.12.2016[/td]
[td]11.2016[/td]
[/tr]
[tr]
[td]2[/td]
[td]300[/td]
[td]01.08.2016[/td]
[td]28.02.2017[/td]
[td]08.2016[/td]
[/tr]
[tr]
[td]2[/td]
[td]300[/td]
[td]01.08.2016[/td]
[td]28.02.2017[/td]
[td]09.2016[/td]
[/tr]
[tr]
[td]2[/td]
[td]300[/td]
[td]01.08.2016[/td]
[td]28.02.2017[/td]
[td]10.2016[/td]
[/tr]
[tr]
[td]2[/td]
[td]300[/td]
[td]01.08.2016[/td]
[td]28.02.2017[/td]
[td]11.2016[/td]
[/tr]
[tr]
[td]2[/td]
[td]300[/td]
[td]01.08.2016[/td]
[td]28.02.2017[/td]
[td]12.2016[/td]
[/tr]
[tr]
[td]2[/td]
[td]300[/td]
[td]01.08.2016[/td]
[td]28.02.2017[/td]
[td]01.2017[/td]
[/tr]
[tr]
[td]2[/td]
[td]300[/td]
[td]01.08.2016[/td]
[td]28.02.2017[/td]
[td]02.2017[/td]
[/tr]
[tr]
[td]3[/td]
[td]450[/td]
[td]01.10.2016[/td]
[td]31.01.2017[/td]
[td]10.2016[/td]
[/tr]
[tr]
[td]3[/td]
[td]450[/td]
[td]01.10.2016[/td]
[td]31.01.2017[/td]
[td]11.2016[/td]
[/tr]
[tr]
[td]3[/td]
[td]450[/td]
[td]01.10.2016[/td]
[td]31.01.2017[/td]
[td]12.2016[/td]
[/tr]
[tr]
[td]3[/td]
[td]450[/td]
[td]01.10.2016[/td]
[td]31.01.2017[/td]
[td]01.2017[/td]
[/tr]
[/table]

Die Spalte Abrechnungsmonat kann natürlich auch mit einem richtigen Datum befühlt werden, also z.B. immer der letzte des Monats (Eomonth als Funktion oder so).
Ich habe das ganze schon mit Schleife, Variable als Monat aus der Tabelle und insert into probiert, aber so ganz komme ich da nicht voran.

Danke schon mal allen die Mithelfen

Grüße Glühkohle
 
Soll die Tabelle so bleiben wie die Ausgangstabelle und nur die Abfrage liefert das was in der erweiterten Tabelle steht?

Oder soll die Tabelle grundsätzlich erweitert werden?
 
Die Ausgangstabelle ist bereits aus einer SQL-Abfrage entstanden, somit gibt es keine Ursprungstabelle in diesem Sinne (Die Daten kommen aus verschiedenen Tabellen, da sich Vorgangsnummer in einer anderen Tabelle befindet, als die Daten wie Pauschale_beginn, Pauschale_ende etc).
Mein erster Gedanke wäre hier die Tabelle, welche aus der Abfrage entstanden ist in eine Subquery zu bauen und damit die entsprechenden Spalten ansprechen zu können.
Falls sich dies in einer Schleife abbilden lässt.
 
Wäre es dann nicht sinnvoll die ursprüngliche Abfrage zu ändern?
Ergänzung ()

Falls es dir jedoch nicht möglich sein sollte, die ursprüngliche Abfrage zu ändern, vielleicht eine procedure in der art (Pseudo):

Code: 
foreach Zeile in Ausgangstabelle
{
  for ( i = pauschale_beginnt; i <= pauschale_ende; addmonth(i))
  {
    insert into temporaray table
    values (vorgang, netto_gesamt, pauschale_beginnt, pauschale_ende, i);
  }
}

und dann natürlich die temporary mit der Ausgangstabelle joinen...
 
Zuletzt bearbeitet:
Eine Änderung der eigentliche Abfrage würde auch nix bringen, da auch da nicht die einzelnen Abrechnungsmonate angegeben sind.

Wie ich das ganze machen könnte über eine Schleife ist mir schon bewusst, aber leider fehlt mir dafür der richtige Syntax im SQL
 
Syntax kann man sich bei TSQL (MSSQL) ziemlich einfach zusammengoogeln... (soweit auch gute Beispiele vorhanden)

Grundsätzlich MSDN

In deinem Fall, deine komplettes Konstrukt an passenden stellen mit BEGIN/END ergänzen

Eine Variable kannst auch als Tabelle deklariert werden BEISPIEL C

Und PROCEDURE wäre für die Schleife super, kann man ja auch temporär erstellen

Das ganze dann etwa so:

Code: 
DECLARE TABLE VARIABLE Tablevariable;

CREATE TEMPORARY PROCEDURE; //ggf ja auch permanent wenns häufiger gebraucht wird

EXECUTE PROCEDURE mit Ausgangstabelle INTO Tablevariable;

SELECT FROM Ausgangstabelle
  INNER JOIN Tablevariable;

Alles dann in eine Abfrage gepackt und natürlich mit den passenden BEGIN/END etc. ergänzt.

Will dir ja nicht deine Arbeit abnehmen, deswegen wieder nur Pseudo ^^
Außerdem lernt man viel beim probieren :-)


P.S.: Ich kann mir auch vorstellen, dass es noch einfachere Möglichkeiten gibt, aber die Variante sollte auch gut machbar sein.
 
Zuletzt bearbeitet:
Ich habe mich damit schon mehrere Stunden damit befasst, aber so richtig will es nicht funktionieren. Irgendwo ist da ein kleiner Haken, den ich noch nicht ganz durchschaut habe.

Meine Abfrage bisher lautet:
Code: 
select	Vertrag.vorgangs_nr,
		Vertrags_pos.net_gesamt_pro_monat,
		Vertrags_pos.pauschale_beginnt,
		datediff(m, Vertrags_pos.pauschale_beginnt, Vertrags_pos.Pauschale_Ende),
		Vertrags_pos.Pauschale_Ende
		
from	Vertrag  inner join 
		Vertrags_pos on 
		Vertrags_pos.fk_Vertrag_id =
		vertraege_.pk_Vertrag_id

Und die Tabelle dazu sieht bisher (nur ein Ausschnitt):
[table="width: 500"]
[tr]
[td]Vorgangs_nr[/td]
[td]net_gesamt_pro_monat[/td]
[td]pauschale_beginnt[/td]
[td]Monate[/td]
[td]pauschale_ende[/td]
[/tr]
[tr]
[td]000015[/td]
[td]750,00 €[/td]
[td]01.04.2013[/td]
[td]53[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]000039[/td]
[td]290,41 €[/td]
[td]01.04.2014[/td]
[td]30[/td]
[td]30.09.2016[/td]
[/tr]
[tr]
[td]000039[/td]
[td]80,78 €[/td]
[td]01.04.2014[/td]
[td]30[/td]
[td]30.09.2016[/td]
[/tr]
[tr]
[td]020005[/td]
[td]167,70 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]020005[/td]
[td]233,00 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]020005[/td]
[td]146,23 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]020005[/td]
[td]315,00 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]020008[/td]
[td]669,00 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]030094[/td]
[td]200,00 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]030095[/td]
[td]103,75 €[/td]
[td]01.01.2014[/td]
[td]44[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]040016[/td]
[td]337,00 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]050010[/td]
[td]91,00 €[/td]
[td]01.01.2014[/td]
[td]44[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]060007[/td]
[td]950,00 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]060021[/td]
[td]75,00 €[/td]
[td]01.03.2014[/td]
[td]42[/td]
[td]25.08.2017[/td]
[/tr]
[tr]
[td]060028[/td]
[td]611,00 €[/td]
[td]01.04.2014[/td]
[td]41[/td]
[td]25.08.2017[/td]
[/tr]
[/table]

Ich versuche noch das ganze bis dahin einzufügen wie ich es verstanden habe und poste das mal noch.
Hatte mich auch schon zu Cursor belesen, aber auch da bin ich nicht auf einen grünen Zweig gekommen bzw. wusste nicht wie der Cursor verwendet wird.
Ergänzung ()

So sieht es nun aus mit den Hilfen aus dem Internet:

Code: 
Create Table #TempTable (RowID int IDENTITY (1,1), alle Spalten mit Datentypen aus der tabelle unten, Abrechnungsmonat)

DECLARE @NumberRecords int, @RowCount int
/* vorgangs_nr kann auch Buchstaben enthalten*/
DECLARE @vorgangs_nr varchar (7), @net_gesamt_pro_monat float, @pauschale_beginnt date, @Monate_dazwischen int, @Pauschale_ende date, @Abrechnungsmonat date

Insert Into #TempTable (vorgangs_nr, net_gesamt_pro_monat, pauschale_beginnt, Monate_dazwischen, Pauschale_ende)
Select	Vertrag.vorgangs_nr,
		Vertrags_pos.net_gesamt_pro_monat,
		Vertrags_pos.pauschale_beginnt,
		datediff(m, Vertrags_pos.pauschale_beginnt, Vertrags_pos.Pauschale_Ende) as Monate_dazwischen,
		Vertrags_pos.Pauschale_Ende
		
from	Vertrag  inner join 
		Vertrags_pos on 
		Vertrags_pos.fk_Vertrag_id =
		vertraege_.pk_Vertrag_id

SET @NumberRecords = @Monate_dazwischen
SET @rowCount = 1

While @RowCount <= @NumberRecords
Begin
	Select @vorgangs_nr = vorgangs_nr, @net_gesamt_pro_monat = net_gesamt_pro_monat, @pauschale_beginnt = pauschale_beginnt, @Monate_dazwischen = Monate_dazwischen, @Pauschale_ende = Pauschale_ende, @Abrechnungsmonat = Abrechnungsmonat)
	from #TempTable
	where RowID = @RowCount

	Exec Abrechnungsmonat = dateadd(m, @rowcount, Abrechnungsmonat)

	set @RowCount = @RowCount + 1
End

Drop Table #TempTable

Muss ich die Procedure vorher komplett definieren oder wie funktioniert das
 
Zuletzt bearbeitet:
*Edit:Procedure ist keine Pflicht, aber hilfreich wenn man es häufiger braucht.

Ansonsten nested Loop*

Hier noch mal ein (nested-)LOOP-Beispiel:

Code: 
declare @i int, @j int
select @i = 0, @j = 0
while @i < 3 begin
    while @j < 3 begin
        select @i as i, @j as j
        set @j = @j + 1
    end
    set @i = @i + 1
    set @j = 0
end


*Edit2: Ein LOOP im FOREACH stil könnte auch helfen:

Code: 
DECLARE @i int
DECLARE @PractitionerId int
DECLARE @numrows int
DECLARE @Practitioner TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , PractitionerId int
)

INSERT @Practitioner
SELECT distinct PractitionerId FROM Practitioner

SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM Practitioner)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(idx) FROM Practitioner))
    BEGIN

        SET @PractitionerId = (SELECT PractitionerId FROM @Practitioner WHERE idx = @i)

        --Do something with Id here
        PRINT @PractitionerId

        SET @i = @i + 1
    END
 
Zuletzt bearbeitet:
Wie eine derartige Schleife aussehen kann ist mir eigentlich klar.
Die Frage ist ob man einen Wert einer Spalte in eine Variable schreiben kann und dann den berechneten Wert wieder in eine Spalte schreiben kann.
Ich schaue mir deine Beispiele mal an und melde mich.
Ist denn meine Variante oben weitestgehend richtig abgesehen von der Berechnung.
 
Also, hab mal was zusammengefrickelt, ist absolut ungetestet, da ich momentan kein MSSQL zur Verfügung habe:

--> Ohne PROCEDURE geschrieben
--> Ich hab eh bestimmt irgendetwas in der syntax vergessen ^^

Code: 
DECLARE @i int, @j int		--Zählervariablen für die Schleifen
declare @months_count int	--Variable für die Monate Pro Zeile
declare @datasets_count int	--Variable für Anzahl der Datensätze
declare @begin_date date	--Variable für Startmonat
declare @vorgangnr varchar(7)		--Variable für Vorgangsnummer

declare @datatable table(	--Da werden die Daten aus deiner Abfrage reingepackt
  rownumber int,
  vorgangs_nr varchar(7),
  net_gesamt_pro_monat float,
  pauschale_beginnt date,
  monate_dazwischen int,
  pauschale_Ende date)

declare @temptable table(	--Tabelle die später gejoint wird
  Vorgang varchar(7),
  Abrechnungsmonat date)
insert into @datatable(
  rownumber,
  vorgangs_nr,
  net_gesamt_pro_monat,
  pauschale_beginnt,
  monate_dazwischen,
  pauschale_Ende)
select
  row_number() over(order by Vertrag.vorgangs_nr asc) as rownumber,        --Feste Rownummer vergeben, damit nichts durcheinanderkommt
  Vertrag.vorgangs_nr as vorgangs_nr,
  Vertrags_pos.net_gesamt_pro_monat as net_gesamt_pro_monat,
  Vertrags_pos.pauschale_beginnt as pauschale_beginnt,
  datediff(m, Vertrags_pos.pauschale_beginnt, Vertrags_pos.Pauschale_Ende) as monate_dazwischen,
  Vertrags_pos.Pauschale_Ende as pauschale_Ende
from Vertrag
  inner join Vertrags_pos on Vertrags_pos.fk_Vertrag_id = Vertrag.pk_Vertrag_id

  set @i = 1,
  set @j = 0,
  set @datasets = (select count(*) from @datatable)

-- Ab hier beginnt der spaß

while @i <= @datasets
begin
    set @months_count = (select monate_dazwischen from @datatable where rownumber = @i),
    set @begin_date = (select pauschale_beginnt from @datatable where rownumber = @i),
    set @vorgangnr = (select vorgangs_nr from @datatable where rownumber = @i)
  while @j < @months_count
  begin
    insert into @temptable(vorgang, abrechnungsmonat)
      values(@vorgangnr, eomonth(@begin_date, @j))
    set @j = @j + 1
  end
  set @i = @i + 1
  set @j = 0
end

select      --Abfrage für die finale Ausgabe
  a.vorgangs_nr,
  a.net_gesamt_pro_monat,
  a.pauschale_beginnt,
  a.pauschale_Ende,
  b.abrechnungsmonat
from @datatable as a
  inner join @temptable as b on a.vorgangs_nr = b.vorgang
 
Zuletzt bearbeitet:
Auf den ersten Blick hat alles super funktioniert.
Musste zwar noch ein Group by mit einfügen, da er mit bei einigen Zeilen alles zweimal angezeigt hat, aber ansonsten ist es genau das was ich gesucht habe.
Ich danke dir tausendmal, da ich jetzt damit weiterarbeiten kann.
 
You're welcome.

Überrascht mich, dass es sozusagen direkt funktioniert hat, habe seit 2 Jahren nicht mehr mit MSSQL gearbeitet :freak:

Hoffentlich ist es auch performant genug, weil ich mir durchaus vorstellen könnte, dass es performantere Möglichkeiten gibt.
 
Du musst dich einloggen oder registrieren, um hier zu antworten.
Zurück
Oben

Willkommen auf ComputerBase!

Mit Werbung weiterlesen

Besuche ComputerBase wie gewohnt mit Werbung und Tracking. Die Zustimmung ist jederzeit widerrufbar.

Details im Privacy Center und in der Liste unserer Partner. Ein Widerruf ist möglich in der Datenschutzerklärung.

… oder ComputerBase Pro bestellen

Nutze ComputerBase ohne Werbebanner, Video-Ads und Werbetracking schon für 4 €/Monat oder 36 €/Jahr.

Mehr zu ComputerBase Pro

Bereits Pro-Nutzer? Hier anmelden.

Tracking: Wir und unsere 158 Partner verarbeiten personenbezogene Daten, indem wir mit auf Ihrem Gerät gespeicherten Informationen (z. B. eindeutige Kennungen in Cookies) ein Nutzungsprofil erstellen, um z. B. Anzeigen zu personalisieren. Verarbeitungszwecke: Genaue Standortdaten und Abfrage von Geräteeigenschaften zur Identifikation, Informationen auf einem Gerät speichern und/oder abrufen, Personalisierte Anzeigen und Inhalte, Anzeigen- und Inhaltsmessungen, Erkenntnisse über Zielgruppen und Produktentwicklungen.

Impressum Kontakt Datenschutz