How to insert record from Table A to Table B based on the conditions in Table C.
Table A:
ID Name DateFrom DateTo
1 Alex 22-7-2015 26-7-2015
2 Alice 21-8-2015 25-8-2015
Table C:
ID Quarter DateFrom DateTo
1 Quater 1 1-7-2015 31-7-2015
2 Quater 2 1-8-2015 31-8-2015
If the records from Table A is between the date range in Table C. It will insert into a new Table B.
Redemption is Table B
insert into redemption(staffID,staffName,department,pointsAccumulated,referrerID)
select referrerStaffID,referrerName,referrerDepartment,SUM(points),activeDirectoryID
FROM referral
WHERE NOT EXISTS (select * from redemption1 where referrerID=activeDirectoryID) group by activeDirectoryID;
Try this
Insert into tableB(Id,name,datefrom,dateto)
select t1.Id,t1.name,t1.datefrom,t1.dateto from tableA as t1
inner join tableC as t2 on t1.id=t2.id
where
t1.datefrom between t2.datefrom and t2.dateto or
t1.dateto between t2.datefrom and t2.dateto
You can use the below query
insert into tableB(id,name,datefrom,dateto)
select A.id,A.name,A.datefrom,A.dateto from tableA A join tableC C
on A.id=C.id
where TO_DATE(A.datefrom,'DD-mm-yyyy')
between TO_DATE(C.datefrom,'DD-mm-yyyy') and TO_DATE(c.dateto,'DD-mm-yyyy')
and TO_DATE(A.dateto,'DD-mm-yyyy')
between TO_DATE(C.datefrom,'DD-mm-yyyy') and TO_DATE(c.dateto,'DD-mm-yyyy')
Please check the answer here SQL Fiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With