Temp Table - SQL Server Forums

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)

Temp Table

Author

Topic

sonyshah
Starting Member

7 Posts

Posted - 2008-07-02 : 19:05:49

Hi, Please see the Code below. I am joining EEID from Temp Table to EEID from source table [EmpHjob]. I need the query to return rows where EEID from Temp table should be excluded from Source Table and it should look at the MAX(DateTimecreated) and give me the data for the rows that meets these 2 conditions. Any help will be appreciated. Thanks

Select
T1.SystemID,
T2.EjhEEID
from #temp_payroll_sal_chng T1 Join dbo.EmpHJob T2
ON T1.EEID = T2.EjhEEID
Where
T1.SystemID <> T2.EjhSystemID /*and
T2.ejhdatetimecreated in
( select top 1 T3.ejhdatetimecreated
from dbo.emphjob T3
where t3.ejheeid = t2.ejheeid
order by T2.ejhdatetimecreated desc)*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

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

[code]SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY T1.EjhEEID ORDER BY T1.ejhdatetimecreated DESC) AS RowNo,
T1.*
FROM dbo.EmpHJob T1
LEFT JOIN #temp_payroll_sal_chng T2
ON T2.EEID = T1.EjhEEID
WHERE T2.EEID IS NULL)t
WHERE t.RowNo=1[/code]

sonyshah
Starting Member

7 Posts

Posted - 2008-07-03 : 14:59:07

Thanks Vishak. That did not work. Here is what i have so far. In the Temp Table, I am getting the most current record with the current Salary using the Max(datetimecreated). SystemID is a Primary Key on EmphJob table. Now I have to get the Previous salary record. So I was trying to exclude the SystemID for the given EEID from the second Select statement, and then take the MAX(Datetimecreated) for the remaining records for that EEID; so it can give me the previous salary. Then, take this second set of data and update the temp table with 2 new columns for PRev Sal and Prev Rate. Is this possible? I really appreciate your help! 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),
EEID Char(12),
New_Sal Money,
New_Rate Money,
--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

SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY T1.EjhEEID ORDER BY T1.ejhdatetimecreated DESC) AS RowNo,
T1.EjhSystemID,
T1.EjhEEID,
T1.EjhAnnSalary as Prev_Sal,
T1.EjhHourlyPayRate as Prev_Rate,
T1.ejhdatetimecreated,
T2.Key_Date,
T1.ejhIsRateChange
FROM dbo.EmpHJob T1
LEFT JOIN #temp_payroll_sal_chng T2
ON T2.EEID = T1.EjhEEID
WHERE T2.EEID IS NULL)t
WHERE t.RowNo=1

--Order by T3.EjhEEID
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources