Requête SQL pour exclure les enregistrements si elle correspond à une entrée dans une autre table (par exemple des dates de vacances)

J'ai deux tables:

Application

identificateur applicationid (int)

applicationname (varchar)

isavailable (bit)

Vacances

identificateur applicationid (int)

holidaydate (datetime)

J'ai besoin d'obtenir le isavailable drapeau pour tout applicationname mais il devrait retourner uniquement si le jour s'est pas un jour férié. Le isavailable drapeau est indépendante de vacances - c'est seulement si il y a des problèmes au niveau du système, et non pas sur un programme défini.

J'ai d'abord eu quelque chose comme:

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)

mais c'était le retour des dossiers, même si aujourd'hui était un jour férié parce que les autres dates de vacances ne correspond pas aujourd'hui.

J'ai essayé

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

(c'est sur SQL Server 2005, donc il n'y a pas de type Date, donc je dois les convertir)

mais encore une fois, il était de retour des dossiers, même si aujourd'hui était un jour férié. Comment structurer cette requête à l'aide d'un "non" ou "à l'exception de" l'alinéa (ou autre chose) à retourner uniquement si aujourd'hui n'est pas un jour férié?

Mise à jour

Je n'ai pas besoin d'une liste de tous les applicationnames qui n'ont pas de vacances - j'ai besoin d'un dossier pour l'apps.applicationname. Les réponses ci-dessous retourner uniquement les noms des applications qui n'ont pas de vacances aujourd'hui. La requête doit renvoyer la isavailable flag si ce n'est pas un jour férié, ou de restitution aucun enregistrement si elle est un jour férié. Je ne me préoccupe pas des autres applications.

Aussi, que si j'ai ajouté une table:

HoursOfOperations

identificateur applicationid (int)

mondayopen (datetime)

mondayclose (datetime)

tuesdayopen (datetime)

tuesdayclose (datetime)

//ouvrir et à fermer toutes les sept jours de la semaine

Pourrais-je rejoindre sur chacun de ces trois tables à retourner uniquement si c'est dans les heures de la journée et n'est pas un jour férié? Dois-je le faire dans les requêtes distinctes?

OriginalL'auteur Tai Squared | 2009-04-02

15

La requête suivante devrait vous donner une liste des applications qui N'ont PAS de vacances défini pour la date ACTUELLE.
```
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)
)
```
Fondamentalement, ce que nous avons à faire est de sélectionner tout où il ne dispose pas d'un match.

Cela donne toutes les applications qui n'ont pas de vacances défini, mais j'ai besoin de la isavailable drapeau de spécifier les applications.applicationname. Je ne veux pas avoir à parcourir la liste après la requête. Aussi, si je dois rejoindre sur une autre table, je ne peux pas combiner les où et n'ont pas de clause de
Tai - il suffit d'ajouter une clause supplémentaire. ET apps.AppName = "votre demande". Vous pouvez ajouter toutes les jointures nécessaires,
J'ai mis à jour l'exemple pour vous montrer comment limiter par le nom de l'application, en supposant que les param de @AppName
Qui fonctionne grâce. Pouvez vous s'il vous plaît de le modifier pour inclure un plus parenthèse fermante pour que quelqu'un puisse le copier et le coller?
Tai - Sûr! (Je ne sais pas où il est allé dans le début!!!)

OriginalL'auteur Mitchel Sellers
5

OK, juste pour être différent, comment au sujet de quelque chose comme ceci:
```
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
```
Fondamentalement, vous êtes joindre les tables basées sur identificateur applicationid et la date actuelle. Puisque c'est une jointure gauche, vous aurez toujours toutes les applications qui correspondent à @appname, alors que vous venez de filtrer les résultats à obtenir une correspondance basée sur la date de vacances étant la date du jour. En supposant que applicationname est unique, vous aurez toujours une ligne unique où la moitié droite de la jointure est nulle, à moins que la date du jour correspond à un jour férié, auquel cas, la requête ne renvoie aucun résultat.

Je ne sais pas comment ça s'empile avec les autres solutions de performance; je crois que les jointures sont généralement censé être plus rapide que les sous-requêtes, mais que, probablement, dépend d'une variété de facteurs, de sorte YMMV.

Matt - Dans la plupart des cas, le rendement est à peu près la même. Il ressemble à cet utilisateur est besoin d'une seule application, de sorte que la sous-requête route peut être un peu plus rapide...
Mitchel Vendeurs - j'ai toujours pensé que le problème avec les sous-requêtes est qu'ils ont été répétées pour chaque ligne, mais je vois ce que vous dites. Tout ce que je vraiment savoir à propos de la performance, c'est que si c'est important dans un cas donné, je devrais juste mesure. 😉
Et le "où n'existe pas" est certainement plus lisible, en tout cas.

OriginalL'auteur Matt
4

Vous pouvez utiliser "OÙ n'EXISTE PAS":
```
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)
)
```
Je suis en train de faire le casting il y a de tronquer la getdate() rappel de la date. N'ai pas testé la même requête, mais je pense qu'il va faire le travail pour vous.

DARN ! Tu m'as devancé par 55 secondes.... 🙂
Cela a le même problème que Michael Vendeurs réponse - Ce qui nous donne toutes les applications qui n'ont pas de vacances défini, mais j'ai besoin de la isavailable drapeau de spécifier les applications.applicationname.
Désolé ne pense pas que ce serait un problème depuis votre requête initiale déjà inclus le test de ApplicationName = @appname. J'ai pensé que vous savait comment faire.

OriginalL'auteur Matt Hamilton

Faire quelque chose comme ceci:

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)
  )

Bien sûr, il serait beaucoup plus facile et plus rapide avec SQL Server 2008 "DATE" type de données, ou si vous pouvez stocker des jour, mois, année en "Vacances" séparément.

Marc

OriginalL'auteur 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
 )

OriginalL'auteur Chase Seibert

Ce sujet de celui-ci:

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'" devrait être remplacé par une variable le prescripteur et le "CurrentDate()" par le système spécifique à la fonction renvoie la date actuelle.

Acclamations

OriginalL'auteur alk

Vous devez vous connecter pour publier un commentaire.