Home » SQL & PL/SQL » SQL & PL/SQL » Missing records (Oracle SQL)
| Missing records [message #689798] |
Wed, 24 April 2024 06:25  |
 |
ace_friends22
Messages: 9
Registered: December 2023
|
Junior Member |
|
|
Hi All,
I'm working for a shipment company. We get voyage information. Below is requirement and I'm not able to find right solution for this.
- We get Voyage detail on daily basis and we load it into a target table
- There is a table called voyage master, which shows when voyage is discontinued.
- Even if Voyage is discontinued, we need to create dummy record and populate it in the separate table
- Later, we do UNION in the view to combine both tables to show details to users
- There is also challenges that sometimes, voyages gets discontinued and later re-start. In that scenario, we need to make sure process creates dummy records for days, it was discontinued and later start populating main target table when it re-start
The solution should be ro-bust to take care these thing.
Can someone help me with what logic I should put. Googled one is not solving both problems and Lead is expecting dummy record process to robust.
Please note, voyage start date could be different.
I hope, i'm clear with my doubt.
[Updated on: Wed, 24 April 2024 08:21] by Moderator Report message to a moderator |
|
|
|
| Missing records [message #689799 is a reply to message #689798] |
Wed, 24 April 2024 06:26  |
|
ace_friends22
Messages: 9
Registered: December 2023
|
Junior Member |
|
|
Hi All,
I'm working for a shipment company. We get voyage information. Below is requirement and I'm not able to find right solution for this.
- We get Voyage detail on daily basis and we load it into a target table
- There is a table called voyage master, which shows when voyage is discontinued.
- Even if Voyage is discontinued, we need to create dummy record and populate it in the separate table
- Later, we do UNION in the view to combine both tables to show details to users
- There is also challenges that sometimes, voyages gets discontinued and later re-start. In that scenario, we need to make sure process creates dummy records for days, it was discontinued and later start populating main target table when it re-start
The solution should be ro-bust to take care these thing.
Can someone help me with what logic I should put. Googled one is not solving both problems and Lead is expecting dummy record process to robust.
Please note, voyage start date could be different.
I hope, i'm clear with my doubt.
Source Data:
WITH SRC AS
(
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/05/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/04/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/06/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/05/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/07/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/06/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/08/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/07/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
--SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/09/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/08/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
--UNION
--SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/10/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/09/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
--UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/11/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/10/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/12/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/11/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/13/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/12/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/14/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/13/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/15/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/14/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
UNION
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/16/2024', 'MM/DD/YYYY') FILE_DATE, TO_DATE('04/15/2024', 'MM/DD/YYYY') DATA_DATE, '04/04/2024' SAIL_DATE
)
select * from src order by data_date
WITH VOYAGE_MSTR AS
(
SELECT '1234' VOYAGE, '2024' CAL_YEAR, TO_DATE('04/04/2024', 'MM/DD/YYYY') SAIL_DATE, TO_DATE('04/15/2024', 'MM/DD/YYYY') RETURN_DATE
)
In the existing table (SRC) we can see, daily VOYAGE information is getting inserted, but somehow we missed entry on 8th and 9th.
Now we need to generate dummy records for 9th & 10th as well as from 4/16/2024 till date. Attachedis output, records marked with yello needs to be generatyed
-
Attachment: Output.PNG
(Size: 15.42KB, Downloaded 1100 times)
[Updated on: Wed, 24 April 2024 08:16] Report message to a moderator |
|
|
|
| Re: Missing records [message #689800 is a reply to message #689798] |
Wed, 24 April 2024 08:31 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
Michel Cadot wrote on Wed, 27 December 2023 13:28
If you want to continue to get help feedback in your topics.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
[Updated on: Wed, 24 April 2024 08:32] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 03:51:15 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
