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.
Below is code block:
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
Voyage Master Data:
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 voyage master we can see ship started on 4/4/2024 and returned on 4/15/2024.
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. Below is output, records marked with yello needs to be generatyed
asd