Home » SQL & PL/SQL » SQL & PL/SQL » Missing records (Oracle SQL)
Missing records [message #689798] Wed, 24 April 2024 06:25 Go to next message
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 Go to previous messageGo to next message
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
Voyage Master:

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. Attachedis output, records marked with yello needs to be generatyed


  • Attachment: Output.PNG
    (Size: 15.42KB, Downloaded 508 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 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Missing records [message #689802 is a reply to message #689800] Wed, 24 April 2024 10:13 Go to previous message
ace_friends22
Messages: 9
Registered: December 2023
Junior Member
@Michel Cadot: Sure. I'll make sure to submit feedback.

Oracle version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Previous Topic: Condition based truncate partition
Next Topic: insert data from csv file
Goto Forum:
  


Current Time: Fri Jun 21 09:28:56 CDT 2024