asked on

Start a field with integer 1

Hello,

How do I write a query to return value 1 when it is the beginning of the year as a Reference number.
The requirement is the Reference number should start with 1 at the beginning of the year.

Cheers

Vitor Montalvão

Without you giving more information I can only provide the following query:

SELECT CASE
	WHEN CAST(GETDATE() AS DATE) = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN 1
	ELSE 999
END

Open in new window

NOTE: Replace 999 with the value or formula that you want when the date is not the beginning of the year.

RIAS

ASKER

Thanks Vitor,
My earlier query was to find max value

	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]

	SET @SQL = 'SELECT MAX ('    
        
			SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'  + ' +1 ) ' +   + ' FROM  [dbo].[' + @tableName + '] '  		
	
	  SET @SQL = @SQL  	
	  
	  EXEC(@SQL)

Open in new window

But, now the need is give value refno 1 if it a new year and refno 1 is not found for new year

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

But, now the need is give value refno 1 if it a new year and refno 1 is not found for new year

Can you explain your requirement in detail ?

RIAS

ASKER

Vitor I am trying your solution.

Pawan,
To explain:
The query needs to return refno:
The refno in table should begin with 1 in a table every year.
If refno 1 is present then give the next value.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RIAS

ASKER

Any suggestion for this one

ALTER PROCEDURE  [dbo].[L_SelectLastVal_FrmCol]
(
     @tableName varchar(100) = null,
	 @ColumnName1 varchar(100) = null
	
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;  	
	
	   declare @SQL varchar(500) = null
	  -- NONCLUSTERED INDEX [NIX__UNQ__UID_]



	SET @SQL = 'SELECT TOP 1 '    
        
			SET @SQL = @SQL +  '[' +  @ColumnName1 + ']'     + ' FROM  [dbo].[' + @tableName + ']  ORDER BY '  	+  '[' +  @ColumnName1 + ']'     +'DESC'	
	
	  SET @SQL = @SQL  	
	  
	  EXEC(@SQL)



		
   
	
END

Open in new window

And

ALTER PROCEDURE  [dbo].[MEDICAL_FindMaxSPRSpPayReq]
AS
BEGIN

        SELECT MAX(SPR#)+1
               FROM SIAL_PAT_REQT
		
				
END

Open in new window