How to make scripts of Sp in sql server?

Question

1.00/5 (1 vote)

See more:

SQL

server

How to script sp in ms sql server 2008

Posted 24-Feb-15 20:03pm

Project CSE

Add a Solution

Comments

Project CSE 25-Feb-15 2:04am

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[AddPreviousSalaryData]
as
begin

insert into MonthlyPSOthersHistory select Right('0000000'+IdNumber,8) as MpoPIN,YEAR(Paid_date) as MpoYear, MONTH(Paid_date) as MpoMonth
,Paid_date as MpoGenDate,IsNull(Basic,0) as MpoBasicAmount, 0 as MpoOtherAmount,'' as MpoOtherAmountName,0 as MpoConsolidatedYN
,0 as MpoCashYN,0.00 as MpoOtherAdjAmount,'' as MpoOtherAdjNotes,0 as MpoUPLDeduction,0 as MpoTranDeduction, IsNull(InTax,0) as MpoIncomeTax
,IsNull(PF,0) as MpoPFAmount, IsNull(HBL,0) +IsNull(CL,0)+IsNull(CCS,0)+IsNull(CS,0) as MpoLoanReaAmount, IsNull(Others,0) as MpoRCDeductAmt, 0 as MpoSecurityAmt, 1 as MpoAutoGenYN,null as MpoAccCode,
IsNull(Entry_Time,GETDATE()) as SetDate,IsNull(Entry_User,'') as UserName1,0 as VerifiedYN,'' as UserName2,'P' as MpoAccPostingStatus from Salary_hstry

insert into MonthlyPSDetailsHistory
select Right('0000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '001' as MphSbkID,IsNull(Basic,0) as MphAmount,'P' as MphAccPostingStatus from Salary_hstry
union all
select Right('0000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '401' as MphSbkID,IsNull(HRent,0) as MphAmount,'P' as MphAccPostingStatus from Salary_hstry
union all
select Right('0000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '009' as MphSbkID,IsNull(HMnt,0) as MphAmount,'P' as MphAccPostingStatus from Salary_hstry
union all
select Right('0000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '008' as MphSbkID,IsNull(Utility,0) as MphAmount,'P' as MphAccPostingStatus from Salary_hstry
union all
select Right('0000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '006' as MphSbkID,IsNull(Med,0) as MphAmount,'P' as MphAccPostingStatus from Salary_hstry
union all
select Right('0000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '010' as MphSbkID,IsNull(LFA,0) as MphAmount,'P' as MphAccPostingStatus from Salary_hstry
union all
select Right('00000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '005' as MphSbkID,IsNull(Ent,0) as MphAmount,'P' as MphAccPostingStatus from Salary_hstry
union all
select Right('0000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '007' as MphSbkID,IsNull(Conv,0) as MphAmount,'P' as MphAccPostingStatus from Salary_hstry
--union all
--select Right('0000000'+IdNumber,8) as MphPIN,YEAR(Paid_date) as MphYear,MONTH(Paid_date) as MphMonth, '005' as MphSbkID,Ent as MphAmount,'P' as MphAccPostingStatus from Salary_hstry

End

begin transaction
exec AddPreviousSalaryData
rollback transaction

_Asif_ 25-Feb-15 2:06am

We can't read your mind, your hard disk and your screen. You need to explain your problem well, please improve your question by adding more details

Dinesh92d 25-Feb-15 2:11am

Your Qus is how to run the script? ist

Thanks7872 25-Feb-15 2:23am

- Open sp in SSMS and press ctrl + s.
- Another option is, right click sp-Script procedure as-select option you want

Project CSE 15-Mar-15 3:27am

---------------------------XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX----------------------------------

Project CSE 15-Mar-15 3:27am

CREATE PROCEDURE [dbo].[SP_Pfm_rptPFAnnualBalance_Test]
@JLFrom nchar(3)=null,
@JLTo nchar(3)=null,
@ExcludeClosed bit,
@Year smallint

--With Encryption
AS

Set dateformat 'DMY'
Set nocount on

Create Table #PFMember(PmPIN nchar(8), StaffName nvarchar(50), PfID nvarchar(8),PFJoinDate datetime, PFStatusID nchar(1))
Create Table #PFOpeingBalance(PmPIN nchar(8),OwnConBalance money,OrgConBalance money,OwnIntBalance money,OrgIntBalance money)
Create Table #PFCYBalance(PmPIN nchar(8),OwnConBalance money,OrgConBalance money,OwnIntBalance money,OrgIntBalance money)
Create Table #PFWithdrawal(PmPIN nchar(8),OwnConBalance money,OrgConBalance money,OwnIntBalance money,OrgIntBalance money)

IF @ExcludeClosed=1
BEGIN
Insert Into #PFMember
Select PssPin, Rtrim(StaFname + space(1)+ISNULL(StaMname,'')) + space(1)+ StaLname AS StaffName,
PssPfID,PssPfJoinDate,PssPfStatusID
From PFStaffStatus inner join Staff on PssPin=StaPIN
Where year(PssPfJoinDate)*100+month(PssPfJoinDate)<=@Year*100+12
And PssPin not in(Select PshPin from PFStaffStatusHistory where PshPFStatusID=2 and Year(pshChangedate)*10000+month(pshChangedate)*100+day(pshChangedate) <=@Year*10000+12*100+31)
Order By PssPin
END
ELSE
BEGIN
Insert Into #PFMember
Select PssPin, StaFname + space(1)+ISNULL(StaMname,'') + space(1)+ StaLname AS StaffName,
PssPfID,PssPfJoinDate,PssPfStatusID
From PFStaffStatus inner join Staff on PssPin=StaPIN
where year(PssPfJoinDate)*100+month(PssPfJoinDate)<=@Year*100+12
And PssPin not in(Select PshPin from PFStaffStatusHistory where PshPFStatusID=2 and Year(pshChangedate)*10000+month(pshChangedate)*100+day(pshChangedate) <@Year*10000+01*100+01)
Order By PssPin
END

--Select * from #PFMember order by PmPIN
--return

Project CSE 15-Mar-15 3:28am

Insert into #PFOpeingBalance
Select pm.PmPIN,isnull(pc.OwnBalance,0)OwnBalance,isnull(pc.OrgBalance,0)OrgBalance,isnull(pfi.OwnInterest,0),ISNULL(pfi.OrgInterest,0)OrgInterest from #PFMember pm
left join
(
Select PmcPIN, Sum(PmcOwnCont+PmcOwnArrear)OwnBalance,Sum(PmcBracCont+PmcBracArrear)OrgBalance from PFMonthlyCollection where PmcYear < @Year
Group By PmcPIN
) pc on pc.PmcPIN=pm.PmPIN
left join
(
Select PciPIN, Sum(PciOwnInt)OwnInterest,Sum(PciBracInt)OrgInterest from PFCalculatedInterest where PciYear < @Year
Group By PciPIN
) pfi on pfi.PciPIN=pm.PmPIN

Insert into #PFCYBalance
Select pm.PmPIN,isnull(pc.OwnBalance,0)OwnBalance,isnull(pc.OrgBalance,0)OrgBalance,isnull(pfi.OwnInterest,0),ISNULL(pfi.OrgInterest,0)OrgInterest from #PFMember pm
left join
(
Select PmcPIN, Sum(PmcOwnCont+PmcOwnArrear)OwnBalance,Sum(PmcBracCont+PmcBracArrear)OrgBalance from PFMonthlyCollection where PmcYear =@Year
Group By PmcPIN
) pc on pc.PmcPIN=pm.PmPIN
left join
(
Select PciPIN, Sum(PciOwnInt)OwnInterest,Sum(PciBracInt)OrgInterest from PFCalculatedInterest where PciYear = @Year
Group By PciPIN
) pfi on pfi.PciPIN=pm.PmPIN

Insert into #PFWithdrawal
Select pm.PmPIN,isnull(pc.OwnBalance,0)+isnull(pfi.OwnBalance,0)OwnBalance,isnull(pc.OrgBalance,0)+isnull(pfi.OrgBalance,0)OrgBalance,isnull(pc.OwnInterest,0)+isnull(pfi.OwnInterest,0)OwnInterest,isnull(pc.OrgInterest,0)+ISNULL(pfi.OrgInterest,0)OrgInterest from #PFMember pm
left join
(
Select pfwPin,sum(PfwOwnCont)OwnBalance,sum(PfwBracCont)OrgBalance,sum(PfwOwnInt)OwnInterest,sum(PfwBracInt )OrgInterest
From PFStaffFinalWithdrawal Where year(PfwWdrawlDate)=@Year Group By pfwPin
) pc on pc.pfwPin=pm.PmPIN
left join
(
Select csaPIN,sum(csaOwnBalance)OwnBalance,sum(csaOrgBalance)OrgBalance,sum(csaOwnInterest)OwnInterest,sum(csaOrgInterest )OrgInterest
From PFClosedStaffAmountAdjustment Where year(csaAdjustmentDate)=@Year Group By csaPIN
) pfi on pfi.csaPIN=pm.PmPIN

Project CSE 15-Mar-15 3:28am

Select pm.PmPIN PmPIN
,pm.StaffName StaffName
,ss.SsDesID DesID
,d.DesName DesName
,pm.PfID PfID
,pm.PFJoinDate PFJoinDate
,pm.PFStatusID PFStatusID
,ob.OwnConBalance OwnOpeningBalance
,ob.OrgConBalance OrgOpeningBalance
,ob.OwnIntBalance OwnOpeningInterest
,ob.OrgIntBalance OrgOpeningInterest
,cb.OwnConBalance OwnCurYearBalance
,cb.OrgConBalance OrgCurYearBalance
,cb.OwnIntBalance OwnCurYearInterest
,cb.OrgIntBalance OrgCurYearInterest
,wb.OwnConBalance OwnWithdralBalance
,wb.OrgConBalance OrgWithdralBalance
,wb.OwnIntBalance OwnWithdralInterest
,wb.OrgIntBalance OrgWithdralInterest
--- Total---
,(ob.OwnConBalance+ob.OrgConBalance+ob.OwnIntBalance +ob.OrgIntBalance+
cb.OwnConBalance+cb.OrgConBalance+cb.OwnIntBalance+cb.OrgIntBalance)-
(wb.OwnConBalance+wb.OrgConBalance+wb.OwnIntBalance+wb.OrgIntBalance) CurrentBalance

from #PFMember pm
inner join #PFOpeingBalance ob on ob.PmPIN=pm.PmPIN
inner join #PFCYBalance cb on cb.PmPIN=pm.PmPIN
inner join #PFWithdrawal wb on wb.PmPIN=pm.PmPIN
inner join StaffStatus ss on ss.SsPIN=pm.PmPIN
inner join Designation d on d.DesID=ss.SsDesID

GO

Project CSE 18-Mar-15 6:32am

------------Mar-2015----------
Create FUNCTION [Fun_StaffDesignationIDByDate](@PIN [varchar](8), @setDate [datetime])
RETURNS [nvarchar](11) WITH EXECUTE AS CALLER
AS
Begin
declare @DesigID varchar(5)

Select @DesigID=SdDesID from StaffDesignation t where t.SdPIN=@PIN And t.SdEffectiveDate in(Select Max(SdEffectiveDate) SdEffectiveDate from StaffDesignation where SdPIN=@PIN And convert(DateTime,SdEffectiveDate,103)<=Convert(DateTime,@setDate,103))
if @DesigID is Null
Begin
Select @DesigID=SdDesID from StaffDesignation t where t.SdPIN=@PIN And t.SdEffectiveDate in(Select Min(SdEffectiveDate) SdEffectiveDate from StaffDesignation where SdPIN=@PIN)
End
Return @DesigID
End

GO

alter PROCEDURE [dbo].[SP_Pay_RptStaffArrearSalaryAnalysis]
@StaPIN VARCHAR(8),
@AreaID VARCHAR(4),
@FromDate DATETIME,
@ToDate DATETIME

With Encryption

AS

SET NOCOUNT ON
SET DATEFORMAT 'dmy'

DECLARE @FromYearMonth as nchar(6)
SET @FromYearMonth = Year(@FromDate)*100+MONTH(@FromDate)

DECLARE @ToYearMonth as nchar(6)
SET @ToYearMonth = Year(@ToDate)*100+MONTH(@ToDate)

CREATE TABLE #TMonthyPsDetailsHistory(SetDate DateTime,SalaryYear int, SalaryMonth int,PIN NCHAR(8), SbkId NCHAR(3), Amount NUMERIC(12,2))

CREATE TABLE #TMonthlyPsSalary
(PIN NCHAR(8),SetDate DateTime,ArrearYear Int,ArrearMonth int, Basic NUMERIC(11), Housing NUMERIC(11), Medical NUMERIC(11), Conveyance NUMERIC(11),Living NUMERIC(11),Ent Numeric(11), HMnt Numeric(11),LFA Numeric(11),Utility Numeric(11),Others Numeric(11) )
------------------------------------------------------------------------------------------------------------------------

DECLARE @PFRatio AS MONEY

Select Top 1 @PFRatio=isnull(PFContributionRatio,6) From BranchModuleFor
Select @PFRatio=(@PFRatio/100)

INSERT INTO #TMonthyPsDetailsHistory
SELECT a.SetDate,a.SssSalaryYear,a.SssSalaryMonth,b.SsdPin,b.SsdSbkID,b.SsdAmt FROM tbl_pay_StaffSalaryArrearOthers a INNER JOIN
tbl_pay_StaffSalaryArrearDetails b ON a.SssPin=b.SsdPin AND a.SssSalaryYear=b.SsdSalaryYear AND a.SssSalaryMonth=b.SsdSalaryMonth
WHERE SssPin LIKE @StaPIN AND a.SetDate between @FromDate and @ToDate

Project CSE 18-Mar-15 6:32am

INSERT INTO #TMonthlyPsSalary
SELECT details.PIN,others.SetDate,others.SssSalaryYear,others.SssSalaryMonth, Basic =SUM(Case when details.SbkID = '001' then Amount else 0 END),
Housing =SUM(Case when details.SbkID = '401' then Amount else 0 END),
Medical =SUM(Case when details.SbkID = '006' then Amount else 0 END),
Conveyance =SUM(Case when details.SbkID = '007' then Amount else 0 END),
Living =SUM(Case when details.SbkID = '604' then Amount else 0 END),
Ent=Sum(Case when details.SbkId='005' then Amount else 0 END),
HMnt=Sum(Case when details.SbkId='009' then Amount else 0 END),
LFA=SUM(Case when details.SbkId='010' then Amount else 0 END),
Utility=Sum(Case when details.SbkId='008' then Amount else 0 END),
Others=0
FROM #TMonthyPsDetailsHistory details
inner join tbl_pay_StaffSalaryArrearOthers others on
details.PIN=others.SssPin and details.SalaryYear=others.SssSalaryYear and details.SalaryMonth=others.SssSalaryMonth
left Join PFStaffStatus pss on pss.PssPin=details.PIN
GROUP BY details.PIN,others.SetDate,others.SssSalaryYear,others.SssSalaryMonth

select demo.*,Left([dbo].[Fun_StaffMonthlyPFSalaryBranchInfo](demo.PIN,demo.SetDate),4) AreaID,Substring([dbo].[Fun_StaffMonthlyPFSalaryBranchInfo](demo.PIN,demo.SetDate),5,4) DivID,Right([dbo].[Fun_StaffMonthlyPFSalaryBranchInfo](demo.PIN,demo.SetDate),3) DeptID
,[dbo].[Fun_StaffDesignationIDByDate](demo.PIN,demo.SetDate) DesigID

into #FinalData
from #TMonthlyPsSalary demo

select SetDate=Convert(varchar(11),f.SetDate,106),ArrearDate=(dbo.MakeDate(f.ArrearYear,f.ArrearMonth,1)),f.PIN,StaffName=isnull(st.StaFname,'') + ' ' + isnull(st.StaMname,' ') + ' ' + isnull(st.StaLname,''),
DesName=desig.DesName,pr.ProjectId,pr.ProjectName,a.AreaID,a.AreaName,div.ordDivisionName,
BasicAmount=ISNULL(f.Basic,0),Housing=isnull(f.Housing,0),Medical=isnull(f.Medical,0),Conveyance=isnull(f.Conveyance,0),
Living=isnull(f.Living,0),Ent=ISNULL(f.Ent,0),HMnt=ISNULL(f.HMnt,0),LFA=ISNULL(f.LFA,0),Utility=ISNULL(f.Utility,0),Others=IsNull(f.Others,0),
IncomeTax=0

,PFAmount= ISNULL( CASE WHEN pss.PssPfStatusID=1 THEN ROUND((f.[Basic]*@PFRatio),0) ELSE 0 END , 0 )

, GrossSalary = isnull(f.Basic,0) + isnull(f.Housing,0) + isnull(f.Medical,0) + isnull(f.Conveyance,0) + isnull(f.Living,0)+isNull(f.Ent,0)+ISNULL(f.HMnt,0)+IsNull(LFA,0)+ISNULL(f.Utility,0)
, NetPay = (isnull(f.Basic,0) + isnull(f.Housing,0) + isnull(f.Medical,0) + isnull(f.Conveyance,0) + isnull(f.Living,0)+IsNull(f.Ent,0) +ISNULL(f.HMnt,0)+ IsNull(f.LFA,0)+ISNULL(f.Utility,0)+ ISNULL(f.Others,0) - ISNULL((CASE WHEN pss.PssPfStatusID=1 THEN round((f.[Basic]*@PFRatio),0) ELSE 0 END),0))

from #FinalData f
inner join staff st on st.StaPIN=f.PIN
inner join Area a on a.AreaID=f.AreaID
inner join Designation desig on desig.DesID=f.DesigID
inner join tbl_HRD_OrgDivision div on div.ordDivisionID=f.DivID
inner join Project pr on pr.ProjectId=f.DeptID
LEFT JOIN PFStaffStatus pss on f.PIN=pss.PssPin

order by a.AreaName,f.PIN ASC

Drop table #FinalData
DROP TABLE #TMonthyPsDetailsHistory
DROP TABLE #TMonthlyPsSalary
GO

Project CSE 23-Mar-15 2:42am

-----------------23-Mar-2015-----------------

-----------------------------------------------------------
-- Type: Stored Procedure
-- Name: SP_HRD_rptServiceLengthWiseStaffDist
-----------------------------------------------------------

Alter PROCEDURE SP_HRD_rptServiceLengthWiseStaffDist
@FromYr TINYINT=0,
@FromMn TINYINT=0 ,
@ToYr TINYINT=null,
@ToMn TINYINT=null,
@BranchID NCHAR(4)=null,
@DeptID NCHAR(3)=null,

--This code is added by Azizur Rahman 12-08-2008
@DivisionID AS NVARCHAR(4)=null,
@UnitID AS NVARCHAR(4)=null
--End of code added by Azizur Rahman 12-08-2008

--With Encryption

AS
SET nocount ON
SET dateformat 'dmy'

DECLARE @Tbl TABLE(PIN NCHAR(8),Age NVARCHAR(50),Yr TINYINT,Mn TINYINT, Dy TINYINT)
INSERT INTO @Tbl
SELECT staPIN,Age=dbo.Fun_AgeCalculation(SsDesIDEDate,getdate()),null,null,null
FROM Staff
inner join StaffStatus on Staff.StaPIN=StaffStatus.SsPIN

UPDATE @Tbl SET Yr=cast(substring(Age,charindex(' Yr',Age)-2,2) AS SMALLINT)
,Mn=cast(substring(Age,charindex(' Mn',Age)-2,2) AS SMALLINT)
,Dy=cast(substring(Age,charindex(' Dy',Age)-2,2) AS SMALLINT)

UPDATE @Tbl SET Mn=isnull(Mn,0)+1 WHERE Dy>15
UPDATE @Tbl SET Yr=isnull(Yr,0)+1,Mn=0 WHERE Mn=12

IF @FromYr is not null and @FromMn is null
DELETE FROM @Tbl WHERE isnull(Yr,0) < @FromYr
ELSE IF @FromYr is null and @FromMn is not null
DELETE FROM @Tbl WHERE isnull(Yr,0)=0 and isnull(Mn,0)<@FromMn
ELSE IF @FromYr is not null and @FromMn is not null
DELETE FROM @Tbl WHERE isnull(Yr,0)*100+isnull(Mn,0)<=isnull(@FromYr,0)*100+isnull(@FromMn,0)

IF @ToYr is not null and @ToMn is null
DELETE FROM @Tbl WHERE isnull(Yr,0) > @ToYr or (isnull(Yr,0)=@ToYr and isnull(Mn,0)>0)
ELSE IF @ToYr is null and @ToMn is not null
DELETE FROM @Tbl WHERE isnull(Yr,0)>0 or isnull(Mn,0)>@ToMn
ELSE IF @ToYr is not null and @ToMn is not null
DELETE FROM @Tbl WHERE isnull(Yr,0)*100+isnull(Mn,0)>=isnull(@ToYr,0)*100+isnull(@ToMn,0)

Project CSE 23-Mar-15 2:43am

SELECT staPIN,staSex,StaffName=isnull(stafname,'')+' '+isnull(stamname,'')+' '+isnull(stalname,'')
,DesName,ssProjectID,ProjectName,ssAreaID,AreaName,StaJoinDate,ssJLevelID
,ordDivisionName,oruUnitName,
-- add by sarwar to get last tranfer date in current division(when staff is in head office) or last tranfer date in current branch(when staff is not working at head office)
DivBranchJoinDate=case when SsAreaID='0100' then dbo.GetLastEffectiveDateByDivision(Staff.StaPIN) else dbo.GetLastEffectiveDateByBranch(Staff.StaPIN) end,
ServiceLength=t.Age--right(' '+cast(Yr AS varchar),3)+' Y, '+right(' '+cast(Mn AS varchar),2)+' M',
FROM Staff
inner join StaffStatus ON ssPIN=staPIN
inner join Designation ON DesID=ssDesID
inner join Project ON ProjectID=ssProjectID
inner join Area ON AreaID=ssAreaID
inner join JobLevel ON JLevelID=ssJLevelID
inner join @Tbl t ON PIN=staPIN

--This Portion is Added by Azizur Rahman 12-08-2008
INNER JOIN tbl_HRD_OrgDivision ON ordDivisionID=SsDivisionID
INNER JOIN tbl_HRD_OrgUnit ON oruUnitID=SsUnitID
--End of the Portion is Added by Azizur Rahman 12-08-2008

WHERE ssAreaID=isnull(@BranchID,ssAreaID)
and ssProjectID=isnull(@DeptID,ssProjectID)
--This Portion is Added by Azizur Rahman 12-08-2008

and SsDivisionID = ISNULL( @DivisionID , SsDivisionID)
and SsUnitID = ISNULL( @UnitID , SsUnitID)

--End of the Portion is Added by Azizur Rahman 12-08-2008
and staPIn not in(
SELECT SsPIN FROM StaffStatus
WHERE ssStatusID <> 'c' and ssStatusID <> 'n'
)

ORDER BY ordDivisionName, DesID,StaJoinDate,staPIN ASC
GO

Project CSE 6-Apr-15 6:12am

----------------06-Apr-2015-------------
SELECT DISTINCT Staff.StaPIN, StaffJobStatus.SjsStatusID, Staff.StaJoinDate,
StaffPromotion.SpJLevelID, ss.SssLevelAtModify,
ss.SssSlgID,[dbo].[Fun_StaffMonthlyPFSalaryBranchInfo](Staff.StaPIN, getdate()),StaffStatus.SsAreaID
FROM Staff INNER JOIN StaffStatus ON Staff.StaPIN = StaffStatus.SsPIN
Inner Join StaffJobStatus ON StaffStatus.SsPIN =StaffJobStatus.SjsPIN
inner join (
SELECT [SjsPIN] ,Max([SjsEffectiveDate]) SjsEffectiveDate
FROM [dbo].[StaffJobStatus] where [SjsEffectiveDate]<@CurDate
group By [SjsPIN]
) b on b.SjsPIN=StaffJobStatus.SjsPIN And b.SjsEffectiveDate=StaffJobStatus.SjsEffectiveDate
Inner Join StaffPromotion ON StaffStatus.SsPIN =StaffPromotion.SpPIN
inner join
(

select SPPin,MAX(SpEffectiveDate) SpEffectiveDate from StaffPromotion where SpEffectiveDate <@CurDate
Group By SPPin
) pb on pb.SpPIN=StaffPromotion.SpPIN And StaffPromotion.SpEffectiveDate=pb.SpEffectiveDate

LEFT JOIN ProjectArea ON StaffStatus.SsProjectID=ProjectArea.PjaProjectID
AND StaffStatus.SsAreaID=ProjectArea.PjaAreaID
INNER JOIN StaffSalaryStructureOthers_History AS ss ON Staff.StaPIN = ss.SssPIN
LEFT OUTER JOIN #TempSalAlreadyCal ON Staff.StaPIN = #TempSalAlreadyCal.StaffPIN

WHERE (SUBSTRING(StaffStatus.SsAutoIPLA, 2, 1) = '1')AND
(Staff.StaJoinDate <=@MaxJoinDate)
AND (StaffJobStatus.SjsStatusID IN ('C', 'N'))
AND SsAreaID LIKE ''+ @BranchID +'' and
(#TempSalAlreadyCal.StaffPIN IS NULL) and
ChangeDate =
(SELECT Max(ChangeDate) FROM StaffSalaryStructureOthers_History
WHERE SssPIN=StaffJobStatus.SjsPIN and StaffSalaryStructureOthers_History.ChangeDate < = @MaxJoinDate)
--And StaffJobStatus.SjsEffectiveDate = (SELECT max(SjsEffectiveDate) FROM StaffJobStatus
--WHERE SssPIN=StaffJobStatus.SjsPIN and SjsEffectiveDate < = @MaxJoinDate)
--And StaffPromotion.SpEffectiveDate=(SELECT max(SpEffectiveDate) FROM StaffPromotion
--WHERE SssPIN=StaffPromotion.SpPIN and SpEffectiveDate < = @MaxJoinDate)
ORDER BY Staff.StaPIN

Project CSE 6-Apr-15 6:13am

SELECT DISTINCT Staff.StaPIN, StaffJobStatus.SjsStatusID, Staff.StaJoinDate,
StaffPromotion.SpJLevelID, ss.SssLevelAtModify,
ss.SssSlgID,[dbo].[Fun_StaffMonthlyPFSalaryBranchInfo](Staff.StaPIN, @GenDate),StaffStatus.SsAreaID

FROM Staff INNER JOIN StaffStatus ON Staff.StaPIN = StaffStatus.SsPIN
Inner Join StaffJobStatus ON StaffStatus.SsPIN =StaffJobStatus.SjsPIN
inner join (
SELECT [SjsPIN] ,Max([SjsEffectiveDate]) SjsEffectiveDate
FROM [dbo].[StaffJobStatus] where [SjsEffectiveDate]<@CurDate And SjsPIN=@Pin
group By [SjsPIN]
) b on b.SjsPIN=StaffJobStatus.SjsPIN And b.SjsEffectiveDate=StaffJobStatus.SjsEffectiveDate
Inner Join StaffPromotion ON StaffStatus.SsPIN =StaffPromotion.SpPIN
inner join
(
select SPPin,MAX(SpEffectiveDate) SpEffectiveDate from StaffPromotion where SpEffectiveDate <@CurDate And SPPin=@Pin
Group By SPPin
) pb on pb.SpPIN=StaffPromotion.SpPIN And StaffPromotion.SpEffectiveDate=pb.SpEffectiveDate

LEFT JOIN ProjectArea ON StaffStatus.SsProjectID=ProjectArea.PjaProjectID
AND StaffStatus.SsAreaID=ProjectArea.PjaAreaID
INNER JOIN StaffSalaryStructureOthers_History AS ss ON Staff.StaPIN = ss.SssPIN
LEFT OUTER JOIN #TempSalAlreadyCal ON Staff.StaPIN = #TempSalAlreadyCal.StaffPIN

WHERE (Staff.StaJoinDate <=@MaxJoinDate)
-- AND (StaffStatus.SsStatusID IN ('C', 'N'))
AND (#TempSalAlreadyCal.StaffPIN IS NULL) And Staff.StaPIN = @Pin and ChangeDate =
(SELECT Max(ChangeDate) FROM StaffSalaryStructureOthers_History
WHERE SssPIN=StaffJobStatus.SjsPIN and StaffSalaryStructureOthers_History.ChangeDate <= @MaxJoinDate )
--And StaffJobStatus.SjsEffectiveDate = (SELECT max(SjsEffectiveDate) FROM StaffJobStatus
--WHERE SssPIN=StaffJobStatus.SjsPIN and SjsEffectiveDate < = @MaxJoinDate )
--And StaffPromotion.SpEffectiveDate=(SELECT max(SpEffectiveDate) FROM StaffPromotion
--WHERE SssPIN=StaffPromotion.SpPIN and SpEffectiveDate < = @MaxJoinDate )
ORDER BY Staff.StaPIN

Project CSE 6-Apr-15 6:14am

CREATE TABLE #TempReleaseStaffDeduction (
Pin NCHAR(8),
EffectiveDate DATETIME,
TotalDays decimal(16,4),
SalaryDays decimal(16,4)
)

Declare @dt datetime
Declare @MaxDay decimal(16,4)
SET @dt = dbo.makeDate(@CurYear,@CurMonth,1)
Set @MaxDay =day(DateAdd(Day,-1,DATEADD(MONTH,1,@dt)))

insert into #TempReleaseStaffDeduction
Select s.Pin,min(s.SjsEffectiveDate)SjsEffectiveDate,@MaxDay,day(min(s.SjsEffectiveDate))-1 from
(
SELECT [SjsPIN] Pin ,Min([SjsEffectiveDate]) SjsEffectiveDate
FROM [dbo].[StaffJobStatus] where Year([SjsEffectiveDate])*100+month([SjsEffectiveDate]) =@CurYear*100+@CurMonth--between @CurDate And @MaxJoinDate
And SjsStatusID NOT IN('C','N')
group By [SjsPIN]
Union

SELECT [SstPIN] Pin ,MIN([SstEDate]) SjsEffectiveDate
FROM [dbo].[StaffStatusToBeEffective] where Year([SstEDate])*100+month([SstEDate]) =@CurYear*100+@CurMonth--between @CurDate And @MaxJoinDate
And SstFValue NOT IN('C','N')
group By [SstPIN]
) s
Group By s.Pin

--select * from CurrentMonPSOthers c inner join
-- #TempReleaseStaffDeduction r on r.pin=c.CmpPIN
--inner join #TempInitialCandidateStaff s on s.PinId=c.CmpPIN
--where c.CmpYear=@CurYear And c.CmpMonth=@CurMonth

update CurrentMonPSOthers set CmpBasicAmount=c.CmpBasicAmount*r.SalaryDays/r.TotalDays
, CmpPFAmount=c.CmpPFAmount*r.SalaryDays/r.TotalDays
from CurrentMonPSOthers c inner join
#TempReleaseStaffDeduction r on r.pin=c.CmpPIN
inner join #TempInitialCandidateStaff s on s.PinId=c.CmpPIN
where c.CmpYear=@CurYear And c.CmpMonth=@CurMonth

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

manak chand · Answer 1 · 2015-02-24T20:15:00

Solution 1

This script might help..
How to script all stored procedures in a database[^]
Generate Scripts for SQL Server Objects
[^]
Alternatively you can also use SQL wizard to create SP Scripts\Use the scripting wizard Right-click the db –> tasks –> Generate scripts –> go through the wizard.

Posted 24-Feb-15 20:15pm

manak chand

Updated 24-Feb-15 20:20pm

v2