Hello!
I have to check how many orders were waiting for delivery at specific times. I create a query to check for one time:
SELECT '2018-02-13 20:00' AS EXTRACT_DTTM, A.DELIVERY_TIME, COUNT(*) AS QTY FROM ( SELECT ORDER_NUMBER, ORDER_DTTM, DELIVERY_DTTM, CASE WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '01. 00 TO 06 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '02. 06 TO 12 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '03. 12 TO 18 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '04. 18 TO 24 HOURS' ELSE '05. > 24 HOURS ' END AS DELIVERY_TIME WHERE ORDER_DTTM <= '2018-02-13 20:00' AND DELIVERY_DTTM > '2018-02-13 20:00' ) A GROUP BY A.DELIVERY_TIME ORDER BY A.DELIVERY_TIME;
and I got this:
EXTRACT_DTTM DELIVERY_TIME QTY 2018-02-13 20:00 01. 00 TO 06 HOURS 2541 2018-02-13 20:00 02. 06 TO 12 HOURS 874 2018-02-13 20:00 03. 12 TO 18 HOURS 211 2018-02-13 20:00 04. 18 TO 24 HOURS 35 2018-02-13 20:00 05. > 24 HOURS 3
How to extract many datetimes at same time?
I tried to create a table with many datetimes to extract it but I can't create a join to get it. I need to create a graphic with a daily vision at 08:00 PM.
Thanks in advice ;)
Solved! Go to Solution.
You need to create a table with the timestamps you want and then you CROSS JOIN:
CREATE TABLE myTableWithTimestamps (EXTRACT_DTTM TIMESTAMP(0));
-- some Inserts
SELECT A.EXTRACT_DTTM, A.DELIVERY_TIME, COUNT(*) AS QTY FROM ( SELECT X.EXTRACT_DTTM,
ORDER_NUMBER, ORDER_DTTM, DELIVERY_DTTM, CASE WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '01. 00 TO 06 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '02. 06 TO 12 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '03. 12 TO 18 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '04. 18 TO 24 HOURS' ELSE '05. > 24 HOURS ' END AS DELIVERY_TIME
FROM orders CROSS JOIN myTableWithTimestamps as X WHERE ORDER_DTTM <= X.EXTRACT_DTTM AND DELIVERY_DTTM > X.EXTRACT_DTTM ) A GROUP BY A.DELIVERY_TIME ORDER BY A.DELIVERY_TIME;
You need to create a table with the timestamps you want and then you CROSS JOIN:
CREATE TABLE myTableWithTimestamps (EXTRACT_DTTM TIMESTAMP(0));
-- some Inserts
SELECT A.EXTRACT_DTTM, A.DELIVERY_TIME, COUNT(*) AS QTY FROM ( SELECT X.EXTRACT_DTTM,
ORDER_NUMBER, ORDER_DTTM, DELIVERY_DTTM, CASE WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '01. 00 TO 06 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '02. 06 TO 12 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '03. 12 TO 18 HOURS' WHEN CAST(((CAST('2018-02-13 20:00' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI')-ORDER_DTTM) HOUR(4)) AS NUMBER) BETWEEN 0 AND 6 THEN '04. 18 TO 24 HOURS' ELSE '05. > 24 HOURS ' END AS DELIVERY_TIME
FROM orders CROSS JOIN myTableWithTimestamps as X WHERE ORDER_DTTM <= X.EXTRACT_DTTM AND DELIVERY_DTTM > X.EXTRACT_DTTM ) A GROUP BY A.DELIVERY_TIME ORDER BY A.DELIVERY_TIME;
Thank you, Dieter!
I just put X.EXTRACT_DTTM in the CASE WHEN too and it worked!
Sorry, I miss the FROM in my query ;)
Yup, I forgot to modify the CASE, glad it worked out for you :-)
Btw, instead of CASTing string to a timestamp better use a Timestamp Literal:
TIMESTAMP '2018-02-13 20:00:00'