Well, here is the problem in detail SINCE I GOT SO MANY
RESPONSE (overwhelmed by all your help
)
I have a live TABLE A
I created a TABLE B with same #, datatype of columns as TABLE A . TABLE
B filters the data from TABLE A by looking at column Exp.Date = 2 months from
now, with other where conditions.
In addition, TABLE B has 10 more columns.
I inserted same data into TABLE B as TABLE A and they all columns from TABLE A
as original TABLE A column name
Example:
Insert into TABLE B
select
TABLEA alias. Column1 as Column1
TABLEA alias. Column2 as Column 2
And so on…
(Since TABLEB has 10
additional columns, I joined TABLE A with 2 other tables . i.e.
append to the above select INSERT INTO TABLE B
SELECT
10 new columns as 10 names)
TABLE1.columnA as xyz,
TABLE2.columnB as abc….etc (10 instances)
FROM
TABLE A
left outer join TABLE1
left outer join TABLE 2
WHERE
conditions
-----------------------------------------------------------------------------------------
Now,
A stored Procedure (1st Notice) needs to run
every Monday.
Let’s say I created a
batch job starting June 2nd, it needs to show data(satisfying the
where conditions) from (Aug 2nd to Aug 8th) i.e. 2 months
from the date the report is run.
Then, a column(Status) in TABLE B needs to be updated to value = ‘1stNotice’ (originally, all Status
= NULL)
Simultaneously, all these new records generated from TableB
by running the stored procedure need to be inserted(appended) to Table A(the
live table).
Also, in the live table, the column(STATUS) needs to be
updated to value = ‘1st notice’(originally, all Status = NULL)
THIS PROCESS FOR FIRST NOTICE GOES ON FOR A MONTH.
Then 30 days later, same cycle needs to be repeated to
update TABLE B Status = ‘2nd Notice’ where Status = ‘1st Notice’
Now, STATUS in TableA also needs to be updated to ‘2nd
Notice’ (we do not insert- insertion only happens once to change the Status
from NULL to 1st Notice)….
THIS PROCESS FOR 2nd NOTICE YET AGAIN GOES ON FOR
A MONTH…..finally, same procedures for final notice
PROBLEMS:
- How do
I specify Date between (1st day of 30days from now to 7th
day of 30days from now)?
- UPDATE
– I tried using inner joins and the suggestions provided to me. However it
does not seem to work.