May 29, 2012 at 4:56 am
i have made the below SP
but stuck up at some places
ALTER proc [dbo].[S_process]
@pMonth int, @pYear int, @pEmpId varchar(10), @p_startdate date,@p_enddate date
as
begin
declare @days int, @dt date
set @dt = cast(@pYear as varchar)+'-'+CAST(@pMonth as varchar) + '-01'
set @days = DATEDIFF(Y, @dt, DATEADD(M,1,@dt))
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM monthlysal WHERE empid = @pEmpId and [month]=@pMonth and [year]= @pYear )
UPDATE monthlysal
SET AMOUNT =AMOUNT WHERE empid = @pEmpId and [month]=@pMonth and [year]= @pYear -- need to add SAL_component
ELSE
insert into monthlysal
(
[month], [year], empid, SAL_component ,AMOUNT ,C_TYPE
)
select @pMonth, @pYear,a.empid, a.SAL_component, (a.C_AMT*(@days - isnull(noofdays,0)))/@days ,C_TYPE
frommonthlysal_STRUCT a
inner join
(
selectempid, MAX(effectivedate) as effectivedate
frommonthlysal_STRUCT
group by empid
)
b on a.empid = b.empid and a.effectivedate = b.effectivedate
left join (
selectempid, SUM(noofdays) as leavedays
--, (case when todate > @p_enddate then @p_enddate else todate end) -
--(case when frmdate > @p_startdate then frmdate else @p_startdate end) as leavedays
fromTBLLEAVE
whereleave_type = 'UP' and YEAR(todate) = @pYear group by empid ,frmdate,todate
) lv on a.empid = lv.empid
1) how do i calculate the the days /date for the specified month
2) have issue with update
May 29, 2012 at 9:10 am
Days in a month (assumes current month):
DECLARE @ThisDate DATE = GETDATE()
select datepart(dd,dateadd(dd,-1,dateadd(mm,1,dateadd(mm, datediff(mm, 0, @ThisDate), 0)))) -- Number of days in the current month
It looks like you're trying to set AMOUNT equal to itself. What are you really trying to do here?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply