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