May 10, 2012 at 9:38 pm
Hello,
Hope someone can help me out here...
I have a calling application that needs to query a table that has duplicate records such as a patient that might have 1 appointment or multiple appointments in a particular date. so i need a query that can get me just one record from these duplicate records so i can call the patient. (that is the first part), the second part is once i get hold of the patient, i need another query that can get me all the appointments based on patient_id but in one condition, i can't have multiple rows with the same patient_id, empl_id and the date from app_datetime.
Here is the structure
RecNum Patient_ID Patient_FirstName Emp_ID Emp_FirstName App_Datetime
1 1234 John 9876 Adam 5/12/2012 9:00 am
2 1234 John 8765 Steve 5/12/2012 10:00 am
3 1234 John 9876 Adam 5/12/2012 9:00 am
4 1234 John 9876 Adam 5/13/2012 9:00 am
5 1234 John 5432 Frank 5/12/2012 2:00 pm
(you can see the attached picture called Image1.png)
as you can see from the table is that
- RecNum is a unique identifier
- Patient_ID, Employee_ID are duplicates
- The date of the Appointment_datetime is the same for some records
Here is the criteria
Get one of the records where Patient_ID is the same and the date portion of the Appointment_Datetime time is the same.
You notice that
- RecNum 1,2,3 and 5 are duplicate, so i need one of those
- RecNum 4 is unique, so i need that to show up
The results i need to from the first query to call the patient is where i want one record is
1 1234 John 9876 Adam 5/12/2012 9:00 am
4 1234 John 9876 Adam 5/13/2012 9:00 am
(you can see the attached picture called Image2.png)
and the twist here is i need to update these 2 rows (1 and 4) with let's say RecordToCall = 1
The 'second' part (as i stated above) is once i get hold of the patient, i need another query that can get me all the appointments based on patient_id but in one condition, i can't have multiple rows with the same patient_id, empl_id and the date from app_datetime.
result i need to get are
RecNum Patient_ID Patient_FirstName Emp_ID Emp_FirstName App_Datetime
1 1234 John 9876 Adam 5/12/2012 9:00 am
2 1234 John 8765 Steve 5/12/2012 10:00 am
4 1234 John 9876 Adam 5/13/2012 9:00 am
5 1234 John 5432 Frank 5/12/2012 2:00 pm
(you can see the attached picture called Image3.png)
hope someone can help me here... i have been struggling with it for a while now.
Thank you,
Labneh
May 10, 2012 at 10:00 pm
Ok,
First, it appears that you have 3 unique records
RecNum Patient_ID Patient_FirstName Emp_ID Emp_FirstName App_Datetime
1 1234 John 9876 Adam 5/12/2012 9:00 am
2 1234 John 8765 Steve 5/12/2012 10:00 am
3 1234 John 9876 Adam 5/12/2012 9:00 am
4 1234 John 9876 Adam 5/13/2012 9:00 am
5 1234 John 5432 Frank 5/12/2012 2:00 pm
Looks to me that 1,3, & 4 are dupes
2 and 5 are unique.
Assuming that is the case then you can probably do something like this:
SELECT *
FROM ( SELECT ROW_Number() OVER (PARTITION BY Patient_ID, Emp_ID, CONVERT(NVARCHAR(20), APP_DATE,100) ORDER BY RecNum) as id
, *
from Table
) as Tmp
where tmp.id = 1
The sub-query above can be altered slightly to get you the results for then 2nd if I am reading your request correctly.
Fraggle
May 10, 2012 at 10:48 pm
check it...
Set NoCount On
Create table Appointments (
RecNum int,
Patient_ID int,
Patient_FirstName varchar(25),
Emp_ID int,
Emp_FirstName varchar(25),
App_Datetime datetime,
RecordToCall bit )
Insert into Appointments (RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime) Values
(1, 1234, 'John', 9876, 'Adam', '5/12/2012 9:00 am'),
(2, 1234, 'John', 8765, 'Steve', '5/12/2012 10:00 am'),
(3, 1234, 'John', 9876, 'Adam', '5/12/2012 9:00 am'),
(4, 1234, 'John', 9876, 'Adam', '5/13/2012 9:00 am'),
(5, 1234, 'John', 5432, 'Frank', '5/12/2012 2:00 pm')
Select RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime from Appointments with (NOLOCK)
--and the twist here is i need to update these 2 rows (1 and 4) with let's say RecordToCall = 1
Update
App
Set
App.RecordToCall = 1
From
Appointments App,
(
Select
RecNum,
RANK() Over ( Partition By (CONVERT(varchar(12), App_DateTime, 106)) Order By RecNum) As Rank
From
Appointments with (NOLOCK)
) tmp
Where
tmp.Rank = 1 And
App.RecNum = tmp.RecNum
--The results i need to from the first query to call the patient is where i want one record is
Select RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime from Appointments with (NOLOCK) Where RecordToCall = 1
--result i need to get
Select
tmp.RecNum, tmp.Patient_ID, tmp.Patient_FirstName, tmp.Emp_ID, tmp.Emp_FirstName, tmp.App_Datetime
From (
Select
App.RecNum, App.Patient_ID, App.Patient_FirstName, App.Emp_ID, App.Emp_FirstName, App.App_Datetime,
RANK() Over ( Partition By App.Patient_ID, App.Emp_ID, App.App_Datetime Order By App.RecNum) As Rank
From
Appointments App with (NOLOCK)
Where
Exists ( Select Null From Appointments tmp with (NOLOCK) Where tmp.RecordToCall = 1 And App.Patient_ID = tmp.Patient_ID) ) tmp
Where
tmp.Rank = 1
Order By
tmp.RecNum
May 10, 2012 at 10:58 pm
Small change in update
--and the twist here is i need to update these 2 rows (1 and 4) with let's say RecordToCall = 1
Update
App
Set
App.RecordToCall = 1
From
Appointments App,
(
Select
RecNum,
RANK() Over ( Partition By Patient_ID, Emp_ID, (CONVERT(varchar(12), App_DateTime, 106)) Order By RecNum) As Rank
From
Appointments with (NOLOCK)
) tmp
Where
tmp.Rank = 1 And
App.RecNum = tmp.RecNum
May 11, 2012 at 1:56 am
These are the queries based on your requirements:
--Creating Table
Create Table Ex
(RecNum Int,
Patient_ID int,
Patient_FirstName varchar(10),
Emp_ID int,
Emp_FirstName varchar(10),
App_Datetime DateTime )
--Inserting Sample Data
Insert Into Ex
Select 1, 1234, 'John', 9876, 'Adam', '5/12/2012 9:00 am'
Union ALL
Select 2, 1234, 'John', 8765, 'Steve', '5/12/2012 10:00 am'
Union ALL
Select 3, 1234, 'John', 9876, 'Adam', '5/12/2012 9:00 am'
Union ALL
Select 4, 1234, 'John', 9876, 'Adam', '5/13/2012 9:00 am'
Union ALL
Select 5, 1234, 'John', 5432, 'Frank', '5/12/2012 2:00 pm'
--First Requirement
Select a.RecNum, a.Patient_Id, a.Patient_FirstName, a.Emp_ID, a.Emp_FirstName, a.App_Datetime From
(Select *, ROW_NUMBER() Over (Partition By Patient_Id,
Convert(Date,App_DateTime) Order By RecNum) As rownum From Ex) As a
Where a.rownum = 1
--Second Requirement
Select a.RecNum, a.Patient_Id, a.Patient_FirstName, a.Emp_ID, a.Emp_FirstName, a.App_Datetime From
(Select *, ROW_NUMBER() Over (Partition By Patient_Id, emp_ID,
Convert(Date,App_DateTime) Order By RecNum) As rownum From Ex) As a
Where a.rownum = 1
Order By RecNum
Your Table doesn't have a field by the name "RecordToCall", where is it that you are updating this field??....In another table or you want to add a column to the same table?
May 11, 2012 at 3:00 am
If I'm understanding, I think this will handle all 3 of your requirements. At least, the results match the 3 pictures you originally posted (with the possible exception of sort order).
DECLARE @Appointments TABLE (
RecNum int,
Patient_ID int,
Patient_FirstName varchar(25),
Emp_ID int,
Emp_FirstName varchar(25),
App_Datetime datetime,
RecordToCall bit )
Insert into @Appointments (RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime)
SELECT 1, 1234, 'John', 9876, 'Adam', '5/12/2012 9:00 am'
UNION ALL SELECT 2, 1234, 'John', 8765, 'Steve', '5/12/2012 10:00 am'
UNION ALL SELECT 3, 1234, 'John', 9876, 'Adam', '5/12/2012 9:00 am'
UNION ALL SELECT 4, 1234, 'John', 9876, 'Adam', '5/13/2012 9:00 am'
UNION ALL SELECT 5, 1234, 'John', 5432, 'Frank', '5/12/2012 2:00 pm'
SELECT * FROM @Appointments
-- Requirement #1
;WITH UApp AS (
SELECT RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime, RecordToCall
,ROW_NUMBER() OVER (
PARTITION BY Patient_ID, Emp_ID, App_Datetime
ORDER BY App_Datetime, RecNum) As r
FROM @Appointments
)
SELECT RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime
FROM UApp
WHERE Emp_ID = 9876 and r = 1
-- Requirement #2
;WITH UApp AS (
SELECT RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime, RecordToCall
,ROW_NUMBER() OVER (
PARTITION BY Patient_ID, Emp_ID, App_Datetime
ORDER BY App_Datetime, RecNum) As r
FROM @Appointments
)
UPDATE a
SET RecordToCall = 1
FROM UApp a
WHERE Emp_ID = 9876 and r = 1
SELECT * FROM @Appointments
-- Requirement #3
;WITH UApp AS (
SELECT RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime, RecordToCall
,ROW_NUMBER() OVER (
PARTITION BY Patient_ID, Emp_ID, App_Datetime
ORDER BY App_Datetime, RecNum) As r
FROM @Appointments
)
SELECT RecNum, Patient_ID, Patient_FirstName, Emp_ID, Emp_FirstName, App_Datetime
FROM UApp
WHERE r = 1
Note since the Uapp CTE is the same in all 3 queries, it might be better to simply use a VIEW.
Vinus: What's up with that CONVERT on App_Datetime anyway? I don't think its needed.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 11, 2012 at 3:16 am
Its continuation to my previous reply 🙂
May 11, 2012 at 3:42 am
preetham gowda (5/11/2012)
Its continuation to my previous reply 🙂
Not actually as I'm using ROW_NUMBER() and not RANK().
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 11, 2012 at 5:42 am
SSC Rookie,
Thanks a lot for your reply.
i tried to use your update statement but i don't think it is doing what is supposed to do.
here what is happening
RecNum Patient_ID Patient_FirstName Emp_ID Emp_FirstName App_Datetime
1 1234 John 9876 Adam 5/12/2012 9:00 am
2 1234 John 8765 Steve 5/12/2012 10:00 am
3 1234 John 9876 Adam 5/12/2012 9:00 am
4 1234 John 9876 Adam 5/13/2012 9:00 am
5 1234 John 5432 Frank 5/12/2012 2:00 pm
the update query is updating the field RecordToCall for 1, 2, 4 and 5.
notice that 4 has a totally different date.
i think the update statement should update one of the duplicate records (either 1, 2, 3 or 5 because the have the same date) and update 4 because it has a different date.
The reason behind all this is, this patient has multiple appointments in one day, i want to call him once for that day. so based on RecordToCall, i will saying get me all the records where RecordToCall = 1 and date of today's date is = to the date of the app_datetime, in this case it will be either (1, 2, 3, 5) for that day and tomorrow i will pick up #4.
Now, once the patient picks up, the second query should take place where i will say to the patient
you, john have an appointment with employee ID 9876 at 5/12/2012 at 9am
and you have an appointment with employee ID 8765 at 5/12/2012 at 10am
and you have an appointment with employee ID 5432 at 5/12/2012 at 2pm
*** notice that i didn't say #4 because it is a duplicate with #1 ***
thank you soo much for helping out.
Labneh
May 11, 2012 at 6:01 am
Try this:
--Creating Table
Create Table Ex
(RecNum Int,
Patient_ID int,
Patient_FirstName varchar(10),
Emp_ID int,
Emp_FirstName varchar(10),
App_Datetime DateTime )
--Inserting Sample Data
Insert Into Ex
Select 1, 1234, 'John', 9876, 'Adam', '5/12/2012 9:00 am'
Union ALL
Select 2, 1234, 'John', 8765, 'Steve', '5/12/2012 10:00 am'
Union ALL
Select 3, 1234, 'John', 9876, 'Adam', '5/12/2012 9:00 am'
Union ALL
Select 4, 1234, 'John', 9876, 'Adam', '5/13/2012 9:00 am'
Union ALL
Select 5, 1234, 'John', 5432, 'Frank', '5/12/2012 2:00 pm'
--First Requirement
Select a.RecNum, a.Patient_Id, a.Patient_FirstName, a.Emp_ID, a.Emp_FirstName, a.App_Datetime From
(Select *, ROW_NUMBER() Over (Partition By Patient_Id,
Convert(Date,App_DateTime) Order By RecNum) As rownum From Ex) As a
Where a.rownum = 1
--Second Requirement
Update <TableName>
Set RecordToCall = 1
Where RecNum IN
Select a.RecNum From
(Select RecNum , ROW_NUMBER() Over (Partition By Patient_Id,
Convert(Date,App_DateTime) Order By RecNum) As rownum From Ex) As a
Where a.rownum = 1
--Third Requirement
Select a.RecNum, a.Patient_Id, a.Patient_FirstName, a.Emp_ID, a.Emp_FirstName, a.App_Datetime From
(Select *, ROW_NUMBER() Over (Partition By Patient_Id, emp_ID,
Convert(Date,App_DateTime) Order By RecNum) As rownum From Ex) As a
Where a.rownum = 1
Order By RecNum
May 11, 2012 at 6:27 am
Old Hand... i think you did it!!!!!
i just tested it and got what i want... i will keep testing and let you know.
you guys are the best!
thank you
Labneh
May 11, 2012 at 10:41 am
So far so good!
thanks again guys.
May 11, 2012 at 11:45 pm
Labneh (5/11/2012)
Old Hand... i think you did it!!!!!i just tested it and got what i want... i will keep testing and let you know.
you guys are the best!
thank you
Labneh
The Name's Vinu. 😎
You're Welcome Labneh. 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply