November 23, 2010 at 11:19 pm
Hello,
My task is to show what position someone is in (eg office, job level (ie consultant, manager, snr manager etc), department etc) someone was in at the point that they took their various types of leave between two dates.
One table contains the leave details ie emp id, leave start date, leave end date, leave type, etc.
The other table contains the job details ie emp id, position start date, position end date, office, department, etc.
I have created a table valued function listing the leave details within a period ie:
SELECT lve.DET_NUMBERA AS [Emp Number], lve.LVE_STARTC AS [Leave Start], lve.LVE_ENDD AS [Leave End], lve.LVE_HOUR_TKNN AS [Hrs taken],lve.LVE_DAY_TAKEN AS [Days taken], lve_desc.TAB_DESCRIPTA AS [Leave Desc], lve_desc.TAB_CODEA AS [Leave Code] (*additional position information as at leave start date)
FROM Production.dbo.CHTAB lve_desc INNER JOIN Production.dbo.EMLVE lve ON lve_desc.TAB_CODEA = lve.LVE_TYPE_CDA
WHERE (lve_desc.TAB_NAMEA = 'LVETP') AND (lve.LVE_STARTC <= @EndDate) AND (lve.LVE_ENDD >= @StartDate))
I have also created another table valued function to get the position details as at a point in time (I have just included the select statement from the tvf below). I want these details in the previous select list but can't seem to return multiple values from the below table valued function:
SELECT office.GNA_ORG_NAMEA AS 'Office Desc', dept.GNA_ORG_NAMEA AS 'Dept Desc'
FROM Production.dbo.EMDET AS det INNER JOIN
Production.dbo.EMPOS AS pos ON det.DET_NUMBERA = pos.DET_NUMBERA LEFT OUTER JOIN
Production.dbo.ORGNA AS office ON pos.POS_L2_CDA = office.GNA_ORG_CODEA INNER JOIN
Production.dbo.ORGNA AS dept ON pos.POS_L3_CDA = dept.GNA_ORG_CODEA INNER JOIN
WHERE (pos.POS_STARTC =
(SELECT max(POS_STARTC)
FROM Production.dbo.EMPOS AS posmax
WHERE (pos.DET_NUMBERA = DET_NUMBERA) AND (POS_STARTC <= @EffDate) ))
Should I be somehow combining these select statements???
Let me know if you need further info.
Thanks.
November 24, 2010 at 2:09 am
Something like this
select * from dbo.function1
Cross Apply dbo.function2
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
November 24, 2010 at 7:35 pm
Thank you so much Sachin, worked a treat!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply