phil83
Jul 06, 2007, updated at Jul 10, 2007 (UTC)
view6251
view14
0
Goto Top

MS SQL Top2 von Jeder ID

GermansolvedQuestionDatabasesDevelopment
Hallo zusammen

Ich habe eine Datenbank mit 2 Tabellen die wie folgt aussehen:

Tabelle tblShare
ID
Name
UNC
erfasst


Tabelle tblSize
ID
ShareID
datum
size

Das ganze ist Basis eines VBScripts das einmal in der Woche läuft und die Größe jeder Freigabe auf einem Fileserver erfasst.
In der Tabelle tblShare werden die Freigaben erfasst mit Freigabe Namen und dem UNC Pfad. In der Tabelle tblSize wird in der ShareID die ID der Freigabe aus tblShare eingetragen um das ganze dann mit einem INNER JOIN verbinden zu können.

Nun will ich eine Auswertung schreiben die aus der Tabelle die 2 letzen Wochen über das Datum mit der jeweiligen Größe ausliest um festzustellen welche Freigabe um wieviel gewachsen ist. Wenn ich die Max Funktion aufs Datum leg geht es für die letze Woche, aber ich bräuchte ja 2 Wochen um einen Vergleich zu haben. So stell ich mir die Ausgabe ca. vor:


Name | UNC | Datum_letzer_lauf | Size_letzer_lauf | Datum_vorletzer_lauf | Size_vorletzer_lauf | Veränderung_in_MB | Veränderung_in_%


Leider reicht hierfür meine SQL Kenntniss nicht mehr aus und auch Google hat mir nicht sonderlich weiter geholfen.

Vielen Dank

Mit freundlichen Grüßen
Phil
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 63193

Url: https://administrator.de/contentid/63193

Printed on: April 23, 2024 at 10:04 o'clock

14 Comments
Latest comment
Goto Top
Member: AndreasHoster
AndreasHoster Jul 06, 2007 at 15:51:03 (UTC)
Goto Top
Also wenn Du es als eine SQL Abfrage haben willst, sieht das so aus (Wenn das Datum bei allen Shares gleich ist, also alle z.B. am 1.7.07 gesammelt werden und nächste Sammlung auch alle an einem anderen Tag z.B. 06.07.07):
 SELECT Aktuell.Name, Aktuell.UNC, Aktuell.Datum, Aktuell.Size, LetzteWoche.Datum, LetzteWoche.Size, Aktuell.Size - LetzteWoche.Size AS Veraenderung, (( Aktuell.Size - LetzteWoche.Size) / LetzteWoche.Size)*100 AS Veraenderung_in_Prozent
from
(SELECT tblShare.ID, tblShare.UNC, tblShare.Name, tblSize.Datum, tblSize.Size
FROM tblShare, tblSize
WHERE tblShare.ID=tblSize.ShareID and  (((tblSize.Datum)=(Select Max(Datum) from 
(SELECT DISTINCT TOP 2 tblSize.Datum 
FROM tblSize 
ORDER BY tblSize.Datum DESC) as t1 
)))) as Aktuell, 
(SELECT tblShare.ID, tblShare.UNC, tblShare.Name, tblSize.Datum, tblSize.Size
FROM tblShare, tblSize
WHERE tblShare.ID=tblSize.ShareID and (((tblSize.Datum)=(Select Min(Datum) from 
(SELECT DISTINCT TOP 2 tblSize.Datum 
FROM tblSize 
ORDER BY tblSize.Datum DESC) as t1 
)))) as LetzteWoche
where Aktuell.ID=LetzteWoche.ID
Ist glaube ich etwas unverständlich, ich versuche es mal aufzudröseln:
Select Min(Datum) from 
(SELECT DISTINCT TOP 2 tblSize.Datum 
FROM tblSize 
ORDER BY tblSize.Datum DESC) as t1
Ich sortiere alle Daten absteigend, falls ein Wert mehrfach vorkommt nur einmal anzeigen (Distinct), nehme die 2 oberen Werte (Top 2) und dann davon das Minimum bzw. Maximum, damit habe ich die aktuelle und letzte Woche.
SELECT tblShare.ID, tblShare.UNC, tblShare.Name, tblSize.Datum, tblSize.Size
FROM tblShare, tblSize
WHERE tblShare.ID=tblSize.ShareID and  (((tblSize.Datum)=(Select Max(Datum) from 
(SELECT DISTINCT TOP 2 tblSize.Datum 
FROM tblSize 
ORDER BY tblSize.Datum DESC) as t1 
)))) as Aktuell
Ich joine die tblSize und tblShare über die ID und nehme nur die Datensätze, die über die Unterabfrage das Aktuelle Datum haben und mache das dann nochmals mit dem vorherigen Datum. Ergibt die 2 temporären Tabellen Aktuell und Letzte Woche.
Letztendlich joine ich dann die 2 temporären Tabellen über die ID und mache noch die 2 berechneten Felder.
Man könnte auch jede temporäre Tabelle als View anlegen und die verwenden, ist übersichtlicher, aber als eine ist auch machbar und eine Herausforderung.
Member: Phil83
Phil83 Jul 09, 2007 at 07:37:49 (UTC)
Goto Top
Hallo,

erstmal Vielen Dank für deine Antwort. Dachte nicht dass das ganze so Komplex wäre. Wenn ich deine Abfrage in den Abfrage Editor des Management Studios kopiere bekomme ich aber leider keinen einzigen Datensatz zurück. Ich habe allerdings 1475 Freigaben erfasst und mehrmals die Größe abgefragt sodass ich inzwischen in der tblSize 10720 Datensätze habe. Wenn ich deine Abfrage in den Editor kopiere ändert der mir die Abfrage etwas ab. Hier die Abfrage wie sie bei mir aussieht:
SELECT     Aktuell.Name, Aktuell.UNC, Aktuell.datum, Aktuell.size, LetzteWoche.datum AS Expr1, LetzteWoche.size AS Expr2, 
                      Aktuell.size - LetzteWoche.size AS Veraenderung, (Aktuell.size - LetzteWoche.size) / LetzteWoche.size * 100 AS Veraenderung_in_Prozent
FROM         (SELECT     dbo.tblShare.ID, dbo.tblShare.UNC, dbo.tblShare.Name, dbo.tblSize.datum, dbo.tblSize.size
                       FROM          dbo.tblShare INNER JOIN
                                              dbo.tblSize ON dbo.tblShare.ID = dbo.tblSize.ShareID
                       WHERE      (dbo.tblSize.datum =
                                                  (SELECT     MAX(datum) AS Expr1
                                                    FROM          (SELECT DISTINCT TOP (2) datum
                                                                            FROM          dbo.tblSize AS tblSize_3
                                                                            ORDER BY datum DESC) AS t1))) AS Aktuell INNER JOIN
                          (SELECT     tblShare_1.ID, tblShare_1.UNC, tblShare_1.Name, tblSize_2.datum, tblSize_2.size
                            FROM          dbo.tblShare AS tblShare_1 INNER JOIN
                                                   dbo.tblSize AS tblSize_2 ON tblShare_1.ID = tblSize_2.ShareID
                            WHERE      (tblSize_2.datum =
                                                       (SELECT     MIN(datum) AS Expr1
                                                         FROM          (SELECT DISTINCT TOP (2) datum
                                                                                 FROM          dbo.tblSize AS tblSize_1
                                                                                 ORDER BY datum DESC) AS t1_1))) AS LetzteWoche ON Aktuell.ID = LetzteWoche.ID

Hoffe du kannst mir nochmal helfen.

Vielen Dank

