CODEWITHSUNDEEP
sqldatabaseoracle-database
Rajiv A
Rajiv A

Reputation: 943

Generate extra records in Select statement Oracle

I want to generate records during my Select statement for each unique F_ID for period of 12/31/2012 to "STARTDT of the first record for that F_ID" in ascending order with a status of FB

( i.e. for 15960 I want a record with

ID = 15960 ; Status = 'FB'; Startdt = 12/31/2012; ENDDT a= 11/14/2013 )

Note : If there is already a record prior to 12/31/2012 then no need to generate for that F_ID

(for example : 16022 has a record from 1/1/2010 so no record is needed for this F_ID)

F_ID     STATUS     STARTDT     ENDDT
-----  ---------  ---------  ---------
15960   NF      11/14/2013  3/3/2014
15960   FB      3/3/2014    12/31/9999
16022   DF      1/1/2010    3/5/2013
16022   FB      3/5/2013    12/31/9999

I remember seeing Model clause using UPSERT , Is there any other way without using complex Model clause, help is really appreciated.If not then how do I go about using Model clause

Upvotes: 1

Views: 93

Answers (1)

bprasanna
bprasanna

Reputation: 2453

Not sure if you need the answer in plsql.

Based on the following conditions:

  1. Select records whose min startdt is greater than 12/31/2012
  2. Out of which select records which contains 'FB' as status

Following is the sql:

select f_id, 'FB' status, '31-Dec-2012' startdt, min(startdt) enddt from ftest
where f_id in
(select f_id from ftest where f_id in(select oq.f_id from ftest oq where
oq.startdt =(
select min(iq.startdt) from ftest iq 
where iq.startdt > to_date('31-DEC-2012','DD-MON-YY')
and iq.f_id=oq.f_id
and iq.startdt = (select min(startdt) from ftest where f_id=iq.f_id)
)) and status='FB') GROUP BY F_ID;

Also, check this sqlfiddle

Upvotes: 1

Related Questions