Geek asks and answers

Check zero date values ​​for correct order

I have a dataset with dates and want to check if the date order is 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

In all cases when dates are given, this should be done: Date1 <Date2 <Date3 <Date4. When a record contains NULL values ​​for specific dates, validation must be performed between dates that are not NULL. Therefore I want:

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

I wrote an extensive CASE statement for this, but there should be a more elegant solution:

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

Any ideas?

EDIT:
, "", , ( , , , ). .

+5
3

ISNULL COALESCE, . , , :

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

, "" 1900 - 3000, ; , , ;)

EDIT:

+7

ISNULL, , null , firs null.

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
+1

Here's a different approach:

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

Here's the conclusion:

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

The validation expression is more complex than in @njr's answer , but I think it pays off when you need to scale the script to support more columns: you just need to add new lines after the sentence VALUES.

+1
source

More articles:


All Articles