CODEWITHSUNDEEP
sqloracle-database
user3802500
user3802500

Reputation: 23

Adding Missing Records

I have a situation i been working on it since yesterday. but couldnt fix it

          Table 1
LN_ID     DATE         CODE
500      01-JUN-2014   00
500      01-MAY-2014   01
500      01-APR-2014   02
500      01-MAR-2014   00
500      01-FEB-2014   04
500      01-JAN-2014   03

501      01-JUN-2014   00
501      01-MAY-2014   01
501      01-APR-2014   02
501      01-MAR-2014   00

502      01-JUN-2014   00
502      01-MAY-2014   01
502      01-APR-2014   02
502      01-MAR-2014   00
502      01-FEB-2014   04

this is all one table with each loan of mulitple records.

Loan 500 have 6 records from Jun to Jan and

Loan 501 have only 4 records from Jun to MAR and

Loan 502 have only 5 records from Jun to FEB

I want to insert missing records JUN to JAN for all loan. and want to make code as XX i want to see this table like this one;

          Table 1 (needed)
LN_ID     DATE         CODE
500      01-JUN-2014   00
500      01-MAY-2014   01
500      01-APR-2014   02
500      01-MAR-2014   00
500      01-FEB-2014   04
500      01-JAN-2014   03

501      01-JUN-2014   00
501      01-MAY-2014   01
501      01-APR-2014   02
501      01-MAR-2014   00
501      01-FEB-2014   XX
501      01-JAN-2014   XX

502      01-JUN-2014   00
502      01-MAY-2014   01
502      01-APR-2014   02
502      01-MAR-2014   00
502      01-FEB-2014   04
502      01-JAN-2014   XX

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can generate the records by doing a cross join. Then you can use left join back to the original data to get the codes.

For the data in your question:

select l.ln_id, d.date, coalesce(t1.code, 'XX') as code
from (select distinct ln_id from table1) l cross join
     (select distinct date from table1) d left outer join
     table1 t1
     on t1.ln_id = l.ln_id and t1.date = d.date;

Note that your query is tagged in a way that suggests both MySQL and Oracle. The above is standard SQL and will work on both those databases.

Upvotes: 1

Related Questions