SQL-Abfrage, um Datensätze auszuschließen, wenn ein entsprechender Eintrag in einer anderen Tabelle (z.B. Urlaub, Termine)

Ich habe zwei Tabellen:

Anwendung

applicationid (int)

applicationname (varchar)

isavailable (bit)

und

Urlaub

applicationid (int)

holidaydate (datetime)

Ich brauche, um die isavailable Flagge für einen bestimmten applicationname sollte es aber nur zurück, wenn der Tag, wenn kein Urlaub ist. Die isavailable flag ist unabhängig von Urlaub - es ist nur gesetzt, wenn es gibt systemweite Probleme verursachen, die nicht auf einen Zeitplan festlegen.

Hatte ich anfangs so etwas wie:

select top 1 apps.isavailable
from dbo.Applications apps, dbo.Holidays hol
where apps.applicationid = hol.applicationid and
      apps.applicationname = @appname and
      ((datediff(dd,getdate(),hol.holidaydate)) != 0)

aber das wurde wieder Datensätze, auch wenn heute ein Feiertag war, weil die anderen urlaubstermin nicht gleich heute.

Versuchte ich

and (CONVERT(VARCHAR,getdate(),101)) not in (CONVERT(VARCHAR,hol.holidaydate,101))

(es ist auf SQL Server 2005, so gibt es kein Datum geben, so habe ich es zu konvertieren)

aber wieder, es wurde wieder Datensätze, auch wenn heute ein Feiertag war. Wie kann ich die Struktur dieser Abfrage mit einem "nicht da" oder "außer" - Klausel (oder etwas anderes) nur die Rückgabe eines Datensatzes, wenn heute nicht ein Feiertag?

Update

Brauche ich nicht eine Liste aller applicationnames, die keinen Urlaub - ich brauche ein Datensatz für den angegebenen apps.applicationname. Die Antworten unten nur die Rückkehr der Anwendung, die Namen, die nicht mit einem Urlaub auf den heutigen Tag. Die Abfrage zurückgeben soll die isavailable Flagge, wenn es ist kein Urlaub, oder sonst keine Datensätze zurückgeben, wenn es ein Feiertag ist. Ich kümmern sich nicht, über die anderen Anwendungen.

Außerdem, was ist, wenn ich fügte hinzu, ein Tisch wie:

HoursOfOperations

applicationid (int)

mondayopen (datetime)

mondayclose (datetime)

tuesdayopen (datetime)

tuesdayclose (datetime)

//öffnen und schließen für alle sieben Tage der Woche

Konnte ich join über alle drei Tabellen, um nur die Rückgabe eines Datensatzes, falls es innerhalb von die Stunden für den Tag und ist nicht ein Feiertag? Muss ich dazu in separate Abfragen?

InformationsquelleAutor Tai Squared | 2009-04-02

15

Sollten Sie die folgende Abfrage erhalten Sie eine Liste der Anwendungen, die NICHT nur ein Feiertag definiert, die für das AKTUELLE Datum.
```
SELECT apps.ApplicationName, apps.isavailable 
FROM dbo.Applications apps
WHERE apps.ApplicationName = @AppName
    AND NOT EXISTS 
( SELECT * 
  FROM Holidays 
  WHERE ApplicationId = apps.ApplicationId
     AND CONVERT(VARCHAR,getdate(),101) = CONVERT(VARCHAR,holidaydate,101)
)
```
Im Grunde, was wir tun müssen, ist wählen Sie alles, wo Sie nicht übereinstimmen.
- Somit haben alle Anwendungen, die nicht über einen Urlaub definiert, aber ich brauche die isavailable-flag nur für den angegebenen apps.applicationname. Ich will nicht zu haben, um die Liste Durchlaufen wird, nachdem die Abfrage. Auch, was ist wenn ich join auf eine andere Tabelle - ich kann nicht kombinieren, wo und wo nicht-Klausel
- Tai - fügen Sie einfach eine zusätzliche Klausel. UND apps.AppName = "Ihre Bewerbung". Können Sie keine joins erforderlich,
- Ich aktualisierte das Beispiel, Ihnen zu zeigen, wie beschränken von app-Namen, unter der Annahme einer param von @AppName
- Das funktioniert - vielen Dank. Kannst du bitte es Bearbeiten, um einen mehr schließenden Klammer so kann jemand Ausschneiden und einfügen?
- Tai - Sicher haben! (Ich weiß nicht, wo es ging am Anfang!)
InformationsquelleAutor Mitchel Sellers
5

