SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to get output 'Month' and 'Year'
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abdbari
Starting Member

Malaysia
7 Posts

Posted - 03/27/2013 :  03:24:55  Show Profile  Reply with Quote
ID StaffNo Month Year LeaveStatus LeaveTaken
---- ------ ----- ---- ----------- ----------
00001 MC 1
00002 MC 2
00003 MC 3
00004 MC 4

e.g Month ->1 Year ->2013

Precedure as below:

CREATE PROCEDURE [dbo].[LEAVE_TAKEN]
( @Organisation_Code GLCOMPANY,
@Location_Code GLLOCN,
@Year SMALLINT,
@Month SMALLINT
)

AS
BEGIN


CREATE TABLE #Leave_Taken_Summ
( Id Char(4) Null,
StaffNo EMPNUM ,
Department FUNCDESC ,
Month varchar(35) ,
Year varchar(35) ,
LeaveStatus char(10) Null,
LeaveTaken int

)

INSERT INTO #Leave_Taken_Summ
( StaffNo ,
Department ,
LeaveStatus ,
LeaveTaken
)
d.C02_Dept ,
a.C03_lve_code,
sum(a.C04_lve_Taken)/8 as 'Total Taken'
FROM LEAVE_TRANS a,
H01_identfcatn b
where a.StaffNo = b.StaffNo
AND a.StaffNo = c.StaffNo
and a.C03_lve_code in ('MC')
and datepart(yy,a.L04_Start_date) = @year
and datepart(mm,a.L04_Start_date) = @month
group by b.StaffNo,b.C02_Dept ,a.C03_lve_code
ORDER BY b.StaffNo


END









bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/27/2013 :  04:38:58  Show Profile  Reply with Quote
Do you want @Month and @Year as OUTPUT Params for Procedure?
If yes,
CREATE PROCEDURE [dbo].[LEAVE_TAKEN]
( @Organisation_Code GLCOMPANY,
@Location_Code GLLOCN,
@Year SMALLINT OUT,
@Month SMALLINT OUT
)
AS
BEGIN
END

--Execution of above procedure
DECLARE @Year SMALLINT = 2013, @Month SMALLINT = 1
EXEC [dbo].[LEAVE_TAKEN] inputParamsValues, @Year OUT, @Month OUT
SELECT @Year, @Month
Go to Top of Page

abdbari
Starting Member

Malaysia
7 Posts

Posted - 03/27/2013 :  05:32:39  Show Profile  Reply with Quote
Now after execution the procedure:

only showings

staffno,
Department,
Leave status,
Leave taken,

--------------------
except for month and Year.

and datepart(yy,a.L04_Start_date) = 2013 ->enter manually.
and datepart(mm,a.L04_Start_date) = 1 ->enter manually.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 03/27/2013 :  07:11:50  Show Profile  Reply with Quote
Why these two Month varchar(35) , Year varchar(35)are Characters in #Leave_Taken_Summ?
Go to Top of Page

abdbari
Starting Member

Malaysia
7 Posts

Posted - 03/28/2013 :  02:49:28  Show Profile  Reply with Quote
i wrongly entered.
it should be Month varchar(4) , Year varchar(4)
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17437 Posts

Posted - 03/28/2013 :  04:45:01  Show Profile  Reply with Quote
What is your question actually ?

You show us the stored procedure LEAVE_TAKEN. However, in that procedure, you create a temp table #Leave_Taken_Summ then insert some records into it and that's it. Do note that, the temp table will be automatically dropped when the stored procedure ended. What do you expect the stored procedure to do ?


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000