从日期列中为所有客户Oracle获取错误的下一个日期 [英] Getting wrong next date from a date column for all customer Oracle
问题描述
这是我的NM_CUST_APPLIANCE_HISTORY表(对于custoner_id = 96).
This is my NM_CUST_APPLIANCE_HISTORY table ( for custoner_id=96 ) .
Customer_id | Last_effective_date | Present_quentity
--------------+---------------------+-----------------
96 | 2009-12-20 | 10
96 | 2014-11-18 | 12
96 | 2015-11-26 | 14
我执行查询以获取单个客户(customer_id = 96)的下一行的开始日期和开始日期作为结束日期.
I execute my query to get start_date and immediate date of next row as a end_date for a single customer ( customer_id=96 ) .
SELECT NM.CUSTOMER_ID customer_id,
NM.LATEST_EFFECTIVE_DATE start_date,
NVL (
CASE
WHEN nm.LATEST_EFFECTIVE_DATE IS NULL
THEN
TO_DATE ('12/12/9999', 'dd/mm/yyyy')
ELSE
FIRST_VALUE (
nm.LATEST_EFFECTIVE_DATE)
OVER (ORDER BY nm.LATEST_EFFECTIVE_DATE
RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
END,
TO_DATE ('12/12/9999', 'dd/mm/yyyy'))
end_date,
NM.PRESENT_QUANTITY PRESENT_quantity
FROM nm_cust_appliance_history nm
WHERE NM.APPLIANCE_INFO_ID = 10484
AND NM.CUSTOMER_ID = 96
ORDER BY customer_id, start_date;
结果就如我所愿.如下所示:
And the result comes perfectly AS I WANT. like below :
Customer_id | START_DATE | END_DATE | PRESENT_QUANTITY
------------+------------+------------+-----------------
96 | 2009-12-20 | 2014-11-18 | 10
96 | 2014-11-18 | 2015-11-26 | 12
96 | 2015-11-26 | 9999-12-12 | 14
但是,当我为所有客户执行此查询(从查询中删除NM.CUSTOMER_ID = 96)时,它给了我相同的START_DATE和END_DATE,并且end_date像下面这样添加了一天,我也给了我一个快照查询结果并用红色框标记出该客户结果...
But when i execute this query for all customer ( removing NM.CUSTOMER_ID = 96 from query ) it gives me same START_DATE and END_DATE and end_date comes added a day AS LIKE below ... I i also give you a snapshot of my output of query and marked out that customer result with red color box...
SELECT NM.CUSTOMER_ID customer_id,
NM.LATEST_EFFECTIVE_DATE start_date,
NVL (
CASE
WHEN nm.LATEST_EFFECTIVE_DATE IS NULL
THEN
TO_DATE ('12/12/9999', 'dd/mm/yyyy')
ELSE
FIRST_VALUE (
nm.LATEST_EFFECTIVE_DATE)
OVER (ORDER BY nm.LATEST_EFFECTIVE_DATE
RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
END,
TO_DATE ('12/12/9999', 'dd/mm/yyyy'))
end_date,
NM.PRESENT_QUANTITY PRESENT_quantity
FROM nm_cust_appliance_history nm
WHERE NM.APPLIANCE_INFO_ID = 10484
--AND NM.CUSTOMER_ID = 96
ORDER BY customer_id, start_date;
结果是:
Customer_id | START_DATE | END_DATE | Present_quentity
--------------+-------------+------------+-----------------
74 | 2008-10-26 | 2008-10-27 | 5
> 96 | 2009-12-20 | 2009-12-21 | 10
> 96 | 2014-11-18 | 2014-11-19 | 12
> 96 | 2015-11-26 | 2015-11-27 | 14
100 | 2009-01-07 | 2009-01-09 | 7
查询结果的图像
我希望所有客户的结果都像单个客户的结果一样. 我该如何解决我的问题? 帮我一个人
I want the result for all customer like the result of single customer. How can i solve my problem? Help me any one
推荐答案
您的window子句正在查看所有数据中的last_effective_dates
.您需要添加partition by
子句以将其限制为当前客户:
Your window clause is looking at last_effective_dates
across all your data. You need to add a partition by
clause to restrict it to the current customer:
OVER (PARTITION BY nm.CUSTOMER_ID
ORDER BY nm.LATEST_EFFECTIVE_DATE
RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
所以:
SELECT NM.CUSTOMER_ID customer_id,
NM.LATEST_EFFECTIVE_DATE start_date,
NVL (
CASE
WHEN nm.LATEST_EFFECTIVE_DATE IS NULL
THEN
TO_DATE ('12/12/9999', 'dd/mm/yyyy')
ELSE
FIRST_VALUE (
nm.LATEST_EFFECTIVE_DATE)
OVER (PARTITION BY nm.CUSTOMER_ID
ORDER BY nm.LATEST_EFFECTIVE_DATE
RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
END,
TO_DATE ('12/12/9999', 'dd/mm/yyyy'))
end_date,
NM.PRESENT_QUANTITY PRESENT_quantity
FROM nm_cust_appliance_history nm
WHERE NM.APPLIANCE_INFO_ID = 10484
ORDER BY customer_id, start_date;
如果您需要运行多个appliance_info_id
,则还需要将其添加到partition by clause
.
If you ever need to run it for more than one appliance_info_id
then you'll need to add that to the partition by clause
too.
使用虚拟的额外记录来模拟通过CTE提供的内容:
Using a dummy extra record to kind of simulate what you're seeing, supplied via a CTE:
with nm_cust_appliance_history(appliance_info_id, customer_id, latest_effective_date, present_quantity) as (
select 10484, 96, date '2009-12-20', 10 from dual
union all select 10484, 96, date '2014-11-18', 12 from dual
union all select 10484, 96, date '2015-11-26', 14 from dual
union all select 10484, 42, date '2009-12-21', 15 from dual
)
您的原始查询会得到:
CUSTOMER_ID START_DATE END_DATE PRESENT_QUANTITY
----------- ---------- ---------- ----------------
42 2009-12-21 2014-11-18 15
96 2009-12-20 2009-12-21 10
96 2014-11-18 2015-11-26 12
96 2015-11-26 9999-12-12 14
,上面的分区查询得到:
and the partition-by query above gets:
CUSTOMER_ID START_DATE END_DATE PRESENT_QUANTITY
----------- ---------- ---------- ----------------
42 2009-12-21 9999-12-12 15
96 2009-12-20 2014-11-18 10
96 2014-11-18 2015-11-26 12
96 2015-11-26 9999-12-12 14
这篇关于从日期列中为所有客户Oracle获取错误的下一个日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!