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