MfG
Phil
Member: AndreasHoster
AndreasHoster Jul 09, 2007 at 08:26:48 (UTC)
Goto Top
Wenn ich die geänderte bei mir wieder einfüge, kommt Fehler in Join.
Seis drum, wir können das auch in Einzelschritten machen und es in 4 Views machen, da kann man dann bei jeder nachschauen, was rauskommt und eventuell den Fehler besser sehen.
Zuerst die View fürs Datum, habe ich admDatum genannt (ich schreibe immer nur den Select für die View hin):
SELECT DISTINCT TOP 2 tblSize.Datum
FROM tblSize
ORDER BY tblSize.Datum DESC;;
Dann View admAktuell:
SELECT tblShare.ID, tblShare.Name, tblShare.UNC, tblSize.Datum, tblSize.Size
FROM tblSize, tblShare
WHERE (((tblSize.Datum)=(select max(datum) from admDatum)) AND ((tblSize.ShareID)=[tblShare].[ID]));;
Dann View admLetzteWoche:
SELECT tblShare.ID, tblShare.Name, tblShare.UNC, tblSize.Datum, tblSize.Size
FROM tblSize, tblShare
WHERE (((tblSize.Datum)=(select min(datum) from admDatum)) AND ((tblSize.ShareID)=[tblShare].[ID]));;
Dann die Ausgabe, admAusgabe:
SELECT admAktuell.Name, admAktuell.UNC, admAktuell.Datum, admAktuell.Size, admLetzteWoche.Datum, admLetzteWoche.Size, admAktuell.Size - admLetzteWoche.Size AS Veraenderung, (( admAktuell.Size - admLetzteWoche.Size) / admLetzteWoche.Size)*100 AS Veraenderung_in_Prozent
FROM admAktuell, admLetzteWoche
WHERE admAktuell.ID = admLetzteWoche.ID;
Probiers also mal mit den Einzelschritten aus, dann sehen wir mal, wie weit wir kommen.
Member: Phil83
Phil83 Jul 09, 2007 at 11:52:43 (UTC)
Goto Top
Hallo,

ich hatte noch einen Fehler drin. Bei mir wurde nicht nur das Datum sondern auch die Uhrzeit erfasst, dadurch hatte ich für jedes Datum nur einen Datensatz. Hab das geändert und alles bis auf die admAusgabe funktioniert auch.
Bei der admAusgabe bekomme ich jetzt allerdings folgendes:
"Fehler aufgrund einer Division durch Null."

Vielen Dank

Mit freundlichen Grüßen
Phil
Member: AndreasHoster
AndreasHoster Jul 09, 2007 at 12:25:23 (UTC)
Goto Top
Für die Veränderung in Prozent dividiere ich ja durch den Wert von letzter Woche.
Wenn der nun 0 ist, dann gibts ein Fehler.
Wenn Du nur die Veränderung in MB nimmst sollte es gehen.
Wie man jetzt aber in SQL dafür sorgt, daß bei 0 es keine Probleme gibt und trotzdem alles anzeigt wüsste ich nicht.
Mit einer Erweiterung der where Bedingung auf and admLetzteWoche.Size <> 0 könnte man natürlich die weglassen, die da Division by Zero erzeugen, aber dann fehlen halt ein paar Shares.
Je nachdem, wie genau es werden muß, könnte man natürlich auch 0,000001 oder so zu size addieren, daß es halt nur fast 0 ist.
Member: Phil83
Phil83 Jul 09, 2007 at 13:17:59 (UTC)
Goto Top
Hi,

also so funktioniert es. Das einzige das jetzt noch nicht geht ist die Prozentrechnung. Ich hab Veränderung immer 0% obwohl die Veränderung in MB zum Beispiel 45 oder -9 an.

Vielen Dank

Mit freundlichen Grüßen
Phil
Member: AndreasHoster
AndreasHoster Jul 09, 2007 at 13:38:22 (UTC)
Goto Top
Wie sieht die admAusgabe denn jetzt aus in SQL?
Member: Phil83
Phil83 Jul 09, 2007 at 13:39:27 (UTC)
Goto Top
SELECT     dbo.admAktuell.Name, dbo.admAktuell.UNC, dbo.admAktuell.datum, dbo.admAktuell.size, dbo.admLetzteWoche.datum AS Expr1, 
                      dbo.admLetzteWoche.size AS Expr2, dbo.admAktuell.size - dbo.admLetzteWoche.size AS Veraenderung, 
                      (dbo.admAktuell.size - dbo.admLetzteWoche.size) / dbo.admLetzteWoche.size * 100 AS Veraenderung_in_Prozent
FROM         dbo.admAktuell INNER JOIN
                      dbo.admLetzteWoche ON dbo.admAktuell.ID = dbo.admLetzteWoche.ID
WHERE     (dbo.admLetzteWoche.size <> 0)
Member: AndreasHoster
AndreasHoster Jul 09, 2007 at 14:27:46 (UTC)
Goto Top
Du verwendest einen MS SQL Server, oder?
Der hat die Eigenschaft, beim Rechnen mit Ganzzahlen immer auf Ganzzahlen zu runden und (dbo.admAktuell.size - dbo.admLetzteWoche.size) / dbo.admLetzteWoche.size wird daher auf 0 gerundet . Und dann hilft multiplizieren mit 100 nicht mehr.
Wenn die ganze Prozent reichen, dann auf ((dbo.admAktuell.size - dbo.admLetzteWoche.size) * 100) / dbo.admLetzteWoche.size ändern (also zuerst mit 100 multiplizieren, dann dividieren) oder eine Floating Point Operation erzwingen und
(cast(dbo.admAktuell.size - dbo.admLetzteWoche.size) as float) / dbo.admLetzteWoche.size * 100 nehmen.
Member: Phil83
Phil83 Jul 09, 2007 at 14:36:55 (UTC)
Goto Top
Hi,

ich glaub solangsam werd ich etwas nervig face-smile. Das ganze klappt jetzt mit den Prozent allerdings nicht wenn Platz frei geworden ist, sprich Veraenderung einen Negativen Wert enthält.

MfG
Phil
Member: AndreasHoster
AndreasHoster Jul 09, 2007 at 15:15:38 (UTC)
Goto Top
Wenns mir zu nervig wird, höre ich einfach mit dem Antworten auf face-wink
Was passiert denn wenn Platz freigeworden ist (dürfte eh nur selten vorkommen, meistens wirds ja mehr face-smile )?
Kommt dann 0 oder was kommt dann?
Member: Phil83
Phil83 Jul 10, 2007 at 05:05:54 (UTC)
Goto Top
Hallo,

anscheinend hab ich brave User denn es wird schon des öfteren wieder Platz frei. face-smile Ja bei negativen Werten kommt 0% raus.

Was mir grade noch aufgefallen ist, die Prozentrechnung rechnet allgemein nicht richtig. Ich hab zum Beispiel einen Datensatz mit 97MB veränderung, in Prozent 0. Dann einen datensatz mit 61MB veränderung und 1% und einen mit 45MB veränderung und 4%. Sieht doch alles sehr merkwürdig aus face-smile

MfG
Phil
Member: AndreasHoster
AndreasHoster Jul 10, 2007 at 08:13:01 (UTC)
Goto Top
Was hast Du denn an Werten von letzterWoche und Aktuell?
Wenn Du es nach der Formel manuell nachrechnest, was kommt denn dann raus?
97MB Veränderung bei 500GB Daten wären ja auch nur 0,018%, könnte ja zu 0 gerundet werden.
Member: Phil83
Phil83 Jul 10, 2007 at 08:14:49 (UTC)
Goto Top
Hi,

du hast Recht. War mein fehler. Es gehen jetzt auch die Negativen %, die Werte dafür waren einfach zu klein und wurden immer auf 0 gerundet. Also Problematik gelöst. Vielen Dank für deine Ausgiebige Hilfe.

MfG
Phil
GermansolvedQuestionDatabasesDevelopment
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 CommentsjstrickerWireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 CommentsAlexWishaHow to set up and configure a Linux GRE tunnelAlexWisha - 3 CommentsDaniEnd of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment