SQL Server: A Business Problem

Here I am going to explain the solution of a business problem which I gave to one of my friends.

There is a SQL Server table like below:

table

Data in my table:

data

Now the problem is that he wants to fetch record columns for after the current month: This means if the current month is February then the result set columns should show the month record for after February (March- December) or if the current month is March then the result set should show (April- December).

So for this I wrote the below stored procedure:

code 

  1. CREATEPROCEDURE [dbo].[GETEMPLOYEE]  
  2. AS  
  3. DECLARE @CurrentMonth INT;  
  4. SET @CurrentMonth=(SELECTMONTH(GETDATE())AS CurrentMonth)-- Get Current Month  
  5.   
  6. CREATETABLE #TempTable(ID VARCHAR(2), Name char(20))  
  7. INSERTINTO #TempTable(ID, Name)VALUES ('1','JAN')  
  8. INSERTINTO #TempTable(ID, Name)VALUES ('2','FEB')  
  9. INSERTINTO #TempTable(ID, Name)VALUES ('3','MAR')  
  10. INSERTINTO #TempTable(ID, Name)VALUES ('4','APR')  
  11. INSERTINTO #TempTable(ID, Name)VALUES ('5','MAY')  
  12. INSERTINTO #TempTable(ID, Name)VALUES ('6','JUN')  
  13. INSERTINTO #TempTable(ID, Name)VALUES ('7','JUL')  
  14. INSERTINTO #TempTable(ID, Name)VALUES ('8','AUG')  
  15. INSERTINTO #TempTable(ID, Name)VALUES ('9','SEP')  
  16. INSERTINTO #TempTable(ID, Name)VALUES ('10','OCT')  
  17. INSERTINTO #TempTable(ID, Name)VALUES ('11','NOV')  
  18. INSERTINTO #TempTable(ID, Name)VALUES ('12','DEC')  
  19.   
  20. DECLARE @listStr VARCHAR(MAX)  
  21. SELECT @listStr =COALESCE(@listStr+',','')+ Name  
  22. FROM #TempTable WHERE ID> @CurrentMonth  
  23.   
  24. DECLARE @sqlCommand varchar(1000)  
  25. DECLARE @columnList varchar(75)  
  26. SET @sqlCommand ='SELECT EMP_ID, EMP_NAME, '+ @listStr +' FROM EMPLOYEE '  
  27. EXEC (@sqlCommand)  
  28.   
  29. DROPTABLE #TempTable  
  30. GO  
Now Execute This Stored Procedure:

Stored Procedure

Here you can see the result set is showing columns after the current month.
Ebook Download
View all
Learn
View all