USE [keyhouse]
GO

/****** Object:  View [dbo].[OpenClosedMatters]    Script Date: 03/04/2014 10:39:19 ******/
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[OpenClosedMatters]'))
DROP VIEW [dbo].[OpenClosedMatters]
GO

USE [keyhouse]
GO

/****** Object:  View [dbo].[OpenClosedMatters]    Script Date: 03/04/2014 10:39:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE VIEW [dbo].[OpenClosedMatters]
AS

SELECT     TOP (100) PERCENT 
LTRIM(ISNULL(dbo.matters.Code, '') + ISNULL(dbo.Closed.CODE, '')) AS Code,
LTRIM(ISNULL(dbo.matters.ClientCode, '')      + ISNULL(dbo.Closed.CLIENTCODE, '')) AS ClientCode,
(SELECT TOP 1 name FROM contacts 
where CODE = (LTRIM(ISNULL(dbo.matters.ClientCode,'')+ISNULL(dbo.Closed.CLIENTCODE,'')))
ORDER BY code ) AS ClientName,
LTRIM(ISNULL(dbo.matters.Matter, '') + ISNULL(dbo.Closed.MATTER, '')) AS Matter, 
LTRIM(ISNULL(dbo.matters.Description, '') + ISNULL(dbo.Closed.DESCRIPTION, '')) AS Description,
LTRIM(ISNULL(dbo.matters.Dept, '') + ISNULL(dbo.Closed.DEPT, ''))  AS Dept, 
LTRIM(ISNULL(dbo.matters.FECode, '') + ISNULL(dbo.Closed.FECODE, '')) AS Fecode,
LTRIM(ISNULL(dbo.matters.Started, '') + ISNULL(dbo.Closed.STARTED,'')) AS Started, 
ISNULL(dbo.matters.Started, '') + ISNULL(dbo.Closed.STARTED ,'') AS StartDate, 
LTRIM(ISNULL(dbo.matters.WType, '') + ISNULL(dbo.Closed.WTYPE, '')) AS Wtype, 
LTRIM(ISNULL(dbo.matters.User1, '') + ISNULL(dbo.Closed.USER1, '')) AS User1, 
LTRIM(ISNULL(dbo.matters.User2, '') + ISNULL(dbo.Closed.USER2, '')) AS User2,
LTRIM(ISNULL(dbo.matters.User3, '') + ISNULL(dbo.Closed.USER3, '')) AS User3,
LTRIM(ISNULL(dbo.matters.OldRef, '') + ISNULL(dbo.Closed.OLDREF, '')) AS OldRef, 
LTRIM(ISNULL(dbo.matters.FileNum, '') + ISNULL(dbo.Closed.FILENUM,  '')) AS FileNum,
dbo.matters.Code AS OpenCode, 
dbo.Closed.CODE AS ClosedCode,
(CASE WHEN dbo.matters.Code IS NULL THEN 'Closed'
ELSE (CASE WHEN dbo.closed.Code IS NULL THEN 'Open'
ELSE 'Both' END)END) As OpenClosed 
FROM         dbo.matters FULL OUTER JOIN
                      dbo.Closed ON dbo.matters.Code = dbo.Closed.CODE
ORDER BY LTRIM(ISNULL(dbo.matters.Code, '') + ISNULL(dbo.Closed.CODE, ''))



GO