Any one please tell me how to fix the view..it's producing lots of errors -
Code Snippet
ALTER VIEW [dbo].[vw_Issues_Deducts]
AS
SELECT E.EmpNo,
IF E.ResignDate IS NOT NULL
MonthWorked = DATEDIFF( M, E.JoinDate, E.ResignDate )
ELSE
MonthWorked = DATEDIFF( M, E.JoinDate, GETDATE() )
FROM Employees E
Code Snippet
Msg 156, Level 15, State 1, Procedure vw_Issues_Deducts, Line 10
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure vw_Issues_Deducts, Line 11
Incorrect syntax near 'MonthWorked'.
Regards
Kapalic
You have to use the CASE WHEN...
|||
Code Snippet
ALTER VIEW [dbo].[vw_Issues_Deducts]
AS
SELECT
E.EmpNo,
MonthWorked =
Case When E.ResignDate IS NOT NULL
DATEDIFF( M, E.JoinDate, E.ResignDate )
ELSE
MonthWorked = DATEDIFF( M, E.JoinDate, GETDATE() )
END
FROM
Employees E
Still geting error -
Code Snippet
Msg 102, Level 15, State 1, Procedure vw_Issues_Deducts, Line 16
Incorrect syntax near 'DATEDIFF'.
Regards
Kapalic
|||Fixed it!
Code Snippet
ALTER VIEW [dbo].[vw_Issues_Deducts]
AS
SELECT E.EmpNo,
MonthWorked = Case When E.ResignDate IS NOT NULL THEN
DATEDIFF( M, E.JoinDate, E.ResignDate )
ELSE
DATEDIFF( M, E.JoinDate, GETDATE() )
END
FROM Employees E
Regards
Kapalic
