August 29, 2018 at 5:05 pm
Hi Iam new to Sql can someone please help me with the derived table.I had an existing view as the starting point from which should build the query to give derived table with all the employees and column for contacts as per current query logic.Setting up of primary key and foreign keys to the employees link table. Thanks
Create view EmpInfo As
With Columns as
(
select EC.Emp_ID
,EC.EmpCode_ER_ID
,EC.EmpCode
,EC.JoiningDate_Date_ID
,EC.JoiningDate_Time_ID
,row_number() over (partition by EC. Emp_ID order by EC.JoiningDate_Date_ID Desc,
EC.JoiningDate_Time_ID Desc) As EmpRef
From Tmp.Employee AS EC
Where EC. EmpCode
In (‘abc’,’ccc’,’aaa’,’dca’)
)
Select CH. EmpID as Employee_Ref_ID
,CH. EmpCode_ET3_ID as TypeOfEmployee_EmpCode_ID
,CH. EmpCode as TypeOfEmployee_EmpCode
,C.CTV3Text as Emp_Descriptn_Text
,case when C.EmpCode = ‘abc’ then ‘A’
When C.EmpCode = ‘ccc’ then ‘B’
When C.EmpCode = ‘aaa’ then ‘C’
When C.EmpCode = ‘dca’ then ‘D’
End as Employee_DatabaseCode
,CH. JoiningDate_Date_ID
,CH.JoiningDate_Time_ID
From columns as CH
Inner join Tmp.Employee AS T
On CH. EmpCode_ET3_ID = C.ID
Where CH.EmpRef = 1;
August 29, 2018 at 5:30 pm
jazzgraham20 - Wednesday, August 29, 2018 5:05 PMHi Iam new to Sql can someone please help me with the derived table.I had an existing view as the starting point from which should build the query to give derived table with all the employees and column for contacts as per current query logic.Setting up of primary key and foreign keys to the employees link table. ThanksCreate view EmpInfo As
With Columns as
(
select EC.Emp_ID
,EC.EmpCode_ER_ID
,EC.EmpCode
,EC.JoiningDate_Date_ID
,EC.JoiningDate_Time_ID
,row_number() over (partition by EC. Emp_ID order by EC.JoiningDate_Date_ID Desc,
EC.JoiningDate_Time_ID Desc) As EmpRef
From Tmp.Employee AS ECWhere EC. EmpCode
In (‘abc’,’ccc’,’aaa’,’dca’)
)
Select CH. EmpID as Employee_Ref_ID
,CH. EmpCode_ET3_ID as TypeOfEmployee_EmpCode_ID
,CH. EmpCode as TypeOfEmployee_EmpCode
,C.CTV3Text as Emp_Descriptn_Text
,case when C.EmpCode = ‘abc’ then ‘A’
When C.EmpCode = ‘ccc’ then ‘B’
When C.EmpCode = ‘aaa’ then ‘C’
When C.EmpCode = ‘dca’ then ‘D’
End as Employee_DatabaseCode
,CH. JoiningDate_Date_ID
,CH.JoiningDate_Time_ID
From columns as CH
Inner join Tmp.Employee AS T
On CH. EmpCode_ET3_ID = C.ID
Where CH.EmpRef = 1;
Your code is complete with errors, column supposedly from the CTE that doesn't exist, incorrect table aliases. Without a little more information I don't think you even need to join back to the Employee table to accomplish what you are attempting.
August 29, 2018 at 5:36 pm
Here is a start at a rewrite but with the errors in your code I am not sure what else you may be needing.
WITH Base AS (
SELECT
[EC].[Emp_ID]
,[EC].[EmpCode_ER_ID]
,[EC].[EmpCode]
,[EC].[JoiningDate_Date_ID]
,[EC].[JoiningDate_Time_ID]
, CASE WHEN [EC].[EmpCode] = 'abc' THEN 'A'
WHEN [EC].[EmpCode] = 'ccc' THEN 'B'
WHEN [EC].[EmpCode] = 'aaa' THEN 'C'
WHEN [EC].[EmpCode] - 'dca' THEN 'D'
END AS [Employee_DatabaseCode]
,ROW_NUMBER() OVER (PARTITION BY [EC]. [Emp_ID]
ORDER BY [EC].[JoiningDate_Date_ID] DESC
,[EC].[JoiningDate_Time_ID] Desc) AS [EmpRef]
FROM
[Tmp].[Employee] AS [EC]
WHERE
[EC]. [EmpCode] In ('abc','ccc','aaa','dca')
)
SELECT
.*
FROM
[Base] AS
WHERE
.[EmpRef] = 1;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply