helminst_crosstab.esqiuel

DROP PROCEDURE IF EXISTS viewSalesByWeek;
-- call viewSalesByWeek(2014,9)
DELIMITER $$
CREATE PROCEDURE viewSalesByWeek
(
iYEAR INTEGER,
iMonth INTEGER
)
BEGIN

DROP TABLE IF EXISTS SalesProduct;
CREATE TABLE SalesProduct
(
SALE_IDXX VARCHAR(200),
SALE_DATE VARCHAR(10),
SALE_OUTL VARCHAR(200),
SALE_AMNT NUMERIC(15,2)
);

DROP TABLE IF EXISTS Outlet;
CREATE TABLE Outlet
(
OUTL_IDXX VARCHAR(200),
OUTL_NAME VARCHAR(200)
);

DROP TABLE IF EXISTS ListWeek;
CREATE TABLE ListWeek
(
WEEK_NUMB INTEGER,
MONT_NUMB INTEGER,
YEAR_NUMB INTEGER
);

Insert into Outlet values ('O-0001','Test Outlet 1');
Insert into Outlet values ('O-0002','Test Outlet 2');
Insert into Outlet values ('O-0003','Test Outlet 3');

Insert Into SalesProduct values ('F-0001','01/09/2014','O-0001',23000000);
Insert Into SalesProduct values ('F-0001','02/09/2014','O-0003',13500000);
Insert Into SalesProduct values ('F-0001','13/09/2014','O-0002',11254000);
Insert Into SalesProduct values ('F-0001','17/09/2014','O-0003',9500000);
Insert Into SalesProduct values ('F-0001','20/09/2014','O-0001',15244400);
Insert Into SalesProduct values ('F-0001','20/09/2014','O-0002',58700000);
Insert Into SalesProduct values ('F-0001','21/09/2014','O-0003',98635000);
Insert Into SalesProduct values ('F-0001','25/09/2014','O-0001',12347770);

INSERT INTO ListWeek
select DISTINCT MINGGU_KE,MONTH(TANGGAL),YEAR(TANGGAL) from
( select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) tanggal,
date_format(adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0),'%U') minggu_ke
from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) bla
where MONTH(TANGGAL)=iMonth AND YEAR(TANGGAL)=iYEAR ;

/* ############# WARNING #############
output GROUP_CONCAT() di sini gede buanget
sesuaikan variable settingan MySQL "group_concat_max_len" default hanya 1024 (1kB)
kalau masih elor silakan rubah jadi 10kB :
- di /etc/my.cnf : group_concat_max_len = 10K
- di konsol mysql: mysql> SET GLOBAL group_concat_max_len = 10240;
   ####################################
*/
-- QUERY
SET @Sql = null;
SET @Sql =
(SELECT CONCAT("SELECT B.SALE_OUTL,C.OUTL_NAME,","\n",
GROUP_CONCAT(DISTINCT CONCAT(
"CASE A.WEEK_NUMB \n \t WHEN ", A.WEEK_NUMB, " THEN ","\n \t\t",
" CASE WHEN ","\n \t\t\t\t",
" SUM(IF(DATE_FORMAT(STR_TO_DATE(B.SALE_DATE,'%d/%m/%Y'),'%U')=", A.WEEK_NUMB, ",SALE_AMNT,0)) IS NULL",
" \n \t\t THEN 0 ELSE ","\n \t\t\t\t",
" SUM(IF(DATE_FORMAT(STR_TO_DATE(B.SALE_DATE,'%d/%m/%Y'),'%U')=", A.WEEK_NUMB, ",SALE_AMNT,0)) END ",
" \n \t ELSE 0 \nEND AS 'PEKAN_KE_", A.WEEK_NUMB, "',", "\n")
ORDER BY A.WEEK_NUMB
separator ''
),
"SUM(B.SALE_AMNT) AS 'SALE_GRAN'",
"\nFROM ListWeek A
LEFT JOIN SalesProduct B
ON A.YEAR_NUMB = YEAR(STR_TO_DATE(B.SALE_DATE,'%d/%m/%Y'))
AND A.MONT_NUMB = MONTH(STR_TO_DATE(B.SALE_DATE,'%d/%m/%Y'))
INNER JOIN Outlet C
ON B.SALE_OUTL = C.OUTL_IDXX
GROUP BY B.SALE_OUTL,C.OUTL_NAME") STATEMENT
FROM ListWeek A
);

-- SELECT @SQL;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;