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日

• S3S: Any chance you can provide a small sample dataset and expected output?
• Adriano Repetti: 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?
• user3341592: 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!
• Adriano Repetti: Add that to your question, you will get much better and focused answers! There are some pretty smart "SQL guys" here.
• S3S: 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?

Answer 1

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

Answer 2

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