In SQL I have table t_test below:
emp_code | period_month | company_code NIK001 | 01 | ALPHA NIK001 | 02 | ALPHA NIK001 | 03 | ALPHA NIK001 | 04 | ALPHA NIK001 | 05 | ALPHA NIK001 | 06 | BETA NIK001 | 07 | BETA NIK001 | 08 | BETA NIK001 | 09 | BETA NIK001 | 10 | ALPHA NIK001 | 11 | ALPHA NIK001 | 12 | ALPHA
I want to query with result below:
emp_code | company_code | from_month | to_month -------------------------------------------------- NIK001 | ALPHA | 01 | 05 NIK001 | BETA | 06 | 09 NIK001 | ALPHA | 10 | 12
This makes me tremble in fear, but it does output an approximation of what you're after:
select w.emp_code, w.company_code, w.period_month from_month,
(select min(convert(u.period_month,unsigned))-1 from t_test u where u.emp_code=w.emp_Code and convert(u.period_month,signed)>convert(w.period_month,signed) and u.company_code<>w.company_code) to_month
from
(
select * from
(
select y.emp_code, y.period_month, y.company_code,
(select x.company_code from t_test x where x.emp_code=y.emp_code and convert(x.period_month,unsigned)<convert(y.period_month,unsigned) order by convert(x.period_month,unsigned) desc limit 1) previous_company_code
from t_test y
) z
where company_code<>previous_company_code or previous_company_code is null
) w
However, I tried this just out of curiosity. You shouldn't really rely on anything like that for your production environment. I don't think that a simple SQL query is a good fit for your problem. I would look elsewhere.
EDIT: Based on the following table:
CREATE TABLE `t_test` (
`emp_code` varchar(50) DEFAULT NULL,
`period_month` varchar(2) DEFAULT NULL,
`company_code` varchar(50) DEFAULT NULL
);
Filled with the following INSERT statements:
INSERT INTO `t_test` (`emp_code`,`period_month`,`company_code`)
VALUES
('NIK001', '01', 'ALPHA'),
('NIK001', '02', 'ALPHA'),
('NIK001', '03', 'ALPHA'),
('NIK001', '04', 'ALPHA'),
('NIK001', '05', 'ALPHA'),
('NIK001', '06', 'BETA'),
('NIK001', '07', 'BETA'),
('NIK001', '08', 'BETA'),
('NIK001', '09', 'BETA'),
('NIK001', '10', 'ALPHA'),
('NIK001', '11', 'ALPHA'),
('NIK001', '12', 'ALPHA');