Incorrect next date from date column for all Oracle clients
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
I am executing my query to get start_date and immediate date of next line as end_date for one 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 out just fine as I want. as shown 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
But when I execute this query for the whole client (removing NM.CUSTOMER_ID = 96 from the query) it gives me the same START_DATE and END_DATE and end_date is added on the AS LIKE day below ... I also give you a snapshot of my query output and highlight result for a client with a red field ...
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;
Result:
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
Query Image Result
I want to get the result for the whole client as the result of one client. How can I solve my problem? Help me any
source to share
Your suggestion window is considering last_effective_dates
for all your data. You need to add an offer partition by
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)
So:
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;
If you ever need to run it for more than one appliance_info_id
, you need to add it to partition by clause
.
Using a dummy extra entry to mimic what you see is supplied via the 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
)
your original request gets:
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 request for the section 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
source to share