Need help in refining this qury... 1

[royc75]

Hello,

I am using this query:
UPDATE COMMITTEES_REQUEST SET protocol_number =
(SELECT MAX(protocol_number) FROM COMMITTEES_REQUEST WHERE code_committee = 1)
WHERE record_number = 5 AND protocol_number IS NULL

And I would like to refine it like this:
protocol_number is a field which looks like this: 1/06, 2/06, 3/06.... where 06 is the current year.
I would like to take MAX(protocol_number) and do the update not on it but on it's next incremental, for example if MAX(protocol_number) returns 5/06 I would like the update to be 6/06.
More than that, I would like the query to check for the current year we are at, and if MAX(protocol_number) returns x/06 and we are in 2007 the update will set 1/07.

Is there any way to acheive all than in a single query?

 

[bborissov]

DECLARE @CurrentYear char(2)
DECLARE @MaxProt = varchar(4)

SET @CurrentYear = RIGHT(convert(varchar(4),YEAR(GETDATE())),2)

SELECT @MaxProt = MAX(protocol_number)
      FROM COMMITTEES_REQUEST WHERE code_committee = 1

UPDATE COMMITTEES_REQUEST
        SET protocol_number =
            CASE WHEN RIGTH(@MaxProt,2) < @CurrentYear THEN
                 '1/'+@CurrentYear
            CASE WHEN LEFT(@MaxProt,2) = '12' THEN
                 '1/'+RIGHT(convert(varchar(4),YEAR(GETDATE())+1),2)
            ELSE
               convert(varchar(2),cast(LEFT(@MaxProt,charindex('/',@MaxProt)-1) as int)+1)+'\'+ @CurrentYear
           END
WHERE record_number = 5 AND protocol_number IS NULL

not tested
(of course if you don't want to declare variables you can substitute them into this update)


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[royc75]

Hello Borislav,

I had to make some changes in your query in order to adapt it to my needs but in general this is what I was looking for, thank you very much for the solution!