Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

SQL SERVER A Business Problem

Article:
Viewed:  681 
Posted On:  05/03/2016 07:46:01 
Here I am going to explain a SQL Server business problem which solution I gave to one of my friend. 

 

Here I am going to explain a solution of a SQL Server business problem which solution I gave to one of my friend.

There is a SQL Server table like below:

1.png

Image 1.

Data in my table:

2.png

Image 2.

Now the problem is that he wants to fetch records columns after current month: Mean if current month is February then result set columns should show After February month record (March- December) or if Current month is march then  result set should show (April- December).

So for this I wrote below Stored Procedure:

3.png

Image 3.

CREATE PROCEDURE [dbo].[GETEMPLOYEE]
AS
DECLARE @CurrentMonth INT;
SET @CurrentMonth= (SELECT  MONTH(GETDATE()) AS CurrentMonth)  -- Get Current Month
 
CREATE TABLE #TempTable(ID VARCHAR(2), Name char(20))
INSERT INTO #TempTable (ID, Name) VALUES ('1','JAN')
INSERT INTO #TempTable (ID, Name) VALUES ('2','FEB')
INSERT INTO #TempTable (ID, Name) VALUES ('3','MAR')
INSERT INTO #TempTable (ID, Name) VALUES ('4','APR')
INSERT INTO #TempTable (ID, Name) VALUES ('5','MAY')
INSERT INTO #TempTable (ID, Name) VALUES ('6','JUN')
INSERT INTO #TempTable (ID, Name) VALUES ('7','JUL')
INSERT INTO #TempTable (ID, Name) VALUES ('8','AUG')
INSERT INTO #TempTable (ID, Name) VALUES ('9','SEP')
INSERT INTO #TempTable (ID, Name) VALUES ('10','OCT')
INSERT INTO #TempTable (ID, Name) VALUES ('11','NOV')
INSERT INTO #TempTable (ID, Name) VALUES ('12','DEC')
 

DECLARE @listStr VARCHAR(MAX)

SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM #TempTable WHERE ID> @CurrentMonth 

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
SET @sqlCommand = 'SELECT EMP_ID, EMP_NAME, ' + @listStr + ' FROM EMPLOYEE '

EXEC
(@sqlCommand)
 

DROP TABLE #TempTable

GO


Now Execute This Stored Procedure:

4.png

Image 4.

Here you can see result set is showing columns after current month.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us