how to create an agent job depending on time

Question

I am creating an agent job, using SQL server. In my database there are 2 tables. The columns in the first table are: Idproduct, number The columns in the second tables are: Idproduct, start (datatime), duration (time)

I need to increment the field number of a product when its (start+duration)<= getdate() and then i need delete this record. How can i do?

Create table product(
    Idproduct int primary key,
    Number int default 0)
Create table production(
    Idproduct int primary key,
    Start datetime not null,
    Times time not null)

Answer 1

One method is with the OUTPUT clause of a DELETE statement to insert the produced products into a table variable. Then use the table variable to increment the count. The example below uses SQL 2012 and above features but you retrofit the error handling for earlier versions if needed.

SET XACT_ABORT ON;

DECLARE @ProducedProducts TABLE(
    Idproduct int
    );

BEGIN TRY

    BEGIN TRAN;

    --delete produced products
    DELETE FROM dbo.production
    OUTPUT deleted.Idproduct INTO @ProducedProducts
    WHERE
        DATEADD(millisecond, DATEDIFF(millisecond, '', Times), Start) <= GETDATE();

    --increment produced products count
    UPDATE dbo.product
    SET Number += 1
    WHERE Idproduct IN(
        SELECT pp.Idproduct
        FROM @ProducedProducts AS pp
        );

    COMMIT;

END TRY
BEGIN CATCH

    THROW;

END CATCH;