WITH date_info
AS (SELECT TO_DATE ('2017-03-07', 'yyyy-mm-dd') AS start_date,
TO_DATE ('2017-03-14', 'yyyy-mm-dd')
- TO_DATE ('2017-03-07', 'yyyy-mm-dd')
+ 1
AS dt
FROM DUAL),
date_group
AS ( SELECT start_date + (LEVEL - 1) AS start_date,
start_date + LEVEL AS end_date
FROM date_info
CONNECT BY LEVEL <= date_info.dt),
data_info
AS (SELECT *
FROM (SELECT t3.factory,
t3.client,
t3.real_value,
NVL (t4.target_value, 0) target_value,
t3.create_time
FROM ( SELECT t.factory,
t.client,
SUM (t.real_value) real_value,
t.create_time
FROM (SELECT factory,
client,
ROUND (
DECODE (person,
0, 0,
product_qty / person),
2)
real_value,
reamark,
create_time
FROM D9DATA_TABLE_DAY_UPPD) t
LEFT JOIN D9DATA_TABLE_MONTH_TARGET t2
ON t.factory = t2.factory
AND t.client = t2.client
AND TO_CHAR (t.create_time, 'yyyy-mm') =
TO_CHAR (
TO_DATE (
t2.year_info
|| '-'
|| t2.month_info,
'yyyy-mm'),
'yyyy-mm')
GROUP BY t.factory,
t.client,
real_value,
t.create_time
ORDER BY t.factory, t.client, t.create_time) t3
LEFT JOIN D9DATA_TABLE_MONTH_TARGET t4
ON t4.factory = t3.factory
AND t4.client = t3.client
AND TO_CHAR (
TO_DATE (
t4.year_info || '-' || t4.month_info,
'yyyy-mm'),
'yyyy-mm') =
TO_CHAR (t3.create_time, 'yyyy-mm')) t5)
SELECT t5.start_date,
DECODE (t5.factory,
1, '比亚迪第九事业部',
6, '第六工厂',
3, '第一工厂',
5, '第五工厂',
4, '第二工厂',
49, '第七工厂',
48, '第三工厂')
factory,
t5.client,
NVL (t5.target_value, 0),
NVL (t5.target_value1, 0),
NVL (t5.real_value, 0),
NVL (t5.balance, 0),
t5.rate
FROM (SELECT t.start_date,
NVL (t1.factory, t.factory) factory,
NVL (t1.client, t.client) client,
t1.target_value,
t1.target_value target_value1,
t1.real_value,
t1.real_value - t1.target_value balance,
DECODE (
NVL (t2.real_value, 0),
0, 0,
TO_CHAR (
ROUND (
(NVL (t1.real_value, 0) - NVL (t2.real_value, 0))
/ NVL (t2.real_value, 0)
* 100,
2),
'fm99999999990.00'))
rate
FROM (SELECT t3.start_date, t4.factory, t4.client
FROM date_group t3,
(SELECT DISTINCT factory, client FROM data_info) t4) t
LEFT JOIN data_info t1
ON t.start_date = t1.create_time
AND t.factory = t1.factory
AND t.client = t1.client
LEFT JOIN data_info t2
ON t.start_date - 1 = t2.create_time
AND t1.factory = t2.factory
AND t1.client = t2.client) t5
WHERE 1 = 1
ORDER BY t5.factory, t5.client, t5.start_date