Can I use table s in a where statement to get the expected result set?

Question

Can I use table s in a where statement to get the expected result set?

mattech06

SSCrazy

Points: 2484
More actions
February 21, 2014 at 1:50 am

#304617

Hi I have 3 tables
table TEST2patient p
table TEST2referralkpis r
table TEST2refstaff s
schema's and data included below.
and I have this sql
SELECT p.dbPatID,
p.dbAddDate,
ISNULL(r.dbStaffLastName, 'No Ref - Staff') AS dbStaffLastName,
ISNULL(r.RefTypeWord, 'No Ref') AS RefTypeWord
FROM TEST2RefStaff s, TEST2Patient p
LEFT OUTER JOIN TEST2ReferralKPIs r ON p.dbPatID = r.dbPatID
Where (p.dbAddDate >= '2013-12-01')
AND (p.dbAddDate <= '2013-12-31 23:59')
AND (s.dbStaffLastName IN ('Swanepoel','Patient','Pelletti','Ray','Qureshi','Grobler','Hedborg','De Kock','Lima','Check In','Hodgson','No Ref - Staff'))
AND (r.RefTypeWord IN ('') OR '' = '')
ORDER BY p.dbAddDate
when I run it (using the user test selections for date and dbstafflastname) I expect 81 rows, but I actually get 972 rows. This is because table s has 12 rows (12*81 = 972).
So when I run it without the table s included and without dbstafflastname in the where statement I get 81 rows.
how do include table s & dbstafflastname in the where clause and get 81 rows?? or a better alternative, thanks
thanks,
table p TEST2Patients:
/****** Object: Table [dbo].[TEST2Patient] Script Date: 02/21/2014 08:43:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST2Patient](
[dbPatID] [varchar](25) NULL,
[dbAddDate] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
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))
table r TEST2ReferralKPIs
/****** Object: Table [dbo].[TEST2ReferralKPIs] Script Date: 02/21/2014 08:44:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST2ReferralKPIs](
[dbPatID] [varchar](8) NULL,
[dbAddDate] [datetime] NOT NULL,
[dbStaffLastName] [varchar](25) NULL,
[RefTypeWord] [varchar](25) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
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')
table s TEST2RefStaff:
/****** Object: Table [dbo].[TEST2RefStaff] Script Date: 02/21/2014 08:14:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEST2RefStaff](
[ID] [int] IDENTITY(1,1) NOT NULL,
[dbStaffLastName] [varchar](25) NULL,
CONSTRAINT [PK_CI_TEST2RefStaff] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[TEST2RefStaff] ON
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (1, N'Swanepoel')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (2, N'Patient')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (3, N'Pelletti')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (4, N'Ray')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (5, N'Qureshi')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (6, N'Grobler')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (7, N'Hedborg')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (8, N'De Kock')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (9, N'Lima')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (10, N'Check In')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (11, N'Hodgson')
INSERT [dbo].[TEST2RefStaff] ([ID], [dbStaffLastName]) VALUES (12, N'No Ref - Staff')
SET IDENTITY_INSERT [dbo].[TEST2RefStaff] OFF

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Your query employs a scary mix of old-style and new-style joins which can be very confusing. There's some evidence for this - you've omitted the join between two of your tables:

FROM TEST2RefStaff s, TEST2Patient p

LEFT OUTER JOIN TEST2ReferralKPIs r ON p.dbPatID = r.dbPatID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 2

mattech06

SSCrazy

Points: 2484

February 21, 2014 at 2:28 am

#1691013

good point, but how do I join them?

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 3

SELECT distinct p.dbPatID,

p.dbAddDate,

ISNULL(r.dbStaffLastName, 'No Ref - Staff') AS dbStaffLastName,

ISNULL(r.RefTypeWord, 'No Ref') AS RefTypeWord

FROM TEST2RefStaff s, TEST2Patient p

LEFT OUTER JOIN TEST2ReferralKPIs r ON p.dbPatID = r.dbPatID

Where (p.dbAddDate >= '2013-12-01')

AND (p.dbAddDate <= '2013-12-31 23:59')

AND (s.dbStaffLastName IN ('Swanepoel','Patient','Pelletti','Ray','Qureshi','Grobler','Hedborg','De Kock','Lima','Check In','Hodgson','No Ref - Staff'))

AND (r.RefTypeWord IN ('') OR '' = '')

AND (r.dbStaffLastName IS NULL OR (r.dbStaffLastName IS NOT NULL AND r.dbStaffLastName = s.dbStaffLastName))

ORDER BY p.dbAddDate

Regards,
Mitesh OSwal
+918698619998

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

mattech06 (2/21/2014)
good point, but how do I join them?

How can you possibly code against these tables without knowing what the relationships are between them?

1. Ask your DBA

2. Study the ERD

If you are still unsure how to write the query after you've done this, then post back.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mattech06 SSCrazy Points: 2484 More actions · Answer 5

Hey that seems to work on my test data Mitesh (I'll try it on the live stuff in a mo).

One question tho, why did you add?

AND (r.dbStaffLastName IS NULL OR (r.dbStaffLastName IS NOT NULL AND r.dbStaffLastName = s.dbStaffLastName))

at least on the test data it brings back the correct result set with that line commented out?

thanks,

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

mattech06 (2/21/2014)
Hey that seems to work on my test data Mitesh (I'll try it on the live stuff in a mo).
One question tho, why did you add?
AND (r.dbStaffLastName IS NULL OR (r.dbStaffLastName IS NOT NULL AND r.dbStaffLastName = s.dbStaffLastName))
at least on the test data it brings back the correct result set with that line commented out?
thanks,

Don't put your trust in a query which works by accident rather than by design. You will be unable to anticipate failure caused by data changes, and you won't be able to fix it when they occur.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden