当前位置： 文江博客话题详情

重新格式化结果，将列转换为行，mysql

发布于 2025-01-28 10:52:16 字数 12479 浏览 3 评论 0原文

我的同事写了一个很棒的查询，解决了人们不断登录和不断登录的情况，因此，寻找今天上午10点可用的“供应时间”可能很棘手，因为有人可能没有登记， 。因此，在此查询之前，我们可以在当天，每周，月份的一天中看到事情，但每小时都不是。查询在问题的末尾

，以下是结果的样子：

查询结果>

您会看到每小时的列扩展到23小时。为了使加入在Tableau的工作所需的内容是显示出来的结果： Col 1市场，Col 2日期，Col 3小时，Col 4供应小时总和 ATL，1-3-2022，3，12 ATL，1-3-2022、4、4 ATL，1-3-2022、5、7

等目前在小时列中分布的所有条目。我是SQL的新手，任何帮助都将不胜感激。

谢谢你，

哈登

DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE AS DATE,
SUM(HOUR_0_SAME_DAY) AS '0',
SUM(HOUR_1_SAME_DAY) AS '1',
SUM(HOUR_2_SAME_DAY) AS '2',
SUM(HOUR_3_SAME_DAY) AS '3',
SUM(HOUR_4_SAME_DAY) AS '4',
SUM(HOUR_5_SAME_DAY) AS '5',
SUM(HOUR_6_SAME_DAY) AS '6',
SUM(HOUR_7_SAME_DAY) AS '7',
SUM(HOUR_8_SAME_DAY) AS '8',
SUM(HOUR_9_SAME_DAY) AS '9',
SUM(HOUR_10_SAME_DAY) AS '10',
SUM(HOUR_11_SAME_DAY) AS '11',
SUM(HOUR_12_SAME_DAY) AS '12',
SUM(HOUR_13_SAME_DAY) AS '13',
SUM(HOUR_14_SAME_DAY) AS '14',
SUM(HOUR_15_SAME_DAY) AS '15',
SUM(HOUR_16_SAME_DAY) AS '16',
SUM(HOUR_17_SAME_DAY) AS '17',
SUM(HOUR_18_SAME_DAY) AS '18',
SUM(HOUR_19_SAME_DAY) AS '19',
SUM(HOUR_20_SAME_DAY) AS '20',
SUM(HOUR_21_SAME_DAY) AS '21',
SUM(HOUR_22_SAME_DAY) AS '22',
SUM(HOUR_23_SAME_DAY) AS '23'

FROM
(SELECT
SUPPLYHRS.driver_id,
MARKET.market_name,
DATE(SUPPLYHRS.onduty_time) AS ONDUTY_DATE,

CASE WHEN 0 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_0_SAME_DAY',
CASE WHEN 1 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_1_SAME_DAY',
CASE WHEN 2 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_2_SAME_DAY',
CASE WHEN 3 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_3_SAME_DAY',
CASE WHEN 4 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_4_SAME_DAY',
CASE WHEN 5 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_5_SAME_DAY',
CASE WHEN 6 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_6_SAME_DAY',
CASE WHEN 7 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_7_SAME_DAY',
CASE WHEN 8 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_8_SAME_DAY',
CASE WHEN 9 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_9_SAME_DAY',
CASE WHEN 10 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_10_SAME_DAY',
CASE WHEN 11 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_11_SAME_DAY',
CASE WHEN 12 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_12_SAME_DAY',
CASE WHEN 13 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_13_SAME_DAY',
CASE WHEN 14 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_14_SAME_DAY',
CASE WHEN 15 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_15_SAME_DAY',
CASE WHEN 16 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_16_SAME_DAY',
CASE WHEN 17 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_17_SAME_DAY',
CASE WHEN 18 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_18_SAME_DAY',
CASE WHEN 19 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_19_SAME_DAY',
CASE WHEN 20 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_20_SAME_DAY',
CASE WHEN 21 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_21_SAME_DAY',
CASE WHEN 22 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_22_SAME_DAY',
CASE WHEN 23 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_23_SAME_DAY'


FROM
janus_service_driver.t_driver_duty_log SUPPLYHRS

LEFT JOIN
janus_service_driver.t_driver_area_market MARKET

ON
SUPPLYHRS.driver_id = MARKET.Driver_ID

WHERE
DATE(SUPPLYHRS.onduty_time) BETWEEN DATE('2022-01-01') AND DATE(SYSDATE()) AND
SUPPLYHRS.offduty_time IS NOT NULL

GROUP BY
SUPPLYHRS.driver_id) DATA

