if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwPBEmployee]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwPBEmployee]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].[vwPBEmployee]
-- Required by Payback Payroll
-- sales@payback.ie
-- 20-Mar-2008
-- 25-Mar-2008 jhonan changed join to optimise query
AS
SELECT DISTINCT TOP 100 PERCENT
e.EMPDBADD,
e.EMPSURNAME,
e.EMPNUMBER AS STAFFID,
e.EMPFORENAME,
e.EMPNINO AS PPSN,
e.EMPLINE1,
e.EMPLINE2,
e.EMPLINE3,
e.EMPLINE4,
e.EMPBIRTHDATE,
s.SHOPCODE AS COSTCENTREID,
s.SHOPDESC AS COSTCENTRENAME,
eh.EHISTEND AS DATELEFT,
eh.EHISTSTART AS DATEJOINED,
eh.EHISTDBADD
FROM dbo.EMP AS e
LEFT OUTER JOIN dbo.EHIST AS eh
ON e.EMPNUMBER = eh.EMPNUMBER
AND eh.EHISTSTART =
(SELECT MAX(EHISTSTART) AS Expr1
FROM dbo.EHIST AS eh2
WHERE (DATEDIFF(dd, GETDATE(), CONVERT(datetime, EHISTSTART)) <= 0) AND (eh.EMPNUMBER = EMPNUMBER))
LEFT OUTER JOIN dbo.PAYMISC AS pm
ON eh.EHISTDBADD = pm.EHISTDBADD
AND pm.PAYMISCSTART =
(SELECT MAX(PAYMISCSTART) AS Expr1
FROM dbo.PAYMISC AS pm2
WHERE (DATEDIFF(dd, GETDATE(), CONVERT(datetime, PAYMISCSTART)) <= 0) AND (pm.EHISTDBADD = EHISTDBADD))
JOIN dbo.SHOP AS s
ON RTRIM(pm.PAYMISCCC1CODE) + 'ROI' = s.SHOPCODE
WHERE (s.SHOPTLCC = 'IRL')
--AND e.EMPNINO in ('5803522C','7802934N','7783798I')
ORDER BY STAFFID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO