Computing dates and counts

Question

I have a big SQL request where I compute dates or counts (from other tables), and I have to compute new dates based on conditions on those pre-computed dates and counts.

In the following example, I compute comp_nactive and comp_date_last_completed, and I use those to compute comp_date_next_todo.

SELECT
    pms_id,
    (
        SELECT
            COUNT(DISTINCT date_assigned)
        FROM wrhwr
        WHERE pms_id = outer_pms.pms_id
              AND date_completed IS NULL
    ) AS comp_nactive,
    (
        SELECT
            CONVERT( DATE, MAX(date_completed))
        FROM wrhwr
        WHERE pms_id = outer_pms.pms_id
    ) AS comp_date_last_completed,
    CONVERT( DATE, date_first_todo) AS date_first_todo,
    CASE
        -- dateLastCompleted == null
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'd'    AND DATEADD(d, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'ww'   AND DATEADD(ww, interval, date_first_todo)   <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'm'    AND DATEADD(m, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'q'    AND DATEADD(q, interval, date_first_todo)    <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN (SELECT CONVERT( DATE, MAX(date_completed)) FROM wrhwr WHERE pms_id = outer_pms.pms_id) IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) <= GETDATE() AND (SELECT COUNT(DISTINCT date_assigned) FROM wrhwr WHERE pms_id = outer_pms.pms_id AND date_completed IS NULL) > 0 THEN CONVERT(DATE, date_first_todo)
    END AS comp_date_next_todo
FROM pms outer_pms

The only solution I found so far was copy/pasting the code, as I can't use comp_nactive (for example) in the rest of the request. Though it works, it's quite ugly and very difficult to manage.

I guess it's possible to be cleaner and smarter. Any hint?

I want to avoid functions as much as possible, as I don't always have authorizations to create such. The code should, if possible, work on both SQL Server and Oracle, as I need it for both DB flavors. Small dataset:

CREATE TABLE pms
([pms_id] varchar(9), [date_first_todo] datetime, [interval] int, [interval_type] varchar(4));

INSERT INTO pms ([pms_id], [date_first_todo], [interval], [interval_type])
VALUES
('CHECK-1M', '2017-01-05 01:00:00', 1, 'm'),
('CHANGE-1Y', '2017-02-06 01:00:00', 1, 'yyyy');

CREATE TABLE wrhwr
([pms_id] varchar(8), [date_assigned] datetime, [date_completed] datetime);

INSERT INTO wrhwr ([pms_id], [date_assigned], [date_completed])
VALUES
('CHECK-1M', '2017-01-05 01:00:00', '2017-01-07 01:00:00'),
('CHECK-1M', '2017-02-05 01:00:00', '2017-02-13 01:00:00'),
('CHECK-1M', '2017-03-05 01:00:00', NULL);

Expected output:

CHECK-1M    1       2016-02-13      2017-01-05      NULL
CHANGE-1Y   0       NULL            2017-02-06      2017-02-06

Any chance you can provide a small sample dataset and expected output? — scsimon, May 23 '17 at 15:20
I'm not a SQL expert then I suppose you will get better answers but...did you first try to refactor that to use CTEs? — Adriano Repetti, May 26 '17 at 10:20
That seems like a very interesting tip. However, it seems that CTE are quite different (in syntax) in Oracle, and the code should, if possible, work on both SQL Server and Oracle. Thanks! — user3341592, May 26 '17 at 12:39
Add that to your question, you will get much better and focused answers! There are some pretty smart "SQL guys" here. — Adriano Repetti, May 26 '17 at 13:09
This is almost impossible to decipher--specifically that case statement. 99.9% sure it's easier done, and i can do it easier already with the test data but it's surely going to miss some logic. Can you explain logically why / how you determine what to calculate for the final column? — scsimon, May 30 '17 at 19:06

coyote · Accepted Answer · 2017-06-23 15:59:54Z

Given your sample data, this is what I could come up with off the cuff.

While likely not an optimal solution, it should be a step in the right direction and I'm sure you can take it from here. I also made some assumptions about nullity since this wasn't explicitly provided in your sample set. You may be able to thus trim down or modify the comp_date_next_todo definition accordingly, since some extra NULL checks are made.

SELECT
    outer_pms.pms_id,
    incmp_wrhwr.cnt_date_assigned AS comp_nactive,
    oa_wrhwr.comp_date_last_completed ,
    CONVERT(DATE, date_first_todo) AS date_first_todo, 
 CASE WHEN oa_wrhwr.comp_date_last_completed IS NULL
    AND date_first_todo IS NOT NULL 
    AND interval IS NOT NULL THEN
        -- dateLastCompleted == null
        CASE WHEN interval_type = 'd'   AND DATEADD(d, interval, date_first_todo)       > GETDATE() THEN CONVERT(DATE, date_first_todo)                                 
             WHEN interval_type = 'ww'  AND DATEADD(ww, interval, date_first_todo)      > GETDATE() THEN CONVERT(DATE, date_first_todo)
             WHEN interval_type = 'm'   AND DATEADD(m, interval, date_first_todo)       > GETDATE() THEN CONVERT(DATE, date_first_todo)             
             WHEN interval_type = 'q'   AND DATEADD(q, interval, date_first_todo)       > GETDATE() THEN CONVERT(DATE, date_first_todo)                 
             WHEN interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo)   > GETDATE() THEN CONVERT(DATE, date_first_todo)             
        ELSE CASE 
                WHEN cnt_date_assigned = 0 THEN CONVERT(DATE, GETDATE())
                WHEN cnt_date_assigned > 0 THEN CONVERT(DATE, date_first_todo)
            END
        END 
    END AS comp_date_next_todo
FROM pms outer_pms
OUTER APPLY (SELECT  CONVERT( DATE, MAX(date_completed)) comp_date_last_completed 
            FROM wrhwr 
            WHERE wrhwr.pms_id = outer_pms.pms_id) oa_wrhwr
OUTER APPLY (SELECT COUNT(date_assigned) cnt_date_assigned
            FROM wrhwr WHERE wrhwr.pms_id = outer_pms.pms_id AND date_completed IS NULL) incmp_wrhwr;

The only real points to be made here are OUTER APPLYs replacing our redundant subqueries, and collapsing our CASE WHEN(s) into something easier to maintain and read based on the requirements as I understood them. Unfortunately DATEADD will not accept a string for the first parameter, or else we could collapse this even further. Hope this helps!

EDIT: Here's another solution that uses ORs instead for readability and moves the cnt_date_assigned > 0 check to the primary CASE WHEN. However, for SQL Server 2008 R2, the execution plans are identical, thus you should go with what is more maintainable and readable for you and your team. I'm providing it as an example of different means to the same end. :)

CASE WHEN oa_wrhwr.comp_date_last_completed IS NULL
    AND date_first_todo IS NOT NULL 
    AND interval IS NOT NULL         
   THEN CASE WHEN (
                    (interval_type = 'd'   AND DATEADD(d, interval, date_first_todo) > GETDATE())                                 
                    OR (interval_type = 'ww'  AND DATEADD(ww, interval, date_first_todo) > GETDATE())     
                    OR (interval_type = 'm'   AND DATEADD(m, interval, date_first_todo) > GETDATE())                
                    OR (interval_type = 'q'   AND DATEADD(q, interval, date_first_todo) > GETDATE())                   
                    OR (interval_type = 'yyyy' AND DATEADD(yyyy, interval, date_first_todo) > GETDATE())
                    OR cnt_date_assigned > 0
                )
                THEN CONVERT(DATE, date_first_todo)    
        WHEN cnt_date_assigned = 0 
                THEN CONVERT(DATE, GETDATE())            
    END 
END AS comp_date_next_todo

EDIT 2: Last time, promise! I personally prefer this version over the other two.

CASE WHEN oa_wrhwr.comp_date_last_completed IS NULL
    AND date_first_todo IS NOT NULL 
    AND interval IS NOT NULL 
THEN CASE WHEN 
        (CASE WHEN interval_type = 'd'   THEN DATEADD(d, interval, date_first_todo)     
                WHEN interval_type = 'ww'  THEN DATEADD(ww, interval, date_first_todo) 
                WHEN interval_type = 'm'   THEN DATEADD(m, interval, date_first_todo)            
                WHEN interval_type = 'q'   THEN DATEADD(q, interval, date_first_todo)               
                WHEN interval_type = 'yyyy' THEN  DATEADD(yyyy, interval, date_first_todo) END)   > GETDATE()
        OR cnt_date_assigned > 0
    THEN CONVERT(DATE, date_first_todo)             
    WHEN cnt_date_assigned = 0 
            THEN CONVERT(DATE, GETDATE())            
    END 
END AS comp_date_next_todo

dnoeth · Accepted Answer · 2017-06-24 15:35:08Z

You always have to refactor your code due to the usage of date/time functions which usually differ between DBMSes, but the CTE-syntax is the same for SQL Server & Oracle. Besides you can simply rewrite using Derived Tables: SELECT ... FROM (SELECT ...).

The first step is to write the repeated Scalar Subqueries only once using a CTE:

WITH cte AS 
 (
   SELECT
      pms_id,
      interval_type, interval,
      (
          SELECT
              Count(DISTINCT date_assigned)
          FROM wrhwr
          WHERE pms_id = outer_pms.pms_id
                AND date_completed IS NULL
      ) AS comp_nactive,
      (
          SELECT
              CONVERT( DATE, MAX(date_completed))
          FROM wrhwr
          WHERE pms_id = outer_pms.pms_id
      ) AS comp_date_last_completed,
      CONVERT( DATE, date_first_todo) AS date_first_todo
   FROM pms outer_pms
 )
SELECT 
   pms_id,
   comp_nactive,
   comp_date_last_completed,
   date_first_todo,
    CASE
        -- dateLastCompleted == null
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d, INTERVAL, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww, INTERVAL, date_first_todo)   > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww, INTERVAL, date_first_todo)   <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww, INTERVAL, date_first_todo)   <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m, INTERVAL, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q, INTERVAL, date_first_todo)    > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q, INTERVAL, date_first_todo)    <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) > GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) <= GETDATE() AND comp_nactive = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) <= GETDATE() AND comp_nactive > 0 THEN CONVERT(DATE, date_first_todo)
    END AS comp_date_next_todo
FROM cte;

And as both Scalar Subqueries are accessing the same table in a similar way (just an additional condition) you can rewrite them to a simple aggregation before joining:

WITH cte AS 
 (
   SELECT
      pms_id,
      Count(DISTINCT CASE WHEN date_completed IS NULL THEN date_assigned END) AS comp_nactive,
      CONVERT( DATE, Max(date_completed)) AS comp_date_last_completed
   FROM wrhwr 
   GROUP BY pms_id
 )
SELECT 
   pms.pms_id,
   Coalesce(comp_nactive, 0) as comp_nactive,
   comp_date_last_completed,
   date_first_todo,
    CASE
        -- dateLastCompleted == null
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d,    INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'd'    AND DATEADD(d,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww,   INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww,   INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'ww'   AND DATEADD(ww,   INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m,    INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'm'    AND DATEADD(m,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q,    INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'q'    AND DATEADD(q,    INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) >  GETDATE() THEN CONVERT(DATE, date_first_todo)
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) = 0 THEN CONVERT(DATE, GETDATE())
        WHEN comp_date_last_completed IS NULL AND interval_type = 'yyyy' AND DATEADD(yyyy, INTERVAL, date_first_todo) <= GETDATE() AND Coalesce(comp_nactive, 0) > 0 THEN CONVERT(DATE, date_first_todo)
    END AS comp_date_next_todo
FROM pms LEFT JOIN cte -- must be an Outer Join
  ON pms.pms_id = cte.pms_id

You just have to take care of NULLs returned for comp_nactive applying COALESCE.

See fiddle on rextester

Stack Exchange Network

current community

your communities

more stack exchange communities

Computing dates and counts

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged sql sql-server namespaces or ask your own question.

Hot Network Questions

Computing dates and counts

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql sql-server namespaces or ask your own question.

Related

Hot Network Questions