mysql 勤務データ 日付またぎのID抽出
以下のデータが用意されています。
USER_NAME,ID,START_TIME,END_TIME
AAAA,1,2017/8/9 9:00,2017/8/9 18:00
AAAA,5,2017/8/9 12:00,2017/8/9 13:00
BBBB,2,2017/8/9 22:00,2017/8/10 00:00
BBBB,2,2017/8/10 00:00,2017/8/10 07:00
BBBB,5,2017/8/10 03:00,2017/8/10 04:00
※START_TIME,END_TIMEはDATETIME型です。
ID [1]は勤務時間。[5]は1時間休憩です。
この時間を15分ごとに区切りその時間はどのIDが該当するか表示させる必要があります。
USER_NAMEがAAAAさんのデータは15分ごとにID取得ができましたが、BBBBさんの[BBBB,2,2017/8/9 22:00,2017/8/10 00:00]このようにEND_TIMEの日付が翌日になるとIDがうまく抽出できませんでした。
以下の書き方だと時間のみ抽出する形のため、抽出されないのだと思いますが、具体的にどう書き換えれば日付が翌日になった場合でもIDがうまく抽出できるのでしょうか。
SELECT distinct
USER_NAME,
ID,
case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00','Asia/Tokyo') ,'%H:%i') > '00:00' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00','Asia/Tokyo') ,'%H:%i') <'00:15')) then SCHEDULE_INTERVAL_TYPE_ID end as '00:00',
case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00','Asia/Tokyo') ,'%H:%i') > '00:15' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00','Asia/Tokyo') ,'%H:%i') <'00:30')) then SCHEDULE_INTERVAL_TYPE_ID end as '00:15',
.
.
.
. 15分ごとに時刻を変更して23:59まで続きます。
case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00','Asia/Tokyo') ,'%H:%i') > '23:30' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00','Asia/Tokyo') ,'%H:%i') <'23:45')) then SCHEDULE_INTERVAL_TYPE_ID end as '23:30',
case when (DATE_FORMAT(CONVERT_TZ(END_TIME, '+00:00','Asia/Tokyo') ,'%H:%i') > '23:45' and (DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00','Asia/Tokyo') ,'%H:%i') <'23:59')) then SCHEDULE_INTERVAL_TYPE_ID end as '23:45'
FROM テーブル1
WHERE
DATE_FORMAT(CONVERT_TZ(START_TIME, '+00:00','Asia/Tokyo') ,'%Y%m%d') = DATE_FORMAT(CONVERT_TZ(CURRENT_TIMESTAMP, '+00:00','Asia/Tokyo') ,'%Y%m%d');