WHERE
market_name IS NOT NULL

GROUP BY
DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE

UNION

SELECT
DATA.market_name,
# DATA.driver_id,
DATE_ADD(DATA.ONDUTY_DATE,interval 1 day) AS "DATE",
SUM(HOUR_0_NEXT_DAY) AS '0',
SUM(HOUR_1_NEXT_DAY) AS '1',
SUM(HOUR_2_NEXT_DAY) AS '2',
SUM(HOUR_3_NEXT_DAY) AS '3',
SUM(HOUR_4_NEXT_DAY) AS '4',
SUM(HOUR_5_NEXT_DAY) AS '5',
SUM(HOUR_6_NEXT_DAY) AS '6',
SUM(HOUR_7_NEXT_DAY) AS '7',
SUM(HOUR_8_NEXT_DAY) AS '8',
SUM(HOUR_9_NEXT_DAY) AS '9',
SUM(HOUR_10_NEXT_DAY) AS '10',
SUM(HOUR_11_NEXT_DAY) AS '11',
SUM(HOUR_12_NEXT_DAY) AS '12',
SUM(HOUR_13_NEXT_DAY) AS '13',
SUM(HOUR_14_NEXT_DAY) AS '14',
SUM(HOUR_15_NEXT_DAY) AS '15',
SUM(HOUR_16_NEXT_DAY) AS '16',
SUM(HOUR_17_NEXT_DAY) AS '17',
SUM(HOUR_18_NEXT_DAY) AS '18',
SUM(HOUR_19_NEXT_DAY) AS '19',
SUM(HOUR_20_NEXT_DAY) AS '20',
SUM(HOUR_21_NEXT_DAY) AS '21',
SUM(HOUR_22_NEXT_DAY) AS '22',
SUM(HOUR_23_NEXT_DAY) AS '23'

FROM
(SELECT
SUPPLYHRS.driver_id,
MARKET.market_name,
DATE(SUPPLYHRS.onduty_time) AS ONDUTY_DATE,

CASE WHEN 0 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_0_NEXT_DAY',
CASE WHEN 1 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_1_NEXT_DAY',
CASE WHEN 2 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_2_NEXT_DAY',
CASE WHEN 3 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_3_NEXT_DAY',
CASE WHEN 4 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_4_NEXT_DAY',
CASE WHEN 5 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_5_NEXT_DAY',
CASE WHEN 6 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_6_NEXT_DAY',
CASE WHEN 7 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_7_NEXT_DAY',
CASE WHEN 8 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_8_NEXT_DAY',
CASE WHEN 9 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_9_NEXT_DAY',
CASE WHEN 10 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_10_NEXT_DAY',
CASE WHEN 11 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_11_NEXT_DAY',
CASE WHEN 12 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_12_NEXT_DAY',
CASE WHEN 13 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_13_NEXT_DAY',
CASE WHEN 14 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_14_NEXT_DAY',
CASE WHEN 15 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_15_NEXT_DAY',
CASE WHEN 16 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_16_NEXT_DAY',
CASE WHEN 17 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_17_NEXT_DAY',
CASE WHEN 18 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_18_NEXT_DAY',
CASE WHEN 19 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_19_NEXT_DAY',
CASE WHEN 20 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_20_NEXT_DAY',
CASE WHEN 21 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_21_NEXT_DAY',
CASE WHEN 22 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_22_NEXT_DAY',
CASE WHEN 23 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_23_NEXT_DAY'

FROM
janus_service_driver.t_driver_duty_log SUPPLYHRS

LEFT JOIN
janus_service_driver.t_driver_area_market MARKET

ON
SUPPLYHRS.driver_id = MARKET.Driver_ID

WHERE
DATE(SUPPLYHRS.onduty_time) BETWEEN DATE('2022-01-01') AND DATE(SYSDATE()) AND
SUPPLYHRS.offduty_time IS NOT NULL

GROUP BY
SUPPLYHRS.driver_id) DATA

WHERE
market_name IS NOT NULL

GROUP BY
DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE
原文

My coworker wrote an awesome query that solves for discreet hours with a situation in which people are logging-in and out continually, so looking for how many 'supply hours' were available at 10 AM today may be tricky since someone may not have logged out. So, prior to this query, we could see things in aggregate at the day, weekly, month level, but not really hourly. The Query is at the end of the question

And here are what the results look like:

Query Results IDE

You'll see the hourly columns extend to hour 23. What I need in order to make joins work in Tableau, is to have results that show:
col 1 Market, col 2 Date, col 3 Hour, col 4 Supply Hour Sum
ATL, ,1-3-2022, 3 , 12
ATL ,1-3-2022, 4 , 4
ATL ,1-3-2022, 5 , 7

And so on for all entries currently spread out in the hours columns. I'm fairly new to sql and any help would be much appreciated.

Thank you,

Hadden

DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE AS DATE,
SUM(HOUR_0_SAME_DAY) AS '0',
SUM(HOUR_1_SAME_DAY) AS '1',
SUM(HOUR_2_SAME_DAY) AS '2',
SUM(HOUR_3_SAME_DAY) AS '3',
SUM(HOUR_4_SAME_DAY) AS '4',
SUM(HOUR_5_SAME_DAY) AS '5',
SUM(HOUR_6_SAME_DAY) AS '6',
SUM(HOUR_7_SAME_DAY) AS '7',
SUM(HOUR_8_SAME_DAY) AS '8',
SUM(HOUR_9_SAME_DAY) AS '9',
SUM(HOUR_10_SAME_DAY) AS '10',
SUM(HOUR_11_SAME_DAY) AS '11',
SUM(HOUR_12_SAME_DAY) AS '12',
SUM(HOUR_13_SAME_DAY) AS '13',
SUM(HOUR_14_SAME_DAY) AS '14',
SUM(HOUR_15_SAME_DAY) AS '15',
SUM(HOUR_16_SAME_DAY) AS '16',
SUM(HOUR_17_SAME_DAY) AS '17',
SUM(HOUR_18_SAME_DAY) AS '18',
SUM(HOUR_19_SAME_DAY) AS '19',
SUM(HOUR_20_SAME_DAY) AS '20',
SUM(HOUR_21_SAME_DAY) AS '21',
SUM(HOUR_22_SAME_DAY) AS '22',
SUM(HOUR_23_SAME_DAY) AS '23'

FROM
(SELECT
SUPPLYHRS.driver_id,
MARKET.market_name,
DATE(SUPPLYHRS.onduty_time) AS ONDUTY_DATE,

CASE WHEN 0 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_0_SAME_DAY',
CASE WHEN 1 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_1_SAME_DAY',
CASE WHEN 2 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_2_SAME_DAY',
CASE WHEN 3 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_3_SAME_DAY',
CASE WHEN 4 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_4_SAME_DAY',
CASE WHEN 5 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_5_SAME_DAY',
CASE WHEN 6 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_6_SAME_DAY',
CASE WHEN 7 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_7_SAME_DAY',
CASE WHEN 8 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_8_SAME_DAY',
CASE WHEN 9 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_9_SAME_DAY',
CASE WHEN 10 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_10_SAME_DAY',
CASE WHEN 11 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_11_SAME_DAY',
CASE WHEN 12 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_12_SAME_DAY',
CASE WHEN 13 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_13_SAME_DAY',
CASE WHEN 14 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_14_SAME_DAY',
CASE WHEN 15 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_15_SAME_DAY',
CASE WHEN 16 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_16_SAME_DAY',
CASE WHEN 17 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_17_SAME_DAY',
CASE WHEN 18 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_18_SAME_DAY',
CASE WHEN 19 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_19_SAME_DAY',
CASE WHEN 20 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_20_SAME_DAY',
CASE WHEN 21 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_21_SAME_DAY',
CASE WHEN 22 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_22_SAME_DAY',
CASE WHEN 23 BETWEEN HOUR(SUPPLYHRS.onduty_time) AND CASE WHEN DATE(SUPPLYHRS.onduty_time) = DATE(SUPPLYHRS.offduty_time) THEN HOUR(SUPPLYHRS.offduty_time) ELSE 23 END THEN 1 ELSE 0 END AS 'HOUR_23_SAME_DAY'


FROM
janus_service_driver.t_driver_duty_log SUPPLYHRS

LEFT JOIN
janus_service_driver.t_driver_area_market MARKET

ON
SUPPLYHRS.driver_id = MARKET.Driver_ID

WHERE
DATE(SUPPLYHRS.onduty_time) BETWEEN DATE('2022-01-01') AND DATE(SYSDATE()) AND
SUPPLYHRS.offduty_time IS NOT NULL

GROUP BY
SUPPLYHRS.driver_id) DATA

WHERE
market_name IS NOT NULL

GROUP BY
DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE

UNION

SELECT
DATA.market_name,
# DATA.driver_id,
DATE_ADD(DATA.ONDUTY_DATE,interval 1 day) AS "DATE",
SUM(HOUR_0_NEXT_DAY) AS '0',
SUM(HOUR_1_NEXT_DAY) AS '1',
SUM(HOUR_2_NEXT_DAY) AS '2',
SUM(HOUR_3_NEXT_DAY) AS '3',
SUM(HOUR_4_NEXT_DAY) AS '4',
SUM(HOUR_5_NEXT_DAY) AS '5',
SUM(HOUR_6_NEXT_DAY) AS '6',
SUM(HOUR_7_NEXT_DAY) AS '7',
SUM(HOUR_8_NEXT_DAY) AS '8',
SUM(HOUR_9_NEXT_DAY) AS '9',
SUM(HOUR_10_NEXT_DAY) AS '10',
SUM(HOUR_11_NEXT_DAY) AS '11',
SUM(HOUR_12_NEXT_DAY) AS '12',
SUM(HOUR_13_NEXT_DAY) AS '13',
SUM(HOUR_14_NEXT_DAY) AS '14',
SUM(HOUR_15_NEXT_DAY) AS '15',
SUM(HOUR_16_NEXT_DAY) AS '16',
SUM(HOUR_17_NEXT_DAY) AS '17',
SUM(HOUR_18_NEXT_DAY) AS '18',
SUM(HOUR_19_NEXT_DAY) AS '19',
SUM(HOUR_20_NEXT_DAY) AS '20',
SUM(HOUR_21_NEXT_DAY) AS '21',
SUM(HOUR_22_NEXT_DAY) AS '22',
SUM(HOUR_23_NEXT_DAY) AS '23'

FROM
(SELECT
SUPPLYHRS.driver_id,
MARKET.market_name,
DATE(SUPPLYHRS.onduty_time) AS ONDUTY_DATE,

CASE WHEN 0 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_0_NEXT_DAY',
CASE WHEN 1 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_1_NEXT_DAY',
CASE WHEN 2 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_2_NEXT_DAY',
CASE WHEN 3 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_3_NEXT_DAY',
CASE WHEN 4 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_4_NEXT_DAY',
CASE WHEN 5 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_5_NEXT_DAY',
CASE WHEN 6 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_6_NEXT_DAY',
CASE WHEN 7 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_7_NEXT_DAY',
CASE WHEN 8 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_8_NEXT_DAY',
CASE WHEN 9 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_9_NEXT_DAY',
CASE WHEN 10 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_10_NEXT_DAY',
CASE WHEN 11 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_11_NEXT_DAY',
CASE WHEN 12 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_12_NEXT_DAY',
CASE WHEN 13 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_13_NEXT_DAY',
CASE WHEN 14 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_14_NEXT_DAY',
CASE WHEN 15 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_15_NEXT_DAY',
CASE WHEN 16 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_16_NEXT_DAY',
CASE WHEN 17 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_17_NEXT_DAY',
CASE WHEN 18 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_18_NEXT_DAY',
CASE WHEN 19 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_19_NEXT_DAY',
CASE WHEN 20 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_20_NEXT_DAY',
CASE WHEN 21 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_21_NEXT_DAY',
CASE WHEN 22 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_22_NEXT_DAY',
CASE WHEN 23 BETWEEN CASE WHEN DATE(SUPPLYHRS.onduty_time) <> DATE(SUPPLYHRS.offduty_time) THEN 0 END AND HOUR(SUPPLYHRS.offduty_time) THEN 1 ELSE 0 END AS 'HOUR_23_NEXT_DAY'

FROM
janus_service_driver.t_driver_duty_log SUPPLYHRS

LEFT JOIN
janus_service_driver.t_driver_area_market MARKET

ON
SUPPLYHRS.driver_id = MARKET.Driver_ID

WHERE
DATE(SUPPLYHRS.onduty_time) BETWEEN DATE('2022-01-01') AND DATE(SYSDATE()) AND
SUPPLYHRS.offduty_time IS NOT NULL

GROUP BY
SUPPLYHRS.driver_id) DATA

WHERE
market_name IS NOT NULL

GROUP BY
DATA.market_name,
# DATA.driver_id,
DATA.ONDUTY_DATE

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。
列表为空，暂无数据

关于作者

柏林苍穹下

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

alipaysp_snBf0MSZIv

文章 0 评论 0

梦断已成空

文章 0 评论 0

瞎闹

文章 0 评论 0

凯凯我们等你回来

文章 0 评论 0

寄意

文章 0 评论 0

似梦非梦

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文