Skip to content

Instantly share code, notes, and snippets.

@AgungPambudi

AgungPambudi/customer-tabulation-query-per-hour.sql

Last active August 10, 2021 06:57
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save AgungPambudi/0a04d0c33107e0992446d3dddff975fc to your computer and use it in GitHub Desktop.
Save AgungPambudi/0a04d0c33107e0992446d3dddff975fc to your computer and use it in GitHub Desktop.
Download ZIP
Raw
customer-tabulation-query-per-hour.sql
#author : Agung Pambudi
#email : mail@agungpambudi.com
#version : 0.1
#==============================================================================
# _ _ _
# ___ ___ _ _ ___ ___ ___ ___ _____| |_ _ _ _| |_| ___ ___ _____
#| .'| . | | | | . | . | .'| | . | | | . | |_| _| . | |
#|__,|_ |___|_|_|_ | _|__,|_|_|_|___|___|___|_|_|___|___|_|_|_|
# |___| |___|_|
SELECT `DATE` AS TGL,STORE,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '05:00:00' AND '05:59:59', LABAKOTOR-PPN,0)),0) AS `05:00:00-06:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '06:00:00' AND '06:59:59', LABAKOTOR-PPN,0)),0) AS `06:00:00-07:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '07:00:00' AND '07:59:59', LABAKOTOR-PPN,0)),0) AS `07:00:00-08:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '08:00:00' AND '08:59:59', LABAKOTOR-PPN,0)),0) AS `08:00:00-09:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '09:00:00' AND '09:59:59', LABAKOTOR-PPN,0)),0) AS `09:00:00-10:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '10:00:00' AND '10:59:59', LABAKOTOR-PPN,0)),0) AS `10:00:00-11:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '11:00:00' AND '11:59:59', LABAKOTOR-PPN,0)),0) AS `11:00:00-12:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '12:00:00' AND '12:59:59', LABAKOTOR-PPN,0)),0) AS `12:00:00-13:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '13:00:00' AND '13:59:59', LABAKOTOR-PPN,0)),0) AS `13:00:00-14:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '14:00:00' AND '14:59:59', LABAKOTOR-PPN,0)),0) AS `14:00:00-15:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '15:00:00' AND '15:59:59', LABAKOTOR-PPN,0)),0) AS `15:00:00-16:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '16:00:00' AND '16:59:59', LABAKOTOR-PPN,0)),0) AS `16:00:00-17:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '17:00:00' AND '17:59:59', LABAKOTOR-PPN,0)),0) AS `17:00:00-18:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '18:00:00' AND '18:59:59', LABAKOTOR-PPN,0)),0) AS `18:00:00-19:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '19:00:00' AND '19:59:59', LABAKOTOR-PPN,0)),0) AS `19:00:00-20:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '20:00:00' AND '20:59:59', LABAKOTOR-PPN,0)),0) AS `20:00:00-21:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '21:00:00' AND '21:59:59', LABAKOTOR-PPN,0)),0) AS `21:00:00-22:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '22:00:00' AND '22:59:59', LABAKOTOR-PPN,0)),0) AS `22:00:00-23:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '23:00:00' AND '23:59:59', LABAKOTOR-PPN,0)),0) AS `23:00:00-24:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '00:00:00' AND '00:59:59', LABAKOTOR-PPN,0)),0) AS `00:00:00-01:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '01:00:00' AND '01:59:59', LABAKOTOR-PPN,0)),0) AS `01:00:00-02:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '02:00:00' AND '02:59:59', LABAKOTOR-PPN,0)),0) AS `02:00:00-03:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '03:00:00' AND '03:59:59', LABAKOTOR-PPN,0)),0) AS `03:00:00-04:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '04:00:00' AND '04:59:59', LABAKOTOR-PPN,0)),0) AS `04:00:00-05:00:00`,
FORMAT(SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '05:00:00' AND '05:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '06:00:00' AND '06:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '07:00:00' AND '07:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '08:00:00' AND '08:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '09:00:00' AND '09:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '10:00:00' AND '10:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '11:00:00' AND '11:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '12:00:00' AND '12:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '13:00:00' AND '13:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '14:00:00' AND '14:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '15:00:00' AND '15:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '16:00:00' AND '16:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '17:00:00' AND '17:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '18:00:00' AND '18:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '19:00:00' AND '19:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '20:00:00' AND '20:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '21:00:00' AND '21:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '22:00:00' AND '22:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '23:00:00' AND '23:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '00:00:00' AND '00:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '01:00:00' AND '01:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '02:00:00' AND '02:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '03:00:00' AND '03:59:59', LABAKOTOR-PPN,0)) +
SUM(IF(JENIS='JUAL' AND WAKTU BETWEEN '04:00:00' AND '04:59:59', LABAKOTOR-PPN,0)),0) AS PENJUALAN_BERSIH
from DATATRANSAKSI WHERE LENGTH(STORE)=4 AND KD_BARANG NOT IN ('KDBRG20052297','KDBRG20052298','KDBRG20052299','KDBRG20052300','KDBRG20052301','KDBRG20052302') GROUP BY STORE,`DATE` ORDER BY STORE,`DATE`;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment