Mailer messed up the format on a very important space... retry:
-- Script Items: 4 Parameter Count: 0
-- 2017-04-12 13:43:15.875 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TEMPORARY TABLE Volume_Information(
ID INTEGER PRIMARY KEY,
VI_Creation_Date TEXT
);
INSERT INTO Volume_Information(VI_Creation_Date) VALUES
('10/30/2015 2:28:30 AM'),
('2/13/2016 7:51:04 AM'),
('5/15/2016 12:06:24 PM'),
('10/7/2016 1:27:13 PM'),
('3/3/2017 1:7:3 PM'),
('10/1/2016 6:59:18 AM'),
('10/09/2016 11:27:13 PM');
WITH DA(ID,DT) AS (
SELECT ID, replace(replace(replace(replace(
VI_Creation_Date,' ', ':'), '/', ':'
), 'AM', '0'), 'PM', '12')||':'
FROM Volume_Information
), DB(i, k, l, c, r) AS (
SELECT DA.ID, 0, 1, DA.DT, -1
FROM DA
UNION ALL
SELECT i, k+1, instr(c, ':'), substr(c, instr( c, ':' ) + 1),
CAST(trim(substr(c, 1, instr(c, ':') - 1)) AS INT)
FROM DB
WHERE l > 0
), DC(ID, YY, MM, DD, HH, NN, SS, AP) AS (
SELECT DA.ID,
MAX(CASE WHEN k=3 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=1 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=2 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=4 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=5 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=6 THEN DB.r ELSE 0 END),
MAX(CASE WHEN k=7 THEN DB.r ELSE 0 END)
FROM DA, DB
WHERE DA.ID = DB.i AND DB.r >= 0 AND DB.k > 0
GROUP BY DA.ID
), DD(ID, ISO_DT) AS (
SELECT ID, YY||'-'||
CASE WHEN MM > 9 THEN MM ELSE '0'||MM END||'-'||
CASE WHEN DD > 9 THEN DD ELSE '0'||DD END||' '||
CASE
WHEN HH = 12 AND AP = 0 THEN '00'
WHEN HH = 12 AND AP > 0 THEN AP
WHEN HH + AP > 9 THEN HH + AP
ELSE '0'||HH
END||':'||
CASE WHEN NN > 9 THEN NN ELSE '0'||NN END||':'||
CASE WHEN SS > 9 THEN SS ELSE '0'||SS END
FROM DC
)
SELECT VI.ID, VI.VI_Creation_Date, DD.ISO_DT,
printf('%0.1f',julianday('now')-julianday(DD.ISO_DT)) AS DaysSince
FROM Volume_Information AS VI
JOIN DD ON DD.ID = VI.ID
ORDER BY VI.ID
;
-- VI.ID | VI.VI_Creation_Date | DD.ISO_DT | DaysSince
-- ----- | ----------------------- | ------------------- | ---------
-- 1 | 10/30/2015 2:28:30 AM | 2015-10-30 02:28:30 | 530.4
-- 2 | 2/13/2016 7:51:04 AM | 2016-02-13 07:51:04 | 424.2
-- 3 | 5/15/2016 12:06:24 PM | 2016-05-15 12:06:24 | 332.0
-- 4 | 10/7/2016 1:27:13 PM | 2016-10-07 13:27:13 | 186.9
-- 5 | 3/3/2017 1:7:3 PM | 2017-03-03 13:07:03 | 39.9
-- 6 | 10/1/2016 6:59:18 AM | 2016-10-01 06:59:18 | 193.2
-- 7 | 10/09/2016 11:27:13 PM | 2016-10-09 23:27:13 | 184.5
DROP TABLE Volume_Information;
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.022s
-- Total Script Query Time: -- --- --- ---
--.----
-- Total Database Rows Changed: 7
-- Total Virtual-Machine Steps: 6304
-- Last executed Item Index: 4
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
