How do I get multiple rows returned? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How do I get multiple rows returned?

Author

Topic

sonyshah
Starting Member

7 Posts

Posted - 2008-10-13 : 15:30:07

This code returns the Last Salary Change for an associate in any given date range [The Last / New Salary and it's PRevious Salary in 1 row]. Now the requirements have changed! The client wants to see ALL Salary changes in the given date range. Each Salary change should return a Row with New Salary and Previous Salary. I am not sure how to do this. Can someone

please 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].[Payroll_Salary_Change]
-- Add the parameters for the stored procedure here
(
@BeginningEntryDate DateTime,
@EndingEntryDate DateTime,
@Login Varchar (250),
@Company Varchar (3500),
@Location Varchar (3500)
)
AS
-- Add the Function / variables here
BEGIN
Select
T1.CmpCompanyName,
T1.CmpCOID,
T8.LocDesc,
T8.LocCode,
T3.EjhJobEffDate,
T3.EjhDateTimeCreated as Key_Date,
T3.EjhOrglvl2 as GL_Dept_Code,
T6.OrgDesc as GL_Dept_Desc,
T3.EjhEEID,
T3.EjhAnnSalary as New_Sal, -- taken as meaning 'current'
T3.EjhHourlyPayRate as New_Rate,
T3.EjhIsRateChange,
T3.EjhReason,
T10.JchCode,
T10.JchDesc,
T4.EecEmpNo,
T4.EecEmplStatus,
T4.EecEEType,
T4.EecFullTimeOrPartTime,
T4.EecDateOfOriginalHire,
T4.EecJobtitle,
T4.EecDateOfLastHire,
T4.EecSalaryOrHourly,
T5.EepNameFirst,
T5.EepNameLast,
T5.EepNameMiddle,
( select top 1 T9.ejhannsalary
from dbo.emphjob T9
where t9.ejheeid = t3.ejheeid and
T9.ejhdatetimecreated < T3.ejhdatetimecreated
order by T9.ejhdatetimecreated desc) as previous_salary,

( select top 1 T9.EjhHourlyPayRate
from dbo.emphjob T9
where t9.ejheeid = t3.ejheeid and
T9.ejhdatetimecreated < T3.ejhdatetimecreated
order by T9.ejhdatetimecreated desc) as previous_rate
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
LEFT OUTER JOIN ULTIPRO_FREE.dbo.JobChRsn T10 ON T3.EjhReason = T10.JchCode
JOIN ULTIPRO_FREE.dbo.FREEDOM_SECLIST Sec ON T1.cmpcoid = sec.Company and
T8.loccode = sec.Location and
T6.orgcode = sec.OrgLevel and
sec.logon = @Login
where
T4.EecEmpNo NOT LIKE 'T%' and
T1.cmpcoid IN (Select * from ULTIPRO_FREE.dbo.Freedom_Company_fnSplit(@Company, '^'))and
T8.LocCode IN (Select * from ULTIPRO_FREE.dbo.Freedom_Company_fnSplit(@Location, '^'))and
T3.ejhdatetimecreated =
( select max(T9.ejhdatetimecreated)
from dbo.emphjob T9
where t9.ejheeid = t3.ejheeid and
T9.ejhisratechange = 'Y' and
T9.EjhEmplStatus <> 'T'
and T9.ejhDateTimeCreated between @BeginningEntryDate and (@EndingEntryDate+1)
)
--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 T1.CmpCompanyName,
T8.LocDesc,T3.EjhEEID

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources