Calculate a column from a table depending on different column from another tablecomputed column based on different

Question

I have tables:

create table Aprovizionari
(
  ID_Aprovizionare  int     identity(1,1) primary key, 
  CodCarte          char(3) foreign key references Carti(CodCarte),
  CodLibrarie       char(3) foreign key references Librarii(CodLibrarie),
  DataAprovizionare date    default getdate(), 
  Cantitate         int     default 1 
)
create table Returnari
(
 CodRet char(3) primary key,
 CodCarte char(3) foreign key references Carti(CodCarte),
 CodLibrarie char(3) foreign key references Librarii(CodLibrarie),
 CodEditura char(4) foreign key references Edituri(CodEditura),
 DataRet date,
 Cantitate int
)

I have a trigger that decrement the column Cantitate(Quantity) from Aprovizionari(Supply) while I add in Cantitate from another table Facturi(Invoices).

In Returnari(Returns -of books) I should have:

DataRet date,--this should be =DataAprovizionare+3 mounths
Cantitate int--this should be=the remaining Cantitate(Quantity) from Aprovizionari at date=DataAprovizionare+3 mounts

Is the idea that a `Returnari` record is automatically created 3 months after the `Aprovizionari` record? — OGHaza, Feb 07 '14 at 11:58

score 0 · Accepted Answer · edited May 23 '17 at 12:28

To do this you're going to need to check every day for Aprovizionari records that are 3 months old. When you find one you'll INSERT the Returnari record.

To do this you've got to create a stored procedure that when executed will create the Returnari records and then schedule a SQL Server Agent job to execute the procedure every day.

The stored procedure will look something like:

CREATE PROCEDURE YourDailyProc ( @Date DATE )
AS
    INSERT INTO Returnari (CodCarte, CodLibrarie, DataRet, Cantitate)
    SELECT CodCarte, CobLibrarie, CAST(@Date AS DATE), Cantitate
    FROM Aprovizionari 
    WHERE DataAprovizionare = CAST(DATEADD(MONTH, -3, @Date) AS DATE)
GO

It's not clear where you'd get CodEditura but presumably you just need to join to another table.

Now you need a job to execute EXEC YourDailyProc @Date=CURRENT_TIMESTAMP each day. This answer looks like it has all the information you'll need to do that - how to schedule a job for sql query to run daily?

Calculate a column from a table depending on different column from another tablecomputed column based on different

1 Answers1