February 19, 2014 at 4:40 am
Hi
I have two tables, let's call TEST2Patient p and TEST2ReferralKPIs r and the drop/create schema and data provided below.
From these two tables I want a result set that has p.dbpatid, p.dbadddate, r.dbstafflastname, r.reftypeword
however when there isn't a matching dbpatid in r i want a row that has p.dbpatid, p.dbadddate, 'No Ref - Staff', 'No Ref' returning.
thanks,
Create TEST2Patient
BEGIN
/****** Object: Table [dbo].[TEST2Patient] Script Date: 10/31/2013 08:33:27 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEST2Patient]') AND type in (N'U'))
DROP TABLE [dbo].[TEST2Patient]
/****** Object: Table [dbo].[TEST2Patient] Script Date: 10/31/2013 08:33:27 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[TEST2Patient](
[dbPatID] [varchar] (25) NULL,
[dbAddDate] [datetime] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
END
Create TEST2ReferralKPIs
BEGIN
/****** Object: Table [dbo].[TEST2ReferralKPIs] Script Date: 10/31/2013 05:34:42 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEST2ReferralKPIs]') AND type in (N'U'))
DROP TABLE [dbo].[TEST2ReferralKPIs]
/****** Object: Table [dbo].[TEST2ReferralKPIs] Script Date: 10/31/2013 05:34:42 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[TEST2ReferralKPIs](
[dbPatID] [varchar](8) NULL,
[dbAddDate] [datetime] NOT NULL,
[dbStaffLastName] [varchar](25) NULL,
[RefTypeWord] [varchar](25) NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
END
Data for both tables
/****** Object: Table [dbo].[TEST2ReferralKPIs] Script Date: 02/19/2014 11:35:21 ******/
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003459', CAST(0x0000A28800876108 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'015996', CAST(0x0000A2880097A70C AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'015994', CAST(0x0000A2880097ACE8 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003460', CAST(0x0000A2880097B198 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003557', CAST(0x0000A2880097B3F0 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'015997', CAST(0x0000A2880097B648 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'015995', CAST(0x0000A2880097E1A4 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016000', CAST(0x0000A28800A81998 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003461', CAST(0x0000A28800A83234 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016001', CAST(0x0000A28800A83360 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'015999', CAST(0x0000A28800A851D8 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016002', CAST(0x0000A28800A851D8 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016006', CAST(0x0000A28800B89DB8 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016003', CAST(0x0000A28800B8A13C AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016005', CAST(0x0000A28800B8B078 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016004', CAST(0x0000A28800B8CA40 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016007', CAST(0x0000A28800B8CDC4 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016008', CAST(0x0000A28800C919A4 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016009', CAST(0x0000A28800EA1ADC AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003462', CAST(0x0000A28800EA3A80 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016010', CAST(0x0000A28800FAB8C4 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'30001022', CAST(0x0000A28A008757A8 AS DateTime), N'Ray', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016012', CAST(0x0000A28F00EA1050 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016013', CAST(0x0000A28F00EA1758 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016017', CAST(0x0000A28F00EA1F8C AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016015', CAST(0x0000A28F00EA2568 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016016', CAST(0x0000A28F00EA2FF4 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016019', CAST(0x0000A28F00EA2FF4 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016021', CAST(0x0000A28F00EA2FF4 AS DateTime), N'Hodgson', N'OTHER')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016018', CAST(0x0000A28F00EA35D0 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003474', CAST(0x0000A28F00EA3F30 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016014', CAST(0x0000A28F00EA450C AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'30001025', CAST(0x0000A29000EA2A18 AS DateTime), N'Ray', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'30001026', CAST(0x0000A29100874BF0 AS DateTime), N'Ray', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'30001027', CAST(0x0000A29200874AC4 AS DateTime), N'Ray', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003478', CAST(0x0000A29400C90F18 AS DateTime), N'Hodgson', N'OTHER')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016022', CAST(0x0000A2960097A964 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016024', CAST(0x0000A29600A81740 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016027', CAST(0x0000A29600A83DEC AS DateTime), N'Hodgson', N'OTHER')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016023', CAST(0x0000A29600A850AC AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016026', CAST(0x0000A29600A85304 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016025', CAST(0x0000A29600A8555C AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003481', CAST(0x0000A29600B8A5EC AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016029', CAST(0x0000A29600B8B9D8 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016031', CAST(0x0000A29600B8C914 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016028', CAST(0x0000A29600B8CDC4 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016032', CAST(0x0000A29600B8CEF0 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016030', CAST(0x0000A29600B8D5F8 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016033', CAST(0x0000A29600EA3E04 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016034', CAST(0x0000A29600EA405C AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016036', CAST(0x0000A29600FA88B8 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016035', CAST(0x0000A29600FAA604 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016037', CAST(0x0000A29700A828D4 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016038', CAST(0x0000A29800B8AA9C AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016039', CAST(0x0000A29800B8D148 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016040', CAST(0x0000A29900FA878C AS DateTime), N'Lima', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016041', CAST(0x0000A299010B06FC AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016042', CAST(0x0000A299010B0BAC AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003483', CAST(0x0000A299010B1E6C AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016043', CAST(0x0000A299010B20C4 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016044', CAST(0x0000A29A00C949B0 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'30001028', CAST(0x0000A29E00872EA4 AS DateTime), N'Ray', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003486', CAST(0x0000A2A100B89458 AS DateTime), N'Hodgson', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016046', CAST(0x0000A2A400872418 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016047', CAST(0x0000A2A400872418 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016048', CAST(0x0000A2A400872418 AS DateTime), N'Pelletti', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016045', CAST(0x0000A2A400873480 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016049', CAST(0x0000A2A4008752F8 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016050', CAST(0x0000A2A40097A5E0 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003577', CAST(0x0000A2A40097C6B0 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016051', CAST(0x0000A2A40097CB60 AS DateTime), N'Grobler', N'DOCTOR')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'90003488', CAST(0x0000A2A40097DF4C AS DateTime), N'Hodgson', N'OTHER')
INSERT [dbo].[TEST2ReferralKPIs] ([dbPatID], [dbAddDate], [dbStaffLastName], [RefTypeWord]) VALUES (N'016053', CAST(0x0000A2A400FAB1BC AS DateTime), N'Grobler', N'DOCTOR')
/****** Object: Table [dbo].[TEST2Patient] Script Date: 02/19/2014 11:35:21 ******/
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003459', CAST(0x0000A28800876108 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'015996', CAST(0x0000A2880097A70C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'015994', CAST(0x0000A2880097ACE8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003460', CAST(0x0000A2880097B198 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003557', CAST(0x0000A2880097B3F0 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'015997', CAST(0x0000A2880097B648 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'015995', CAST(0x0000A2880097E1A4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016000', CAST(0x0000A28800A81998 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003461', CAST(0x0000A28800A83234 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016001', CAST(0x0000A28800A83360 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016002', CAST(0x0000A28800A851D8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'015999', CAST(0x0000A28800A851D8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016006', CAST(0x0000A28800B89DB8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016003', CAST(0x0000A28800B8A13C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016005', CAST(0x0000A28800B8B078 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016004', CAST(0x0000A28800B8CA40 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016007', CAST(0x0000A28800B8CDC4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016008', CAST(0x0000A28800C919A4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016009', CAST(0x0000A28800EA1ADC AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003462', CAST(0x0000A28800EA3A80 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016010', CAST(0x0000A28800FAB8C4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003463', CAST(0x0000A28900A8429C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'30001021', CAST(0x0000A28900EA2310 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'30001022', CAST(0x0000A28A008757A8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'30001023', CAST(0x0000A28B00873228 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'30001024', CAST(0x0000A28B010B3CE4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016012', CAST(0x0000A28F00EA1050 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016013', CAST(0x0000A28F00EA1758 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016017', CAST(0x0000A28F00EA1F8C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016015', CAST(0x0000A28F00EA2568 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016016', CAST(0x0000A28F00EA2FF4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016021', CAST(0x0000A28F00EA2FF4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016019', CAST(0x0000A28F00EA2FF4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016018', CAST(0x0000A28F00EA35D0 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003474', CAST(0x0000A28F00EA3F30 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016014', CAST(0x0000A28F00EA450C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003477', CAST(0x0000A29000A8267C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'30001025', CAST(0x0000A29000EA2A18 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'30001026', CAST(0x0000A29100874BF0 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'30001027', CAST(0x0000A29200874AC4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003478', CAST(0x0000A29400C90F18 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016022', CAST(0x0000A2960097A964 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016024', CAST(0x0000A29600A81740 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016027', CAST(0x0000A29600A83DEC AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016023', CAST(0x0000A29600A850AC AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016026', CAST(0x0000A29600A85304 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016025', CAST(0x0000A29600A8555C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003481', CAST(0x0000A29600B8A5EC AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016029', CAST(0x0000A29600B8B9D8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016031', CAST(0x0000A29600B8C914 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016028', CAST(0x0000A29600B8CDC4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016032', CAST(0x0000A29600B8CEF0 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016030', CAST(0x0000A29600B8D5F8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016033', CAST(0x0000A29600EA3E04 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016034', CAST(0x0000A29600EA405C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016036', CAST(0x0000A29600FA88B8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016035', CAST(0x0000A29600FAA604 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016037', CAST(0x0000A29700A828D4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016038', CAST(0x0000A29800B8AA9C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016039', CAST(0x0000A29800B8D148 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016040', CAST(0x0000A29900FA878C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016041', CAST(0x0000A299010B06FC AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016042', CAST(0x0000A299010B0BAC AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003483', CAST(0x0000A299010B1E6C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016043', CAST(0x0000A299010B20C4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003485', CAST(0x0000A29A00B89EE4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003484', CAST(0x0000A29A00B8D5F8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016044', CAST(0x0000A29A00C949B0 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'30001028', CAST(0x0000A29E00872EA4 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003486', CAST(0x0000A2A100B89458 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016046', CAST(0x0000A2A400872418 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016047', CAST(0x0000A2A400872418 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016048', CAST(0x0000A2A400872418 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016045', CAST(0x0000A2A400873480 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003487', CAST(0x0000A2A400874614 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016049', CAST(0x0000A2A4008752F8 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016050', CAST(0x0000A2A40097A5E0 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003577', CAST(0x0000A2A40097C6B0 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016051', CAST(0x0000A2A40097CB60 AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'90003488', CAST(0x0000A2A40097DF4C AS DateTime))
INSERT [dbo].[TEST2Patient] ([dbPatID], [dbAddDate]) VALUES (N'016053', CAST(0x0000A2A400FAB1BC AS DateTime))
February 19, 2014 at 4:48 am
Left outer join and a couple of isnull should do the trick unless there is a better way?
SELECT p.dbpatid ,
p.dbadddate ,
ISNULL(r.dbstafflastname, 'No Ref - Staff') AS StaffLastName ,
ISNULL(r.reftypeword, 'No Ref') AS RefType
FROM @TEST2Patient p
LEFT OUTER JOIN @TEST2ReferralKPIs r ON p.dbpatid = r.dbpatid
February 19, 2014 at 1:40 pm
cheers!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply