Solved

Set the max value for a column

Posted on 2016-10-12
7
42 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 50

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 50

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 50

Accepted Solution

by:
Vitor Montalvão earned 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

739 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