Seitenvergleich

...

Abfrage von Terminduplikaten

Wiki-Markup

Mit dieser Abfrage können Sie prüfen, wie viele Terminduplikate existieren. 
<ac:structured-macro ac:name="anchor" ac:schema-version="1" ac:macro-id="12dfa1fd-2571-4d9f-a623-6a05b73b0446"><ac:parameter ac:name="">OLE_LINK162</ac:parameter></ac:structured-macro><span style="color: #0000ff">USE</span> \[Advokat_DATEN\]
<span style="color: #0000ff">GO</span>
\\
<span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> dbo<span style="color: #808080">.</span>Termin <span style="color: #0000ff">where</span> <span style="color: #ff00ff">concat</span><span style="color: #808080">(</span>ANr<span style="color: #808080">,</span> Datum<span style="color: #808080">,</span> Zeit<span style="color: #808080">,</span> Dauer<span style="color: #808080">,</span> Ende<span style="color: #808080">,</span> Sender<span style="color: #808080">,</span> SB<span style="color: #808080">,</span> Person<span style="color: #808080">,</span> Betreff<span style="color: #808080">,</span> Memo<span style="color: #808080">,</span> Art<span style="color: #808080">,</span> Erledigt<span style="color: #808080">,</span> Geschützt<span style="color: #808080">,</span> Flag<span style="color: #808080">,</span> Tag<span style="color: #808080">,</span> Pfad<span style="color: #808080">)</span> <span style="color: #808080">IN</span>
<span style="color: #808080">(</span><span style="color: #0000ff">SELECT</span> Wert <span style="color: #0000ff">FROM</span> <span style="color: #808080">(</span><span style="color: #0000ff">SELECT</span> Zähler<span style="color: #808080">,</span> <span style="color: #ff00ff">concat</span><span style="color: #808080">(</span>ANr<span style="color: #808080">,</span> Datum<span style="color: #808080">,</span> Zeit<span style="color: #808080">,</span> Dauer<span style="color: #808080">,</span> Ende<span style="color: #808080">,</span> Sender<span style="color: #808080">,</span> SB<span style="color: #808080">,</span> Person<span style="color: #808080">,</span> Betreff<span style="color: #808080">,</span> Memo<span style="color: #808080">,</span> Art<span style="color: #808080">,</span> Erledigt<span style="color: #808080">,</span> Geschützt<span style="color: #808080">,</span> Flag<span style="color: #808080">,</span> Tag<span style="color: #808080">,</span> Pfad<span style="color: #808080">)</span> <span style="color: #0000ff">AS</span> Wert <span style="color: #0000ff">FROM</span> dbo<span style="color: #808080">.</span>Termin<span style="color: #808080">)</span> <span style="color: #0000ff">AS</span> tmp
<span style="color: #0000ff">group</span> <span style="color: #0000ff">by</span> wert
<span style="color: #0000ff">having</span> <span style="color: #ff00ff">count</span><span style="color: #808080">(</span>Zähler<span style="color: #808080">)</span> <span style="color: #808080">&gt;</span> 1<span style="color: #808080">)</span>
<span style="color: #0000ff">order</span> <span style="color: #0000ff">by</span> Anr<span style="color: #808080">,</span> Datum<span style="color: #808080">,</span> Zeit
<span style="color: #0000ff">GO</span>
\\

Löschung von Terminduplikaten

Wiki-Markup

Mit dieser Abfrage können Sie alle laut Schritt 3 gefundenen Terminduplikate löschen. Stellen Sie sicher, dass die Tabelle _dbo.TerminDuplikate_ nicht existiert, ist diese Tabelle vorhanden, muss sie vorher gelöscht werden. 
<span style="color: #0000ff">USE</span> \[Advokat_DATEN\]
<span style="color: #0000ff">GO</span>
\\
<span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">INTO</span> dbo<span style="color: #808080">.</span>TerminDuplikate <span style="color: #0000ff">FROM</span> dbo<span style="color: #808080">.</span>Termin <span style="color: #0000ff">WHERE</span>
<span style="color: #ff00ff">concat</span><span style="color: #808080">(</span>ANr<span style="color: #808080">,</span> Datum<span style="color: #808080">,</span> Zeit<span style="color: #808080">,</span> Dauer<span style="color: #808080">,</span> Ende<span style="color: #808080">,</span> Sender<span style="color: #808080">,</span> SB<span style="color: #808080">,</span> Person<span style="color: #808080">,</span> Betreff<span style="color: #808080">,</span> Memo<span style="color: #808080">,</span> Art<span style="color: #808080">,</span> Erledigt<span style="color: #808080">,</span> Geschützt<span style="color: #808080">,</span> Flag<span style="color: #808080">,</span> Tag<span style="color: #808080">,</span> Pfad<span style="color: #808080">)</span> <span style="color: #808080">in</span>
<span style="color: #808080">(</span><span style="color: #0000ff">SELECT</span> Wert <span style="color: #0000ff">FROM</span>
<span style="color: #808080">(</span><span style="color: #0000ff">SELECT</span> Zähler<span style="color: #808080">,</span> <span style="color: #ff00ff">concat</span><span style="color: #808080">(</span>ANr<span style="color: #808080">,</span> Datum<span style="color: #808080">,</span> Zeit<span style="color: #808080">,</span> Dauer<span style="color: #808080">,</span> Ende<span style="color: #808080">,</span> Sender<span style="color: #808080">,</span> SB<span style="color: #808080">,</span> Person<span style="color: #808080">,</span> Betreff<span style="color: #808080">,</span> Memo<span style="color: #808080">,</span> Art<span style="color: #808080">,</span> Erledigt<span style="color: #808080">,</span> Geschützt<span style="color: #808080">,</span> Flag<span style="color: #808080">,</span> Tag<span style="color: #808080">,</span> Pfad<span style="color: #808080">)</span> <span style="color: #0000ff">AS</span> Wert
<span style="color: #0000ff">FROM</span> dbo<span style="color: #808080">.</span>Termin<span style="color: #808080">)</span> <span style="color: #0000ff">AS</span> tmp
<span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> wert <span style="color: #0000ff">HAVING</span> <span style="color: #ff00ff">COUNT</span><span style="color: #808080">(</span>Zähler<span style="color: #808080">)</span> <span style="color: #808080">&gt;</span> 1<span style="color: #808080">)</span> <span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> anr<span style="color: #808080">,</span> datum<span style="color: #808080">,</span> zeit
<span style="color: #0000ff">GO</span>
\\
<span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span> dbo<span style="color: #808080">.</span>TerminDuplikate <span style="color: #0000ff">WHERE</span> Zähler <span style="color: #808080">IN</span>
<span style="color: #808080">(</span><span style="color: #0000ff">SELECT</span> Kleinster <span style="color: #0000ff">from</span> <span style="color: #808080">(</span><span style="color: #0000ff">select</span> <span style="color: #ff00ff">MIN</span><span style="color: #808080">(</span>Zähler<span style="color: #808080">)</span> <span style="color: #0000ff">AS</span> Kleinster<span style="color: #808080">,</span> Wert <span style="color: #0000ff">FROM</span>
<span style="color: #808080">(</span><span style="color: #0000ff">SELECT</span> Zähler<span style="color: #808080">,</span> <span style="color: #ff00ff">concat</span><span style="color: #808080">(</span>ANr<span style="color: #808080">,</span> Datum<span style="color: #808080">,</span> Zeit<span style="color: #808080">,</span> Dauer<span style="color: #808080">,</span> Ende<span style="color: #808080">,</span> Sender<span style="color: #808080">,</span> SB<span style="color: #808080">,</span> Person<span style="color: #808080">,</span> Betreff<span style="color: #808080">,</span> Memo<span style="color: #808080">,</span> Art<span style="color: #808080">,</span> Erledigt<span style="color: #808080">,</span> Geschützt<span style="color: #808080">,</span> Flag<span style="color: #808080">,</span> Tag<span style="color: #808080">,</span> Pfad<span style="color: #808080">)</span> <span style="color: #0000ff">AS</span> Wert <span style="color: #0000ff">FROM</span> dbo<span style="color: #808080">.</span>Termin<span style="color: #808080">)</span>
<span style="color: #0000ff">AS</span> tmp <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> Wert <span style="color: #0000ff">HAVING</span> <span style="color: #ff00ff">COUNT</span><span style="color: #808080">(</span>Zähler<span style="color: #808080">)</span> <span style="color: #808080">&gt;</span> 1<span style="color: #808080">)</span> <span style="color: #0000ff">AS</span> tmp2<span style="color: #808080">)</span>
<span style="color: #0000ff">GO</span>
\\
<span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span> dbo<span style="color: #808080">.</span>Termin <span style="color: #0000ff">WHERE</span> Zähler <span style="color: #808080">IN</span>
<span style="color: #808080">(</span><span style="color: #0000ff">SELECT</span> Zähler <span style="color: #0000ff">FROM</span> dbo<span style="color: #808080">.</span>TerminDuplikate<span style="color: #808080">)</span>
<span style="color: #0000ff">GO</span>
\\
Nach Ausführung der Befehle löschen Sie die entstandene Tabelle _dbo.Terminduplikate{_}!
\\

Aufgrund der Löschung in ADVOKAT wird die Löschung automatisch an Microsoft Exchange übertragen, die Terminduplikate müssen in Microsoft Office Outlook also nicht gelöscht werden.

Versionen im Vergleich

Alte Version 4

Neue Version 5

Schlüssel