OK, einfach nur anders zu sein, wie wäre es mit etwas wie dieses:
```
select apps.isavailable
from dbo.Application apps left outer join dbo.Holidays hol
    on apps.applicationid = hol.applicationid
    and convert(varchar(10),getdate(),101) = convert(varchar(10),hol.holidaydate,101)
where apps.applicationname = @appname
    and hol.applicationid is null
```
Im Grunde hast du die Tabellen verknüpfen, basierend auf applicationid und das aktuelle Datum. Da es ein left join, erhalten Sie immer alle Anwendungen, match @appname, dann haben Sie gerade den filter heraus keine Ergebnisse, die erhalten ein Spiel basiert auf den Urlaub Datum wird das aktuelle Datum. Unter der Annahme, dass applicationname ist einzigartig, erhalten Sie immer eine einzelne Zeile, wo die Rechte Hälfte des join ist null, es sei denn, das aktuelle Datum mit einem Urlaub, in dem Fall die Abfrage keine Ergebnisse.

Ich weiß nicht, wie es stapelt sich mit den anderen Lösungen performance-Weise, ich glaube, joins sind in der Regel soll schneller sein als die sub-queries, aber das hängt wahrscheinlich von einer Vielzahl von Faktoren ab, so YMMV.
- Matt - In den meisten Fällen die performance ist etwa die gleiche. Es sieht aus wie dieser Benutzer benötigt nur eine einzige Anwendung, so dass die Unterabfrage route vielleicht ein bisschen schneller...
- Mitchel Sellers - ich dachte immer, das Problem mit den Unterabfragen war, dass Sie waren wiederholt für jede Zeile, aber ich sehe, was du sagst. Alle, die ich wirklich wissen über die Leistung ist, dass, wenn es darauf ankommt, in einem bestimmten Fall genau muss ich Messen. 😉
- Und der "where not exists" - Ansatz ist sicherlich besser lesbar in jedem Fall.
InformationsquelleAutor Matt
4

Können Sie "WHERE not EXISTS":
```
SELECT *
FROM Applications a
WHERE NOT EXISTS (
    SELECT * 
    FROM Holidays h
    WHERE h.ApplicationID = a.ApplicationID
        AND HolidayDate = cast(cast(getdate() as int) as datetime)
)
```
Mache ich den cast dort zum abschneiden der getdate () - Aufruf zurück, um nur das Datum. Getestet noch nicht, die genaue Abfrage aber ich denke, es wird die Arbeit für Sie erledigen.
- VERDAMMT ! Sie schlagen mir um 55 Sekunden.... 🙂
- Dieser hat das gleiche problem wie Michael Sellers' beantworten - zeigt alle Anwendungen, die nicht über einen Urlaub definiert, aber ich brauche die isavailable-flag nur für den angegebenen apps.applicationname.
- Sorry - wusste nicht, denke, das wäre ein problem, da Sie Ihre ursprüngliche Abfrage bereits den test für ApplicationName = @appname. Ich dachte, Sie wusste, was zu tun ist.
InformationsquelleAutor Matt Hamilton

Etwas wie das hier tun:

SELECT (fields) FROM Application
WHERE NOT EXISTS
  (SELECT * FROM Holidays
   WHERE ApplicationID = Application.ApplicationID
   AND DAY(getdate()) = DAY(holidaydate) 
   AND MONTH(getdate()) = MONTH(holidaydate)
   AND YEAR(getdate()) = YEAR(holidaydate)
  )

Natürlich wäre es viel einfacher und schneller als mit SQL Server 2008 die "DATE" - Datentyp ist, oder wenn Sie die Speicherung von Tag, Monat, Jahr in "Urlaub" getrennt.

Marc

InformationsquelleAutor marc_s

SELECT a.isAvailable
  FROM Application a
 WHERE NOT EXISTS (
    SELECT TOP 1 0
      FROM Holidays b
     WHERE a.applicationid = b.applicationid
       AND holidaydate = $today
 )

InformationsquelleAutor Chase Seibert

Was über diese:

SELECT 
  Application.isavailable
FROM 
   Holidays 
RIGHT JOIN 
   Application
ON 
   Holidays.applicationid = Application.applicationid
WHERE 
  ((Application.applicationname='app1') AND 
  (((Holidays.holidaydate=CurrentDate()) AND (Holidays.applicationid Is Null)) OR 
  (holidays.holidaydate Is Null)));

"'app1'" sollte ersetzt werden durch eine Variablen-Bezeichner und "CurrentDate ()", die durch das system bestimmte Funktion, um das aktuelle Datum.

Cheers

InformationsquelleAutor alk

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.