creating a stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

creating a stored procedure

Author

Topic

msugradus
Starting Member

40 Posts

Posted - 2007-06-11 : 10:20:03

I need to add up the number of people who joined this month and compare that number to the number of people who joined last month and display the results in a report and I have never done a stored procedure and I was wondering if it would be ease to do?

FirstName..LastName.....StartedDate
Randy......Simpson......5/4/2007 10:00:00 PM
Steve......Rowe.........5/2/2007 10:00:00 PM
Eric.......Dickerson....5/4/2007 10:00:00 PM
Gloria.....Sanches......5/1/2007 12:00:29 AM
Andres.....Marcelino....5/1/2007 12:06:31 AM
katie......ryan.........6/4/2007 12:08:35 AM
Denise.....River........6/4/2007 12:27:14 AM
Kellog.....Stover.......6/5/2007 12:37:20 AM
Glenn......Sanders......6/1/2007 12:42:40 AM

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-11 : 10:22:08

How should the output look like?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

msugradus
Starting Member

40 Posts

Posted - 2007-06-11 : 10:24:15

I just need a simple report displaying the new reps as compared to last month

subrata4allfriends
Starting Member

24 Posts

Posted - 2007-06-11 : 16:46:49

FirstName, LastName, StartedDate are the column of table EMPTABLE.

Step 01: Replace the table name, column name according to your table structure.

Step 02: Execute the following stored procedure.

CREATE PROCEDURE RPT_JOINNING_DETAILS
AS
BEGIN

DECLARE @ROWCOUNT AS INT
DECLARE @EMPNAME AS VARCHAR(50)
DECLARE @ID AS INT

CREATE TABLE #JOINTHISMONTHS(ID INT IDENTITY(1,1) NOT NULL, EMPNAME VARCHAR(50))
CREATE TABLE #JOINLASTMONTHS(ID INT IDENTITY(1,1) NOT NULL, EMPNAME VARCHAR(50))

INSERT INTO #JOINTHISMONTHS
SELECT FirstName +' '+ LastName FROM EMPTABLE WHERE CAST(MONTH(StartedDate) AS VARCHAR(10)) = CAST(MONTH(GETDATE()) AS VARCHAR(10))

INSERT INTO #JOINLASTMONTHS
SELECT FirstName +' '+ LastName FROM EMPTABLE WHERE CAST(MONTH(StartedDate) AS VARCHAR(10)) = CAST((MONTH(GETDATE())-1) AS VARCHAR(10))

SELECT @ROWCOUNT = MAX(ID) FROM #JOINTHISMONTHS
PRINT '------------------------------------------------------------------------------------------'
PRINT '------------------------COMPARISION REPORT FOR LAST TWO MONTH-----------------------------'
PRINT '------------------------------------------------------------------------------------------'

PRINT 'TOTAL NO OF EMPLOYEES JOINED THIS MONTHS ('+ CAST(MONTH(GETDATE()) AS VARCHAR(10))+'-'+ RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(10)),2) +') :' + CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @EMPNAME = EMPNAME, @ID = ID FROM #JOINTHISMONTHS WHERE ID = @ROWCOUNT
PRINT ' * '+@EMPNAME
SET @ROWCOUNT = @ROWCOUNT - 1
END

SELECT @ROWCOUNT = MAX(ID) FROM #JOINLASTMONTHS

PRINT 'TOTAL NO OF EMPLOYEES JOINED LAST MONTHS ('+ CAST(MONTH((GETDATE())-1) AS VARCHAR(10))+'-'+ RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(10)),2) +') :' + CAST(@ROWCOUNT AS VARCHAR(50))

WHILE @ROWCOUNT > 0
BEGIN
SELECT @EMPNAME = EMPNAME, @ID = ID FROM #JOINLASTMONTHS WHERE ID = @ROWCOUNT
PRINT ' * '+@EMPNAME
SET @ROWCOUNT = @ROWCOUNT - 1
END

DROP TABLE #JOINTHISMONTHS
DROP TABLE #JOINLASTMONTHS

END

Step 03: EXEC RPT_JOINNING_DETAILS

Hope, you will get the report.....

"Life is not a bed of roses."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources