simple method to avoid Cursor in ma storeprocedur

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

SQL Server 2005 Forums

Transact-SQL (2005)

simple method to avoid Cursor in ma storeprocedur

Author

Topic

umar
Starting Member

6 Posts

Posted - 2009-01-22 : 05:51:16

this is ma procedure

ALTER PROCEDURE [dbo].[lntsp_Csti_Hr_Stipend_Last_Report]
(

@Trade_Code varchar(15)=null,

@CstiCode VARCHAR(15)= NULL,
@SelYear int=null,
@SelMonth int=null,
@SelFN int=null,

@StipendFrom VARCHAR(50)= NULL,
@StipendTo VARCHAR(50)= NULL,
@LeavingDate varchar(50)=null,

@uid int=0,
@error VARCHAR(100)=null OUTPUT
)

AS

SET NOCOUNT ON

/*
Created By : umar
Created On : 2-jan-2009
Purpose : lntsp_Csti_Hr_Stipend_Last_Report
*/

BEGIN

---Creating Temparay table for finding Extra holiday based on P&OD
create table #temp_Extra_Holiday_L
(trainee_Id varchar(15),Extra_Holiday int)
----get set of trainee id based condition
declare @TraineeID varchar(15)
declare @ExdL int

select REPLACE(CONVERT(CHAR(11), holiday_date, 113),space(1),'-') as holiday_date
into #Csti_Hr_Holiday_Master1 from Csti_Hr_Holiday_Master
where csticode=@CstiCode and (holiday_date BETWEEN @StipendFrom and @StipendTo)

DECLARE @NoofDays int
set @NoofDays = datediff(dd ,@StipendFrom ,@StipendTo)+1

insert into #Csti_Hr_Holiday_Master1 (holiday_date)
select REPLACE(CONVERT(CHAR(11), DATEADD(dd, M.number-1, @StipendFrom), 113),space(1),'-')
FROM master..spt_values AS M
WHERE M.type = 'P' and datename(dw,DATEADD(dd, M.number-1, @StipendFrom))='Sunday' and
M.number BETWEEN 1 AND @NoofDays

select distinct(holiday_date) into #Csti_Hr_Holiday_Master from #Csti_Hr_Holiday_Master1

select tr.trainee_id into #temp_trainee_id_GRL
from Csti_Hr_Trainees tr where tr.Joined_Csti_Code=@CstiCode and tr.joined_trade_code=@Trade_Code and (
(@StipendFrom between tr.Joining_Date and tr.Leaving_Date) OR
(@StipendTo between tr.Joining_Date and tr.Leaving_Date)) and (tr.leaving_date between @StipendFrom and @StipendTo)
and tr.leaving_date=@LeavingDate
----ctreate cursor for that set of trainee id
declare cursor_tid cursor for
select trainee_id from #temp_trainee_id_GRL order by trainee_id
--open & fetch cursor
open cursor_tid
fetch cursor_tid into @TraineeID --FETCH NEXT FROM cursor_po INTO @po_code

while (@@FETCH_STATUS = 0)
begin

--print @TraineeID

----get set of Absent date based condition

declare @Absent_dateL varchar(50)
declare @tcL int
set @ExdL=0

select Absent_date into #temp_absent_date_L
from Csti_Hr_Trainee_Absent_Register tr where
tr.trainee_id=@TraineeID AND (Absent_date BETWEEN @StipendFrom and @StipendTo)

-- DECLARE @NoofDays int
-- set @NoofDays = datediff(dd ,@StipendFrom ,@StipendTo)+1
--
-- insert into #temp_absent_date_L (Absent_date)
-- select REPLACE(CONVERT(CHAR(11), DATEADD(dd, M.number-1, @StipendFrom), 113),space(1),'-')
-- FROM master..spt_values AS M
-- WHERE M.type = 'P' and datename(dw,DATEADD(dd, M.number-1, @StipendFrom))='Sunday' and
-- M.number BETWEEN 1 AND @NoofDays

----ctreate cursor for that set of Absent dates

declare cursor_ad cursor for
select Absent_date from #temp_absent_date_L order by Absent_date
--open & fetch cursor
open cursor_ad
fetch cursor_ad into @Absent_dateL

while (@@FETCH_STATUS = 0)
begin
--print @Absent_dateL
DECLARE @FlagL INT
SET @FlagL = 0
declare @holiday_dateL datetime
set @holiday_dateL=dateadd(day,1,@Absent_dateL)

IF(select holiday_date from #Csti_Hr_Holiday_Master where holiday_date=@holiday_dateL ) IS NOT NULL --csticode=@CstiCode and and (holiday_date BETWEEN @StipendFrom and @StipendTo)
BEGIN
WHILE (select holiday_date from #Csti_Hr_Holiday_Master where holiday_date=@holiday_dateL) is not null --csticode=@CstiCode and
BEGIN
SET @FlagL = @FlagL + 1
set @holiday_dateL=dateadd(day,1,@holiday_dateL)
END
-- PRINT '-FINAL'
if(select Absent_date from Csti_Hr_Trainee_Absent_Register where trainee_id=@TraineeID and Absent_date=@holiday_dateL) is null
-- PRINT @FlagL
-- ELSE
-- SET @FlagL=0
begin
SET @FlagL=0
end
END
--print @FlagL--------------extra
--Add the number of extra days
set @ExdL=@ExdL+@FlagL
FETCH NEXT FROM cursor_ad INTO @Absent_dateL
--select @TraineeID,@FlagL
end
close cursor_ad
deallocate cursor_ad
drop table #temp_absent_date_L
--print @ExdL-------total extra
--insert Extra days into temp table

insert into #temp_Extra_Holiday_L
select @TraineeID,@ExdL

FETCH NEXT FROM cursor_tid INTO @TraineeID
end
--close the cursor
close cursor_tid
deallocate cursor_tid

---select * from #temp_Extra_Holiday_L

create table #StipendRuleNetL
(
Trade_Code varchar(15),
Stipend_Per_Day money
)
insert into #StipendRuleNetL (Trade_Code, Stipend_Per_Day)
select tr.Trade_Code, (tr.Stipend_In_INR /tr.Duration_In_Days)
from Csti_Hr_Trade_Availability_Master tr where tr.Csti_code = @CstiCode

create table #AbsentCountNetL
(
Trainee_ID varchar(15),
AbsentCount smallint
)
insert into #AbsentCountNetL (Trainee_ID, AbsentCount)
select Trainee_ID, count(*) from dbo.Csti_Hr_Trainee_Absent_Register where
(Absent_Date between @StipendFrom and @StipendTo) AND
(Trainee_ID in (select Trainee_ID from Csti_Hr_Trainees where Joined_Csti_Code = @CstiCode ))
group by Trainee_ID

create table #StiListNetL
(
Trainee_ID varchar(15),
Applicant_Name varchar(100),
father_name varchar(100),
Age int,
Joined_Trade_Code varchar(15),
Batch_Id int,
Trade varchar(100),
Joining_Date datetime,--varchar(50),
Leaving_Date datetime,--varchar(50),
From_Date datetime,--varchar(50),
To_Date datetime,--varchar(50),
Remarks varchar(100),
MAXWD smallint,
ABSWD smallint,
ACTWD smallint,
Holiday_Count smallint
)
insert into #StiListNetL
(Trainee_ID, Applicant_Name,father_name, Age, Joined_Trade_Code, Batch_Id, Trade, Joining_Date, Leaving_Date,
From_Date, To_Date,Remarks, MAXWD, ABSWD)
select tr.Trainee_ID, hr.Applicant_Name,hr.father_name, hr.Age, tr.Joined_Trade_Code, tr.Batch_Id, tm.Trade_Description_HR as Trade,
tr.Joining_Date, tr.Leaving_Date,
@StipendFrom, @StipendTo, --Default FromDate & ToDate
'Remarks' = CASE
when (tr.Joining_Date >= @StipendFrom) then 'New Joinee'
when (tr.Leaving_Date <= @StipendTo) then 'Out Going'
else ''
END,
'MAXWD' = CASE
when (tr.Joining_Date >= @StipendFrom) then (1+DATEDIFF(day, tr.Joining_Date, @StipendTo))
when (tr.Leaving_Date <= @StipendTo) then (1+DATEDIFF(day, @StipendFrom, tr.Leaving_Date))
else (1+DATEDIFF(day, @StipendFrom, @StipendTo))
END,
isnull(aa.AbsentCount,0)
from Csti_Hr_Trainees tr inner join
Csti_Trade_Master tm on tr.Joined_Trade_Code = tm.Trade_Code inner join
Csti_Hr_Applicants_View hr on tr.Application_ID = hr.Application_ID left join
#AbsentCountNetL aa on tr.Trainee_ID = aa.Trainee_ID
where
Joined_Csti_Code=@CstiCode and joined_trade_code=@Trade_Code and (
(@StipendFrom between tr.Joining_Date and tr.Leaving_Date) OR
(@StipendTo between tr.Joining_Date and tr.Leaving_Date)) and (tr.leaving_date between @StipendFrom and @StipendTo)
and tr.leaving_date=@LeavingDate and tr.Cancelled_by is null

update #StiListNetL set actwd = maxwd-abswd
update #StiListNetL set From_Date = Joining_Date where Joining_Date > @StipendFrom
update #StiListNetL set To_Date = Leaving_Date where Leaving_Date < @StipendTo

update #StiListNetL set Holiday_Count=isnull((select count(*) from #Csti_Hr_Holiday_Master where Holiday_date BETWEEN From_Date and To_Date),0)

select distinct sd.trainee_id, tr.joined_csti_code as csti_code, tr.joined_trade_code as trade_code,sd.stipend_year,sd.stipend_month,sd.stipend_fn into #totalNetL
from Csti_Hr_Stipend_Deductions sd inner join Csti_Hr_Trainees tr on sd.trainee_id=tr.trainee_id

select sd.trainee_id, tr.joined_csti_code as csti_code, tr.joined_trade_code as trade_code,sd.stipend_year,sd.stipend_month,sd.stipend_fn,Temp_Amt_Deducted= sum(isnull(sd.Amount_Deducted,0)) into #tempDeductionNetL
from Csti_Hr_Stipend_Deductions sd inner join Csti_Hr_Trainees tr on sd.trainee_id=tr.trainee_id
where sd.cancelled_by is null and sd.Deduction_Type='T'
group by sd.trainee_id, tr.joined_csti_code, tr.joined_trade_code,sd.Deduction_Type,sd.stipend_year,sd.stipend_month,sd.stipend_fn

select sd.trainee_id, tr.joined_csti_code as csti_code, tr.joined_trade_code as trade_code,sd.stipend_year,sd.stipend_month,sd.stipend_fn,Perm_Amt_Deducted= sum(isnull(sd.Amount_Deducted,0)) into #PerDeductionNetL
from Csti_Hr_Stipend_Deductions sd inner join Csti_Hr_Trainees tr on sd.trainee_id=tr.trainee_id
where sd.cancelled_by is null and sd.Deduction_Type='P'
group by sd.trainee_id, tr.joined_csti_code, tr.joined_trade_code,sd.stipend_year,sd.stipend_month,sd.stipend_fn

select t.trainee_id,t.Csti_Code,t.Trade_Code,t.stipend_year,t.stipend_month,t.stipend_fn,
p.Perm_Amt_Deducted,tt.Temp_Amt_Deducted into #TotAmtDeductedNetL--,ed.Extra_Holiday
from #totalNetL t left join #PerDeductionNetL p on t.trainee_id=p.trainee_id and t.Csti_Code=p.Csti_Code
and t.stipend_year=p.stipend_year and t.stipend_month=p.stipend_month and t.stipend_fn = p.stipend_fn
left join #tempDeductionNetL tt on t.trainee_id=tt.trainee_id and t.Csti_Code=tt.Csti_Code
and t.stipend_year=tt.stipend_year and t.stipend_month=tt.stipend_month and t.stipend_fn = tt.stipend_fn
--left join #temp_Extra_Holiday_L ed on t.trainee_id= ed.Trainee_Id
where t.stipend_year=@SelYear and t.stipend_month=@SelMonth and t.stipend_fn=@SelFN

select @CstiCode as Dept_Code,dm.dept_description,@Trade_Code Trade_code,tm.trade_description_hr as trade_description,'Last' as Dis_type,sti.Trainee_ID,sti.Applicant_Name,sti.father_name,sti.ACTWD,
ACTD_POD=case when (sti.MAXWD-(sti.Holiday_Count+isnull(ac.AbsentCount , 0)))=0 then 0 else (sti.ACTWD-ed.Extra_Holiday) end , sday.Stipend_Per_Day,
cast(((case when (sti.MAXWD-(sti.Holiday_Count+isnull(ac.AbsentCount , 0)))=0 then 0 else (sti.ACTWD-ed.Extra_Holiday) end) * sday.Stipend_Per_Day) as int) as Stipend_Payable,
--cast(cast((sti.actwd * sday.Stipend_Per_Day) as int) as decimal(12,2)) as Stipend_Payable,
cast(tad.temp_amt_deducted as int) as temp_amt_deducted ,
cast(tad.perm_amt_deducted as int) as perm_amt_deducted,
cast((cast(((case when (sti.MAXWD-(sti.Holiday_Count+isnull(ac.AbsentCount , 0)))=0 then 0 else (sti.ACTWD-ed.Extra_Holiday) end) * sday.Stipend_Per_Day) as decimal(12,2))-(isnull(tad.temp_amt_deducted,0) + isnull(tad.perm_amt_deducted,0))) as int) as Net_Stipend_Payable,
sti.Remarks,@SelMonth month ,@SelYear year,@SelFN FN,@StipendFrom StipendFrom,@StipendTo StipendTo,@LeavingDate LeavingDate ,sti.Age, sti.Batch_Id,ed.Extra_Holiday
from #StiListNetL sti left join
#StipendRuleNetL sday on sti.Joined_Trade_Code=sday.Trade_Code
left join #TotAmtDeductedNetL tad on sti.trainee_id=tad.trainee_id
left join Csti_Hr_Depts_All dm on dm.dept_code=@CstiCode
left join #temp_Extra_Holiday_L ed on sti.trainee_id= ed.Trainee_Id
left join Csti_Trade_Master tm on tm.trade_code=@Trade_Code
left join #AbsentCountNetL ac on ac.trainee_id=sti.trainee_id
order by sti.Trainee_ID--Joined_Trade_Code,

END

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-22 : 05:59:54

Can you please edit your post and add [ code ] and [ /code ] tags around your SQL?

- Lumbago

umar
Starting Member

6 Posts

Posted - 2009-01-23 : 03:31:28

[code]

ALTER PROCEDURE [dbo].[lntsp_Csti_Hr_Stipend_Last_Report]
(

@Trade_Code varchar(15)=null,

@CstiCode VARCHAR(15)= NULL,
@SelYear int=null,
@SelMonth int=null,
@SelFN int=null,

@StipendFrom VARCHAR(50)= NULL,
@StipendTo VARCHAR(50)= NULL,
@LeavingDate varchar(50)=null,

@uid int=0,
@error VARCHAR(100)=null OUTPUT
)

AS

SET NOCOUNT ON

/*
Created By : umar
Created On : 2-jan-2009
Purpose : lntsp_Csti_Hr_Stipend_Last_Report
*/

BEGIN

---Creating Temparay table for finding Extra holiday based on P&OD
create table #temp_Extra_Holiday_L
(trainee_Id varchar(15),Extra_Holiday int)
----get set of trainee id based condition
declare @TraineeID varchar(15)
declare @ExdL int

select REPLACE(CONVERT(CHAR(11), holiday_date, 113),space(1),'-') as holiday_date
into #Csti_Hr_Holiday_Master1 from Csti_Hr_Holiday_Master
where csticode=@CstiCode and (holiday_date BETWEEN @StipendFrom and @StipendTo)

DECLARE @NoofDays int
set @NoofDays = datediff(dd ,@StipendFrom ,@StipendTo)+1

insert into #Csti_Hr_Holiday_Master1 (holiday_date)
select REPLACE(CONVERT(CHAR(11), DATEADD(dd, M.number-1, @StipendFrom), 113),space(1),'-')
FROM master..spt_values AS M
WHERE M.type = 'P' and datename(dw,DATEADD(dd, M.number-1, @StipendFrom))='Sunday' and
M.number BETWEEN 1 AND @NoofDays

select distinct(holiday_date) into #Csti_Hr_Holiday_Master from #Csti_Hr_Holiday_Master1

select tr.trainee_id into #temp_trainee_id_GRL
from Csti_Hr_Trainees tr where tr.Joined_Csti_Code=@CstiCode and tr.joined_trade_code=@Trade_Code and (
(@StipendFrom between tr.Joining_Date and tr.Leaving_Date) OR
(@StipendTo between tr.Joining_Date and tr.Leaving_Date)) and (tr.leaving_date between @StipendFrom and @StipendTo)
and tr.leaving_date=@LeavingDate
----ctreate cursor for that set of trainee id
declare cursor_tid cursor for
select trainee_id from #temp_trainee_id_GRL order by trainee_id
--open & fetch cursor
open cursor_tid
fetch cursor_tid into @TraineeID --FETCH NEXT FROM cursor_po INTO @po_code

while (@@FETCH_STATUS = 0)
begin

--print @TraineeID

----get set of Absent date based condition

declare @Absent_dateL varchar(50)
declare @tcL int
set @ExdL=0

select Absent_date into #temp_absent_date_L
from Csti_Hr_Trainee_Absent_Register tr where
tr.trainee_id=@TraineeID AND (Absent_date BETWEEN @StipendFrom and @StipendTo)

-- DECLARE @NoofDays int
-- set @NoofDays = datediff(dd ,@StipendFrom ,@StipendTo)+1
--
-- insert into #temp_absent_date_L (Absent_date)
-- select REPLACE(CONVERT(CHAR(11), DATEADD(dd, M.number-1, @StipendFrom), 113),space(1),'-')
-- FROM master..spt_values AS M
-- WHERE M.type = 'P' and datename(dw,DATEADD(dd, M.number-1, @StipendFrom))='Sunday' and
-- M.number BETWEEN 1 AND @NoofDays

----ctreate cursor for that set of Absent dates

declare cursor_ad cursor for
select Absent_date from #temp_absent_date_L order by Absent_date
--open & fetch cursor
open cursor_ad
fetch cursor_ad into @Absent_dateL

while (@@FETCH_STATUS = 0)
begin
--print @Absent_dateL
DECLARE @FlagL INT
SET @FlagL = 0
declare @holiday_dateL datetime
set @holiday_dateL=dateadd(day,1,@Absent_dateL)

IF(select holiday_date from #Csti_Hr_Holiday_Master where holiday_date=@holiday_dateL ) IS NOT NULL --csticode=@CstiCode and and (holiday_date BETWEEN @StipendFrom and @StipendTo)
BEGIN
WHILE (select holiday_date from #Csti_Hr_Holiday_Master where holiday_date=@holiday_dateL) is not null --csticode=@CstiCode and
BEGIN
SET @FlagL = @FlagL + 1
set @holiday_dateL=dateadd(day,1,@holiday_dateL)
END
-- PRINT '-FINAL'
if(select Absent_date from Csti_Hr_Trainee_Absent_Register where trainee_id=@TraineeID and Absent_date=@holiday_dateL) is null
-- PRINT @FlagL
-- ELSE
-- SET @FlagL=0
begin
SET @FlagL=0
end
END
--print @FlagL--------------extra
--Add the number of extra days
set @ExdL=@ExdL+@FlagL
FETCH NEXT FROM cursor_ad INTO @Absent_dateL
--select @TraineeID,@FlagL
end
close cursor_ad
deallocate cursor_ad
drop table #temp_absent_date_L
--print @ExdL-------total extra
--insert Extra days into temp table

insert into #temp_Extra_Holiday_L
select @TraineeID,@ExdL

FETCH NEXT FROM cursor_tid INTO @TraineeID
end
--close the cursor
close cursor_tid
deallocate cursor_tid

---select * from #temp_Extra_Holiday_L

create table #StipendRuleNetL
(
Trade_Code varchar(15),
Stipend_Per_Day money
)
insert into #StipendRuleNetL (Trade_Code, Stipend_Per_Day)
select tr.Trade_Code, (tr.Stipend_In_INR /tr.Duration_In_Days)
from Csti_Hr_Trade_Availability_Master tr where tr.Csti_code = @CstiCode

create table #AbsentCountNetL
(
Trainee_ID varchar(15),
AbsentCount smallint
)
insert into #AbsentCountNetL (Trainee_ID, AbsentCount)
select Trainee_ID, count(*) from dbo.Csti_Hr_Trainee_Absent_Register where
(Absent_Date between @StipendFrom and @StipendTo) AND
(Trainee_ID in (select Trainee_ID from Csti_Hr_Trainees where Joined_Csti_Code = @CstiCode ))
group by Trainee_ID

create table #StiListNetL
(
Trainee_ID varchar(15),
Applicant_Name varchar(100),
father_name varchar(100),
Age int,
Joined_Trade_Code varchar(15),
Batch_Id int,
Trade varchar(100),
Joining_Date datetime,--varchar(50),
Leaving_Date datetime,--varchar(50),
From_Date datetime,--varchar(50),
To_Date datetime,--varchar(50),
Remarks varchar(100),
MAXWD smallint,
ABSWD smallint,
ACTWD smallint,
Holiday_Count smallint
)
insert into #StiListNetL
(Trainee_ID, Applicant_Name,father_name, Age, Joined_Trade_Code, Batch_Id, Trade, Joining_Date, Leaving_Date,
From_Date, To_Date,Remarks, MAXWD, ABSWD)
select tr.Trainee_ID, hr.Applicant_Name,hr.father_name, hr.Age, tr.Joined_Trade_Code, tr.Batch_Id, tm.Trade_Description_HR as Trade,
tr.Joining_Date, tr.Leaving_Date,
@StipendFrom, @StipendTo, --Default FromDate & ToDate
'Remarks' = CASE
when (tr.Joining_Date >= @StipendFrom) then 'New Joinee'
when (tr.Leaving_Date <= @StipendTo) then 'Out Going'
else ''
END,
'MAXWD' = CASE
when (tr.Joining_Date >= @StipendFrom) then (1+DATEDIFF(day, tr.Joining_Date, @StipendTo))
when (tr.Leaving_Date <= @StipendTo) then (1+DATEDIFF(day, @StipendFrom, tr.Leaving_Date))
else (1+DATEDIFF(day, @StipendFrom, @StipendTo))
END,
isnull(aa.AbsentCount,0)
from Csti_Hr_Trainees tr inner join
Csti_Trade_Master tm on tr.Joined_Trade_Code = tm.Trade_Code inner join
Csti_Hr_Applicants_View hr on tr.Application_ID = hr.Application_ID left join
#AbsentCountNetL aa on tr.Trainee_ID = aa.Trainee_ID
where
Joined_Csti_Code=@CstiCode and joined_trade_code=@Trade_Code and (
(@StipendFrom between tr.Joining_Date and tr.Leaving_Date) OR
(@StipendTo between tr.Joining_Date and tr.Leaving_Date)) and (tr.leaving_date between @StipendFrom and @StipendTo)
and tr.leaving_date=@LeavingDate and tr.Cancelled_by is null

update #StiListNetL set actwd = maxwd-abswd
update #StiListNetL set From_Date = Joining_Date where Joining_Date > @StipendFrom
update #StiListNetL set To_Date = Leaving_Date where Leaving_Date < @StipendTo

update #StiListNetL set Holiday_Count=isnull((select count(*) from #Csti_Hr_Holiday_Master where Holiday_date BETWEEN From_Date and To_Date),0)

select distinct sd.trainee_id, tr.joined_csti_code as csti_code, tr.joined_trade_code as trade_code,sd.stipend_year,sd.stipend_month,sd.stipend_fn into #totalNetL
from Csti_Hr_Stipend_Deductions sd inner join Csti_Hr_Trainees tr on sd.trainee_id=tr.trainee_id

select sd.trainee_id, tr.joined_csti_code as csti_code, tr.joined_trade_code as trade_code,sd.stipend_year,sd.stipend_month,sd.stipend_fn,Temp_Amt_Deducted= sum(isnull(sd.Amount_Deducted,0)) into #tempDeductionNetL
from Csti_Hr_Stipend_Deductions sd inner join Csti_Hr_Trainees tr on sd.trainee_id=tr.trainee_id
where sd.cancelled_by is null and sd.Deduction_Type='T'
group by sd.trainee_id, tr.joined_csti_code, tr.joined_trade_code,sd.Deduction_Type,sd.stipend_year,sd.stipend_month,sd.stipend_fn

select sd.trainee_id, tr.joined_csti_code as csti_code, tr.joined_trade_code as trade_code,sd.stipend_year,sd.stipend_month,sd.stipend_fn,Perm_Amt_Deducted= sum(isnull(sd.Amount_Deducted,0)) into #PerDeductionNetL
from Csti_Hr_Stipend_Deductions sd inner join Csti_Hr_Trainees tr on sd.trainee_id=tr.trainee_id
where sd.cancelled_by is null and sd.Deduction_Type='P'
group by sd.trainee_id, tr.joined_csti_code, tr.joined_trade_code,sd.stipend_year,sd.stipend_month,sd.stipend_fn

select t.trainee_id,t.Csti_Code,t.Trade_Code,t.stipend_year,t.stipend_month,t.stipend_fn,
p.Perm_Amt_Deducted,tt.Temp_Amt_Deducted into #TotAmtDeductedNetL--,ed.Extra_Holiday
from #totalNetL t left join #PerDeductionNetL p on t.trainee_id=p.trainee_id and t.Csti_Code=p.Csti_Code
and t.stipend_year=p.stipend_year and t.stipend_month=p.stipend_month and t.stipend_fn = p.stipend_fn
left join #tempDeductionNetL tt on t.trainee_id=tt.trainee_id and t.Csti_Code=tt.Csti_Code
and t.stipend_year=tt.stipend_year and t.stipend_month=tt.stipend_month and t.stipend_fn = tt.stipend_fn
--left join #temp_Extra_Holiday_L ed on t.trainee_id= ed.Trainee_Id
where t.stipend_year=@SelYear and t.stipend_month=@SelMonth and t.stipend_fn=@SelFN

select @CstiCode as Dept_Code,dm.dept_description,@Trade_Code Trade_code,tm.trade_description_hr as trade_description,'Last' as Dis_type,sti.Trainee_ID,sti.Applicant_Name,sti.father_name,sti.ACTWD,
ACTD_POD=case when (sti.MAXWD-(sti.Holiday_Count+isnull(ac.AbsentCount , 0)))=0 then 0 else (sti.ACTWD-ed.Extra_Holiday) end , sday.Stipend_Per_Day,
cast(((case when (sti.MAXWD-(sti.Holiday_Count+isnull(ac.AbsentCount , 0)))=0 then 0 else (sti.ACTWD-ed.Extra_Holiday) end) * sday.Stipend_Per_Day) as int) as Stipend_Payable,
--cast(cast((sti.actwd * sday.Stipend_Per_Day) as int) as decimal(12,2)) as Stipend_Payable,
cast(tad.temp_amt_deducted as int) as temp_amt_deducted ,
cast(tad.perm_amt_deducted as int) as perm_amt_deducted,
cast((cast(((case when (sti.MAXWD-(sti.Holiday_Count+isnull(ac.AbsentCount , 0)))=0 then 0 else (sti.ACTWD-ed.Extra_Holiday) end) * sday.Stipend_Per_Day) as decimal(12,2))-(isnull(tad.temp_amt_deducted,0) + isnull(tad.perm_amt_deducted,0))) as int) as Net_Stipend_Payable,
sti.Remarks,@SelMonth month ,@SelYear year,@SelFN FN,@StipendFrom StipendFrom,@StipendTo StipendTo,@LeavingDate LeavingDate ,sti.Age, sti.Batch_Id,ed.Extra_Holiday
from #StiListNetL sti left join
#StipendRuleNetL sday on sti.Joined_Trade_Code=sday.Trade_Code
left join #TotAmtDeductedNetL tad on sti.trainee_id=tad.trainee_id
left join Csti_Hr_Depts_All dm on dm.dept_code=@CstiCode
left join #temp_Extra_Holiday_L ed on sti.trainee_id= ed.Trainee_Id
left join Csti_Trade_Master tm on tm.trade_code=@Trade_Code
left join #AbsentCountNetL ac on ac.trainee_id=sti.trainee_id
order by sti.Trainee_ID--Joined_Trade_Code,

END

[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources