SQL Help with Stored Procedure

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)

SQL Help with Stored Procedure

Author

Topic

sonyshah
Starting Member

7 Posts

Posted - 2008-07-02 : 18:00:04

Hi, I have this SQL server stored procedure. I have used a Temp Table so far to get the first set of results. For the second set of results, I have to make sure SystemID is NOT in the Source Table and then pull the MAX(DateTimeCreated) and data for this MAX(DateTimeCreated) row. Can someone please help me build this query further? thanks
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: <Shah,Sony>
-- Create date: <06/11/2008>
-- Description: <Payroll Salary Change Report>
-- =============================================
ALTER PROCEDURE [dbo].[Test]
-- Add the parameters for the stored procedure here
(
@KeyDateFrom DateTime,
@KeyDateTo DateTime
)
AS
-- Add the Function / variables here
BEGIN
if OBJECT_ID('tempdb..#temp_payroll_sal_chng') is not null
begin
drop table #temp_payroll_sal_chng
end

create table #temp_payroll_sal_chng
(
CompanyName varchar(255),
LocDesc varchar(255),
JobEffDate DateTime,
Key_Date DateTime,
Orglvl2 Char(6) ,
GL_Dept_Code_Desc varchar(50),
EjhEEID Char(12),
New_Sal Money,
New_Rate Decimal,
--Prev_Sal Money,
--Prev_Rate Decimal,
IsRateChange Char(1),
EmpNo Char(9),
Jobtitle varchar(255),
DateOfLastHire DateTime,
SalaryOrHourly Char(1),
NameFirst varchar(50),
NameLast varchar(50),
NameMiddle varchar(50),
OrgDesc varchar(255),
SystemID Char(12)
)
Insert Into #temp_payroll_sal_chng
select
T1.CmpCompanyName,
T8.LocDesc,
T3.EjhJobEffDate,
T3.EjhDateTimeCreated as Key_Date,
T3.EjhOrglvl2,
T3.ejhorglvl2 + ' ' + T6.OrgDesc as GL_Dept_Code_Desc,
T3.EjhEEID,
T3.EjhAnnSalary as New_Sal,
T3.EjhHourlyPayRate as New_Rate,
--0.00 as Prev_Sal,
--0.00 as Prev_Rate,
T3.EjhIsRateChange,
T4.EecEmpNo,
T4.EecJobtitle,
T4.EecDateOfLastHire,
T4.EecSalaryOrHourly,
T5.EepNameFirst,
T5.EepNameLast,
T5.EepNameMiddle,
T6.OrgDesc,
T3.EjhSystemID
from
ULTIPRO_FREE.dbo.Company T1
JOIN ULTIPRO_FREE.dbo.EmpHJob T3
ON T1.CmpCoID = T3.EjhCoID
JOIN ULTIPRO_FREE.dbo.EmpComp T4
ON T3.EjhCoID = T4.EecCoID and
T3.EjhEeID = T4.EecEEID
JOIN ULTIPRO_FREE.dbo.Location T8
ON T4.EecLocation = T8.LocCode
JOIN ULTIPRO_FREE.dbo.EmpPers T5
ON T4.EecEEID = T5.EepEEID
LEFT OUTER JOIN ULTIPRO_FREE.dbo.OrgLevel T6
ON T3.EjhOrgLvl2 = T6.OrgCode and
T6.OrgLvl = 2
JOIN
(select ejhcoid,ejheeid,max(ejhdatetimecreated) as maxdatetimecreated
from ULTIPRO_FREE.dbo.EmpHJob
where
ejhjobeffdate between @KeyDateFrom and @KeyDateTo
group by ejhcoid,ejheeid
) T7
ON T3.ejhCoid = T7.ejhCoId and
T3.ejhEeId = T7.ejhEeID and
T3.ejhDateTimeCreated = T7.maxdatetimecreated
where
T3.ejhisratechange = 'Y' and
T3.EjhEmplStatus <> 'T' and
--T1.CmpCompanyName like 'Appeal Demo%' --and
T3.ejheeid in ('5GQK0K0000K0','5U8FSP0000K0','5U8FUX0000K0','5U8FX30000K0','5U8GEV0030K0','5U8GKD0000K0','5U8GNG0030K0','5U8GO30000K0','5U8GOR0030K0','5U8GSE0000K0','5U8GXL0000K0','5U8GYT0000K0','5U8H000000K0')and
T3.ejhDateTimeCreated between @KeyDateFrom and (@KeyDateTo+1)
order by T3.EjhEEID

Select * from #temp_payroll_sal_chng
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-03 : 02:35:13

isnt this same as:=-

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105960

??

sonyshah
Starting Member

7 Posts

Posted - 2008-07-03 : 14:39:50

Hi Visakh,

It is same; I could not edit the previous entry; and I had added the System ID column. therefore, I reposted it. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources