none
Problem mit Zeitberechnungen auf SQL-Server-DB RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden

    Hallo zusammen,

    ich habe hier ein Problem mit einer Tabelle, in der ich bestimmte Zeitberechnungen durchführen muss. Hier die Tabelle:

    CREATE TABLE [dbo].[TestTable5](
	[NummerA] [nvarchar](50) NULL,
	[BeginnA] [datetime] NULL,
	[EndeA] [datetime] NULL,
	[NummerB] [nvarchar](50) NULL,
	[BeginnB] [datetime] NULL,
	[EndeB] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable5] ([NummerA], [BeginnA], [EndeA], [NummerB], [BeginnB], [EndeB]) VALUES (N'A', CAST(N'2018-12-01T12:00:00.000' AS DateTime), CAST(N'2018-12-01T12:15:00.000' AS DateTime), N'B', CAST(N'2018-12-01T14:00:00.000' AS DateTime), CAST(N'2018-12-01T14:00:00.000' AS DateTime))
INSERT [dbo].[TestTable5] ([NummerA], [BeginnA], [EndeA], [NummerB], [BeginnB], [EndeB]) VALUES (N'A', CAST(N'2018-12-01T12:28:00.000' AS DateTime), CAST(N'2018-12-01T12:31:00.000' AS DateTime), N'B', CAST(N'2018-12-01T14:00:30.000' AS DateTime), CAST(N'2018-12-01T14:20:00.000' AS DateTime))
INSERT [dbo].[TestTable5] ([NummerA], [BeginnA], [EndeA], [NummerB], [BeginnB], [EndeB]) VALUES (N'D', CAST(N'2018-12-01T10:35:00.000' AS DateTime), CAST(N'2018-12-01T10:38:15.000' AS DateTime), N'A', CAST(N'2018-12-01T09:15:12.000' AS DateTime), CAST(N'2018-12-01T09:30:10.000' AS DateTime))
INSERT [dbo].[TestTable5] ([NummerA], [BeginnA], [EndeA], [NummerB], [BeginnB], [EndeB]) VALUES (N'C', CAST(N'2018-12-01T06:00:00.000' AS DateTime), CAST(N'2018-12-01T08:30:00.000' AS DateTime), NULL, NULL, NULL)


    In mehreren Spalten werden Nummern aufgelistet mit deren Kommunikationszeit (Beginn/Ende). Ich muss nun die Zeitspannen berechnen, in denen die einzelnen Nummern aktiv waren, d.h. es zählt die früheste BeginnZeit und die späteste EndeZeit. Zu berechnen ist dann diese Zeitspanne in HH:mm:ss.

    Desweiteren sollen 2 zusätzliche Optionen gegeben sein, mit denen man die max./min. Minuten angeben kann.

    Mit folgender Query habe ich schon mal das Zwischenergebnis erzeugt:

    ;WITH cte AS
(
	SELECT [NummerA] AS Nummer, [BeginnA] AS Beginn, [EndeA] AS Ende
	FROM TestTable5
	WHERE [NummerA] is not null
	UNION ALL
	SELECT [NummerB] AS Nummer, [BeginnB] AS Beginn, [EndeB] AS Ende
	FROM TestTable5
	WHERE [NummerB] is not null
)
SELECT * 
FROM cte
GROUP BY [Nummer], Beginn, Ende

    hat die folgende Ausgabe:

    Mit den obigen Angaben muss ich nun folgende Ausgaben erzeugen:

    Kann mir hier einer 'unter die Arme greifen', und mir erklären, wie ich die gewünschte Ausgabe mit einer SQL-Query erzeugen kann?

    Gruß

    Jürgen

    Freitag, 14. Dezember 2018 06:45
    |

Antworten

  • Question
    Anmelden
    1
    Anmelden

    Hallo Jürgen,

    das geht mit einer zweiten CTE. Das Semikolon bitte auch immer am Ende der Statements setzen und nicht vor das WITH.

    WITH cte AS
(
	SELECT [NummerA] AS Nummer, [BeginnA] AS Beginn, [EndeA] AS Ende
	FROM TestTable5
	WHERE [NummerA] is not null
	UNION ALL
	SELECT [NummerB] AS Nummer, [BeginnB] AS Beginn, [EndeB] AS Ende
	FROM TestTable5
	WHERE [NummerB] is not null
), Summen as
(
	SELECT Nummer, min(Beginn) as Beginn, max(Ende) as Ende
	FROM cte
	GROUP BY [Nummer]
)
Select Nummer, Beginn, Ende, DateDiff(SECOND, Beginn, Ende) as Differenz, cast(DATEADD(SECOND, DateDiff(SECOND, Beginn, Ende), '1900-01-01') as time) as Zeitspanne
from Summen;

    Die Where-Bedingung bekommst Du dann selber rein, oder?

    Die erste Zeitspanne in Deinem Beispiel ist falsch! :-)

    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    • Als Antwort markiert Jürgen Sch Freitag, 14. Dezember 2018 07:18
    Freitag, 14. Dezember 2018 07:08
    |
    Beantworter

Alle Antworten

  • Question
    Anmelden
    1
    Anmelden

    Hallo Jürgen,

    das geht mit einer zweiten CTE. Das Semikolon bitte auch immer am Ende der Statements setzen und nicht vor das WITH.

    WITH cte AS
(
	SELECT [NummerA] AS Nummer, [BeginnA] AS Beginn, [EndeA] AS Ende
	FROM TestTable5
	WHERE [NummerA] is not null
	UNION ALL
	SELECT [NummerB] AS Nummer, [BeginnB] AS Beginn, [EndeB] AS Ende
	FROM TestTable5
	WHERE [NummerB] is not null
), Summen as
(
	SELECT Nummer, min(Beginn) as Beginn, max(Ende) as Ende
	FROM cte
	GROUP BY [Nummer]
)
Select Nummer, Beginn, Ende, DateDiff(SECOND, Beginn, Ende) as Differenz, cast(DATEADD(SECOND, DateDiff(SECOND, Beginn, Ende), '1900-01-01') as time) as Zeitspanne
from Summen;

    Die Where-Bedingung bekommst Du dann selber rein, oder?

    Die erste Zeitspanne in Deinem Beispiel ist falsch! :-)

    Einen schönen Tag noch, Christoph - http://www.insidesql.org/blogs/cmu

    • Als Antwort markiert Jürgen Sch Freitag, 14. Dezember 2018 07:18
    Freitag, 14. Dezember 2018 07:08
    |
    Beantworter
  • Question
    Anmelden
    0
    Anmelden

    Hallo Christoph,

    danke für die schnelle Antwort.

    Ich habe ziemlich Zeitdruck, denn ich muss noch insgesamt 3 neue Abfragen bis Ende 2018 generieren und in unsere App einbauen und bin für jede Hilfe äußerst dankbar.
    Jau, in meinem Beispiel, was ich in Excel generiert habe, hatte ich die Zeitspanne für die Nummer A vergessen zu berechnen. Danke für den Hinweis.

    Aber deine Ergänzung läuft so, wie ich es brauche. :-)

    Die WHERE Bedingungen kriege ich hin!

    Gruß Jürgen 

    Freitag, 14. Dezember 2018 07:17
    |
  • Question
    Anmelden
    0
    Anmelden

    Statt des langsameren Union-Selects gehts ja auch mit einem einfachen Select:

    WITH Summen AS
(
	SELECT [NummerA] AS Nummer, 
        case when [BeginnA] < [BeginnB] then [BeginnA] else [BeginnB] end AS Beginn, 
        case when [EndeaA] > [EndeaB] then [EndeaA] else [EndeB] end AS Ende, 
	FROM TestTable5
)

Select Nummer, Beginn, Ende, DateDiff(SECOND, Beginn, Ende) as Differenz, cast(DATEADD(SECOND, DateDiff(SECOND, Beginn, Ende), '1900-01-01') as time) as Zeitspanne
from Summen;

    Falls die Zeiten NULL-Werte aufweisen können, muss man den Vergleich noch in Coalesce einbetten, z.B.:

    when [BeginnA] < coalesce([BeginnB], convert(datetime, '01.01.1800', 104)

    Beim "Union All / Distinct" ist ein SQL nicht so gut optimierbar und erzwingt häufig Tabelscans, was die Abfrage verlangsamt. Bei wenigen 1000 Sätzen markt man das noch nicht so, aber bei Millionen dann schon.

    Freitag, 14. Dezember 2018 09:42
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo bfuerchau,

    danke für den Hinweis, geht das auch für die Spalten Nummer?

    Freitag, 14. Dezember 2018 11:28
    |
  • Question
    Anmelden
    0
    Anmelden

    Mittels case-Ausdruck kannst du alles abfragen:

    Variante 1

    case
    when <Condition1>
    then <True-Expression>
    when <Condition2>
    then <True-Expression>
    :
    else <False-Expression> end

    Variante 2:

    case <Expression>
    when <Constant1> then <Expression1>
    when <Constant2> then <Expression2>
    :
    else <ExpressionDefault>
    end

    https://docs.microsoft.com/de-de/sql/t-sql/language-elements/case-transact-sql?view=sql-server-2017

    Freitag, 14. Dezember 2018 11:37
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo bfuerchau,

    ich habe mir deine obige Lösung nochmal angeguckt. Da fehlt aber komplett die Spalte [NummerB] und deshalb liefert deine Abfrage nicht das richtige Ergebnis.

    Sonntag, 16. Dezember 2018 10:23
    |
  • Question
    Anmelden
    0
    Anmelden

    Dann denke dir doch den passenden Caseausdruck dazu aus.

    Die Frage ist doch, in wie weit NummerA und NummerB mit den jeweiligen Zeiten A/B in direktem Zusammenhang stehen.
    Da in der Union-Lösung ja beide Nummern verwendet werden, diesen jedoch die kleinest Anfangs - und größte Endezeit nummernunabhängig zugeoordnet sind, stellt sich die Frage der Relevanz der 2. Nummer.

    Und was ist dann mit diesem Datenmodell gemeint?
    Sind das dann nicht im Prinzip 2 getrennte Gruppen?

    Dann wäre der Union wieder korrekt, aber ohne Group und Aggregat:

    select NummerA, BeginA, EndeA, EndeA - BeginA as Dauer
    where NummerA is not null
    union all
    select NummerB, BeginB, EndeB, EndeB - BeginB as Dauer
    where NummerB is not null

    Sonntag, 16. Dezember 2018 12:23
    |
  • Question
    Anmelden
    0
    Anmelden

    Ich habe leider hier ein falsches Datenmodell gewählt.

    Im richtigen Modell haben wir Datensätze, die Kommunikationsvorgänge abbilden. Dabei gibt es natürlich nur ein Beginn und ein Ende, dafür aber 3 Nummern für Anrufer, Angerufener und Weitergeleitet. Es sollen nun alle Nummern aus den 3 Nummernspalten angegeben werden mit einer ersten Beginnzeit und der letzten Endezeit, sowie die dazugehörige Zeitspanne. Dabei können innerhalb dieser Zeitspanne weitere Kommunikationsvorgänge vorliegen.

    Ich denke, dass ich ohne den UNION nicht auskomme.

    Wir haben zwar u.U. Millionen von Datensätzen zu untersuchen, aber dann dauert das eben ein bisschen. Eine Excel-VBA-Prozedur dauert da auch schon mal ein bis 2 Stunden.

    Sonntag, 16. Dezember 2018 12:56
    |
  • Question
    Anmelden
    0
    Anmelden

    Dann stelle doch einfach das korrekte Datenmodell und die Aufgabe dazu vor, dann gibt es nicht irgendwelche Lösungen sondern die Richtige;-).

    Rekursive CTE's sind da schon sauschnell, wenn man sie denn korrekt anwendet und entsprechende Indizes vorhanden sind.

    Ich nehme daher mal an, dass das Thema Zeitberechnung ursächlich mit dem anderen Thema rekursive Abfrage zusammen hängt.

    Sonntag, 16. Dezember 2018 13:06
    |
  • Question
    Anmelden
    0
    Anmelden

    Nachfrage:

    Welches Ergebnis erwartest du nach der Union-Methode, wenn der Anrufer denn nach 3 Wochen noch mal anruft?
    Ist die Gesprächsdauer dann 20 Tage oder sogar länger?

    Ich habe einen Kunden, mit dem ich seit mittlerweile über 20 Jahren im Schnitt 1x pro Woche telefoniere, deshalb ist allerdings die Gesamtgesprächsdauer auch nicht 20 Jahre.

    Ggf. macht also da mehr Sinn, die einzelnen Dauern zu kumulieren.

    Montag, 17. Dezember 2018 08:45
    |
  • Question
    Anmelden
    0
    Anmelden

    Hallo bfuerchau,

    nein, die rekursive Abfrage bezog sich auf ein sog. Pfadproblem.

    und die zu untersuchenden Datensätze hier sind i.d.R. auf ca. 2 Tage begrenzt. Der gewünschte Result kommt fachlich von den Usern.

    Gruß Jürgen

    Montag, 17. Dezember 2018 15:25
    |