Query for extract many visions at same time

Database
Enthusiast

Query for extract many visions at same time

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

 

 


Accepted Solutions
Highlighted
Junior Contributor

Re: Query for extract many visions at same 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;

 

1 ACCEPTED SOLUTION
3 REPLIES
Highlighted
Junior Contributor

Re: Query for extract many visions at same 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;

 

Enthusiast

Re: Query for extract many visions at same 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 ;)

 

Junior Contributor

Re: Query for extract many visions at same time

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'