Query for extract many visions at same time

[coloric]

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 ;)

[dnoeth · Accepted 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;

[coloric]

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 ;)

[dnoeth]

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'