Datediff problem

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