CODEWITHSUNDEEP
sql-serverstored-procedures
Vinicius Cano
Vinicius Cano

Reputation: 319

SQL SERVER Select MAX Procedure

I'm trying to make a procedure that changes the user login on a table,but change only the last entry if its in a specific status:Here is the procudure i'm trying:

Update T_STATUS_CLIC 
SET iEDV = @iEDV 
WHERE 
    iClic = @iClic and iStatus = '9' and dtDateCreated = 
    (select max (dtDateCreated)  FROM T_STATUS_CLIC where iclic = @iClic );

I can only change the iEDV if the last row of the table the max date,is in the iStatus 9,i dont know if this is possible,i'm trying something like this:

iStatus = (select iStatus = '9' where max(dtDateCreated))

But didn't work,is there a way to do this?

Upvotes: 0

Views: 109

Answers (1)

Stan
Stan

Reputation: 983

If i understand correctly, you can have many rows per "iClic" and only want to update the row if the most recent has a status = 9.

It would help if the table had a true PK.

But I believe this will work, given:

CREATE TABLE T_STATUS_CLIC (iClic INT , iEDV INT, iStatus CHAR(1), dtDateCreated DateTime)
DECLARE @iEDV int = 123;
DECLARE @iClic int = 8888;

this script: 

; -- WITH must be preceeded by a semicolon
WITH clicmax as (SELECT iClic
                ,   max(dtDateCreated) as maxdt 
                ,   max(case when mc.iStatus = '9' THEN mc.dtDateCreated ELSE null END) as Max9dt
                FROM dbo.T_STATUS_CLIC mc WHERE iClic = @iClic GROUP BY iClic)
UPDATE tt
SET iEDV = @iEDV
FROM dbo.T_STATUS_CLIC tt
    JOIN clicmax on clicmax.iClic = tt.iClic 
WHERE tt.iClic = @iClic
and tt.dtDateCreated = clicmax.Max9dt  
and tt.dtDateCreated = clicmax.Maxdt
and tt.iStatus = '9';

The WITH clause determines BOTH the actual max date and the max date for status 9. The update can then reference both of these in its WHERE clause.

Upvotes: 1

Related Questions