Vérifier les valeurs de dates invalides pour un ordre correct

Question

J'ai un ensemble de données avec des dates et je veux vérifier si l'ordre des dates est correct.

RecordID   Date1        Date2        Date3        Date4
1          2011-05-10   2011-08-16   NULL         2011-11-22
2          NULL         2012-02-03   2012-02-27   2012-03-05
3          2011-05-30   2011-05-11   2011-08-17   2011-09-15
4          2011-05-30   NULL         NULL         NULL

Dans tous les cas où des dates sont fournies, cela devrait tenir : Date1 < Date2 < Date3 < Date4. Lorsque l'enregistrement contient des valeurs NULL pour certaines dates, la vérification doit se faire entre les dates qui ne sont pas NULL. Voici donc le résultat que je veux :

RecordID   Date1        Date2        Date3        Date4        CheckDates
1          2011-05-10   2011-08-16   NULL         2011-11-22   correct
2          NULL         2012-02-03   2012-02-27   2012-03-05   correct
3          2011-05-30   2011-05-11   2011-08-17   2011-09-15   incorrect
4          2011-05-30   NULL         NULL         NULL         correct

J'ai écrit une déclaration CASE détaillée à ce sujet, mais il doit y avoir une solution plus élégante :

CASE
  WHEN Date1 IS NULL AND Date2 IS NULL AND Date3 IS NULL AND Date4 IS NULL THEN 'correct'
  WHEN Date1 IS NULL AND Date2 IS NULL AND Date3 IS NULL AND Date4 IS NOT NULL THEN 'correct'
  WHEN Date1 IS NULL AND Date2 IS NULL AND Date3 IS NOT NULL AND Date4 IS NULL THEN 'correct'
  WHEN Date1 IS NULL AND Date2 IS NULL AND Date3 IS NOT NULL AND Date4 IS NOT NULL AND Date3 < Date4 THEN 'correct'
  ...
  ELSE 'incorrect'
END

Des idées ?

EDIT :
Je cherche une solution qui permette d'avoir plus de colonnes 'Date' que les trois colonnes du premier exemple que j'ai donné ci-dessus (j'en ai quatre dans mon problème réel, et je les ai changées en trois pour simplifier le problème, mais il semble que j'ai perdu une caractéristique importante avec cette simplification). Mise à jour de l'exemple pour quatre colonnes.

Answer 1

Vous pouvez utiliser ISNULL y COALESCE pour ne pas tenir compte des valeurs nulles. Si la date est manquante, il suffit de la remplacer par une date qui passera toujours le contrôle :

CASE
  WHEN (ISNULL(Date1, '01/01/1900') < COALESCE(Date2, Date3, Date4, '01/01/3000'))
   AND (ISNULL(Date2, '01/01/1900') < COALESCE(Date3, Date4, '01/01/3000'))
   AND (ISNULL(Date3, '01/01/1900') < COALESCE(Date4, '01/01/3000'))
  THEN 'correct'
  ELSE 'incorrect'
END

Cela suppose que vos dates "réelles" ne sortent jamais de la fourchette 1900 - 3000, bien sûr ; le bug du prochain millénaire ne demande qu'à se produire ;)

EDIT : Modifié pour gérer quatre champs

Answer 2

Vous pouvez utiliser le cas sur le select en comparant les dates et utiliser la fonction ISNULL qui retourne le premier paramètre s'il n'est pas nul ou le second si le premier est nul.

Dans ce cas, j'ai défini les dates de début comme une date antérieure et la date de fin comme une date antérieure.

select date1,date2,date3,
     case
        when isnull(date1,'01/01/1900') <isnull(date2,'01/01/2100') and isnull(date2,'01/01/1900') <isnull(date3,'2100') then 'OK'
        else 'not OK'
    end
from testDates

Answer 3

Voici une autre approche :

WITH data (
  RecordID,  Date1      , Date2      , Date3      , Date4
) AS (
  SELECT 1, '2011-05-10','2011-08-16', NULL       ,'2011-11-22' UNION ALL
  SELECT 2,  NULL       ,'2012-02-03','2012-02-27','2012-03-05' UNION ALL
  SELECT 3, '2011-05-30','2011-05-11','2011-08-17','2011-09-15' UNION ALL
  SELECT 4, '2011-05-30', NULL       , NULL       , NULL
)
SELECT
  *,
  CheckDates = (
    SELECT
      MAX(CASE IdRank WHEN DateRank THEN 'correct' ELSE 'incorrect' END)
    FROM (
      SELECT
        IdRank   = ROW_NUMBER() OVER (ORDER BY ID),
        DateRank = ROW_NUMBER() OVER (ORDER BY Date)
      FROM (
        VALUES
          (1, Date1),
          (2, Date2),
          (3, Date3),
          (4, Date4)
      ) s (ID, Date)
      WHERE Date IS NOT NULL
    ) s
  )
FROM data

Voici le résultat :

RecordID    Date1      Date2      Date3      Date4      CheckDates
----------- ---------- ---------- ---------- ---------- ----------
1           2011-05-10 2011-08-16 NULL       2011-11-22 correct
2           NULL       2012-02-03 2012-02-27 2012-03-05 correct
3           2011-05-30 2011-05-11 2011-08-17 2011-09-15 incorrect
4           2011-05-30 NULL       NULL       NULL       correct

L'expression de vérification est plus compliquée que celle de l'exemple suivant La réponse de @njr mais, je suppose que cela s'avère payant lorsque vous devez mettre à l'échelle le script pour prendre en charge davantage de colonnes : il vous suffit d'ajouter de nouvelles lignes après l'élément VALUES clause.