Left join in a one to many relationship

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left join in a one to many relationship

Author

Topic

fixxer
Starting Member

2 Posts

Posted - 2011-02-28 : 13:50:16

I have the following tables: Patient, Bill, Employee.

I need to get a list of all patients (with or without bills), one row per patient. If the patient has a bill for today and the employee who created that bill is a nurse, then I would like to see an 'N', if that bill was created by a doctor, I would like to see a 'D'. If there are multiple bills for today, "Nurse" takes precedence.
Nothing should be shown on that field for bills created on days other than today or by an Admin employee.

Here's a script that creates the scenario:


create table Patient ([ID] int IDENTITY(1,1) PRIMARY KEY, Name varchar(50))

create table Employee ([UserID] varchar(10) PRIMARY KEY, UserRole varchar(20))	

create table Bill ([ID] int IDENTITY(1,1) PRIMARY KEY, PatientId int, 
	EmployeeId varchar(10), Charge decimal(5,2), [TimeStamp] datetime)
	
insert into Patient 
select 'Patient A' 
union
select 'Patient B' 
union
select 'Patient C' 
union
select 'Patient D' 
union
select 'Patient E'
union
select 'Patient F'

insert into Employee 
select 'Emp A', 'Doctor'
union
select 'Emp B', 'Nurse'
union
select 'Emp C', 'Admin'

insert into Bill 
select 1, 'Emp A', 150, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) --pt A billed today by doc
union
select 1, 'Emp B', 300, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) --pt A billed today by nurse
union
select 1, 'Emp B', 225, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) --pt A billed today by nurse (again)
union
select 2, 'Emp B', 125, DATEADD(dd, DATEDIFF(dd, 0, dateadd(dd, -1, getdate())), 0) --pt B billed yesterday by nurse
union
select 2, 'Emp B', 100, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) --pt B billed today by nurse
union
select 3, 'Emp A', 900, DATEADD(dd, DATEDIFF(dd, 0, dateadd(dd, -1, getdate())), 0) --pt C billed yesterday by doc
union
select 4, 'Emp A', 645, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) --pt D billed today by doc
union
select 4, 'Emp A', 105, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) --pt D billed today by doc (again)
union
select 5, 'Emp C', 275, DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) --pt E billed yesterday by admin

select p.name, b.employeeid, e.userrole, b.timestamp, 'N/D' as flag 
from patient p
left outer join Bill b
on p.id = b.patientid
left outer join Employee e
on b.employeeid = e.userid

drop table patient
drop table employee
drop table bill

The select statement shows the relationships for better understanding.

Keep in mind that the flag field should only populate for bills created today by non-admin employees.

Can someone help me out? I can't seem to be able to come up with a query that meets my requirements.

Thanks in advance.

mikgri
Starting Member

39 Posts

Posted - 2011-03-01 : 14:47:00

Try this:
if object_id('tempdb.dbo.#t') is not null drop table #t;
go
select a.patientid, b.userid as employeeid, max(a.timestamp) as timestamp
into #t
from bill a inner join employee b on a.employeeid=b.userid
where b.userrole='Nurse' --and convert(varchar, a.timestamp, 101)=convert(varchar, getdate(), 101)
group by a.patientid, b.userid

insert #t (patientid, employeeid, timestamp)
select a.patientid, b.userid, max(a.timestamp) as timestamp
from bill a inner join employee b on a.employeeid=b.userid
left join #t t on a.patientid=t.patientid
where t.patientid is null
group by a.patientid, b.userid

select p.name, b.employeeid as employeeid, e.userrole, b.timestamp, 'N/D' as flag
from patient p
left outer join #t b
on p.id = b.patientid
left outer join Employee e
on b.employeeid = e.userid
where convert(varchar, timestamp, 101)=convert(varchar, getdate(), 101)
order by p.name

fixxer
Starting Member

2 Posts

Posted - 2011-03-02 : 09:40:56

Thanks for the reply.

I was able to solve this problem with the following query:

DECLARE @today DATETIME;
SET @today = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()),0); -- strip time from current date/time.

SELECT p.name, t.employeeid, t.userrole, t.timestamp,
-- only get doctors and nurses that billed today
flag = CASE WHEN t.timestamp = @today AND t.userrole LIKE '[DN]%' THEN LEFT(t.userrole,1)
ELSE '' END
FROM @patient p
OUTER APPLY (SELECT TOP 1 *
FROM @Bill b
LEFT JOIN @Employee e
ON b.EmployeeID = e.UserID
WHERE b.PatientId = p.id
AND b.timestamp = @today
ORDER BY CASE e.UserRole WHEN 'Nurse' THEN 1
WHEN 'Doctor' THEN 2 END,
b.TimeStamp DESC) t
ORDER BY p.NAME;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources