February 28, 2011 at 12:14 pm
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.
February 28, 2011 at 1:31 pm
Can you verify what version of SQL you're using?
This will work with SQL 2005+ (I changed the tables to be table variables...). If you're on 7/2000, we'll have to change that OUTER APPLY stuff...
DECLARE @Patient TABLE ([ID] int IDENTITY(1,1) PRIMARY KEY, Name varchar(50))
DECLARE @Employee TABLE ([UserID] varchar(10) PRIMARY KEY, UserRole varchar(20))
DECLARE @Bill TABLE ([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 ALL
select 'Patient B' UNION ALL
select 'Patient C' UNION ALL
select 'Patient D' UNION ALL
select 'Patient E' UNION ALL
select 'Patient F';
insert into @Employee
select 'Emp A', 'Doctor' UNION ALL
select 'Emp B', 'Nurse' UNION ALL
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
-- need a different employee table with an OrderBy column for controlling priority
DECLARE @Employee2 TABLE ([UserID] varchar(10) PRIMARY KEY, UserRole varchar(20), OrderBy TINYINT);
INSERT INTO @Employee2
SELECT UserID, UserRole, CASE WHEN UserRole = 'NURSE' THEN 1
WHEN UserRole = 'DOCTOR' THEN 2
WHEN UserRole = 'ADMIN' THEN 3 END
FROM @Employee;
-- get today's date only
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 @Employee2 e
ON b.EmployeeID = e.UserID
WHERE b.PatientId = p.id
ORDER BY e.OrderBy, b.TimeStamp DESC) t
ORDER BY p.NAME;
This returns:
name employeeid userrole timestamp flag
-------------------------------------------------- ---------- -------------------- ----------------------- ----
Patient A Emp B Nurse 2011-02-28 00:00:00.000 N
Patient B Emp B Nurse 2011-02-28 00:00:00.000 N
Patient C Emp A Doctor 2011-02-27 00:00:00.000
Patient D Emp A Doctor 2011-02-28 00:00:00.000 D
Patient E Emp C Admin 2011-02-28 00:00:00.000
Patient F NULL NULL NULL
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 28, 2011 at 1:54 pm
WayneS:
I'm using SQL Server 2008, so the OUTER APPLY works fine. The output you posted is exactly what I'm looking for, however I can't add new tables (like Employee2). Is it possible to come up with the same results without introducing additional tables?
Thank you,
Mariano
February 28, 2011 at 2:01 pm
Just add the case statement used for building the OrderBy column into the ORDER BY clause itself:
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
ORDER BY CASE WHEN e.UserRole = 'NURSE' THEN 1
WHEN e.UserRole = 'DOCTOR' THEN 2
WHEN e.UserRole = 'ADMIN' THEN 3 END, b.TimeStamp DESC) t
ORDER BY p.NAME;
Edit: corrected typo of "ADMIN"
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 28, 2011 at 2:04 pm
Looks great - Thank you VERY much for the help!
February 28, 2011 at 2:12 pm
marianoa (2/28/2011)
Looks great - Thank you VERY much for the help!
No problem. THANK YOU for posting the DDL/DML code for producing your environment - you went a long way in helping us help you with this. (The only thing you missed was providing your expected output based on the sample data provided.)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 28, 2011 at 3:17 pm
You should make the timestamp the first item of your ORDER BY clause because as it is now it will return the most recent Nurse billing record first and not the most recent record. So for Patient D, if the Nurse submitted billing yesterday it would show that instead of the Doctor submitting billing today.
February 28, 2011 at 3:30 pm
I noticed that problem when I adjusted the query to run against production data. I moved the @today condition to the subquery and it seems to be working OK now.
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
February 28, 2011 at 4:02 pm
Good catch. Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply