Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Set the max value for a column

Posted on 2016-10-12
7
Medium Priority
?
80 Views
Last Modified: 2016-10-12
Hello,

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.

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

0
Comment
Question by:RIAS
  • 4
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41840180
That was answered in your previous question.
0
 

Author Comment

by:RIAS
ID: 41840184
Can I use the same code as the query is a bit different
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41840208
I guess it will work but give it a try and check.
Here you're using TOP 1 and ORDER BY and in the previous one you used MAX and GROUP BY. The latest is the better option.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:RIAS
ID: 41840214
ok
0
 

Author Comment

by:RIAS
ID: 41840270
Is this ok?

  DECLARE @SQL VARCHAR(1000) = null


SET @SQL = '
	
		SELECT 
		
				CASE 
						WHEN CAST(GETDATE() AS DATE) = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) THEN 1 
				ELSE 
				
						TOP 1 ([' +  @ColumnName1 + ']'  + '  ) END Output'  
				
				+ ' FROM  [dbo].[' + @tableName + ']'  
			  
EXECUTE(@SQL)

Open in new window

0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41840279
Just use the solution from the previous question. Like I told you above, MAX is better option than TOP 1 and it will give you the same result but faster.
1
 

Author Comment

by:RIAS
ID: 41840287
Cool!
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question