How can I prevent a record from appearing based on two columns
-
Sorry for the vague Subject line but, I couldn' t come up with a good summary. In the table the PrescriptionID and DuplicatePrescriptionID repeat themselves. Once as PrescriptionID and DuplicatePrescriptionID then, as DuplicatePrescriptionID and PrescriptionID. It will be apparent when you look at the table. How can I get a "unique" list of records that counts the "duplicate pair" only once?
create table #T
(
VisitID varchar(30),
PrescriptionID varchar(30),
DuplicateSeqID int,
DuplicateName varchar(30),
DuplicatePrescriptionID varchar(30),
DuplicateType char(1)
)
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',1,'IBUPROFEN','594060','G')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594060','C')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594059',3,'IBUPROFEN','594060','I')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',1,'IBUPROFEN','594059','G')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594059','C')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594060',3,'IBUPROFEN','594059','I')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',1,'QUEtiapine Fumarate','594065','G')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',2,'ANTIPSYCHOTIC AGENTS','594065','C')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594064',3,'QUETIAPINE','594065','I')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',1,'QUEtiapine Fumarate','594064','G')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',2,'ANTIPSYCHOTIC AGENTS','594064','C')
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType) values('F1-B20170128151307684','594065',3,'QUETIAPINE','594064','I')
- NineIron - Monday, January 30, 2017 10:21 AM
Could you tell us the "answer" (the correct set) and then maybe we can figure it out?
- pietlinden - Monday, January 30, 2017 10:31 AM
Not entirely sure if I'm supposed to UNION the two columns together and then do a GROUP BY or just a plain GROUP
The first option would be like this:SELECT VisitID, PrescriptionID, COUNT(*) As dupecount
FROM (
SELECT VisitID, PrescriptionID
FROM #T
UNION ALL SELECT VisitID, DuplicatePrescriptionID
FROM #T) x
GROUP BY x.visitID, PrescriptionID; -
Can you elaborate a little bit further on the problem and the expected output, not entirely clear what you are after?
😎Quick suggestions, not certain though
create table #T
(
VisitID varchar(30),
PrescriptionID varchar(30),
DuplicateSeqID int,
DuplicateName varchar(30),
DuplicatePrescriptionID varchar(30),
DuplicateType char(1)
)
insert into #T(VisitID, PrescriptionID, DuplicateSeqID, DuplicateName, DuplicatePrescriptionID, DuplicateType)
values('F1-B20170128151307684','594059',1,'IBUPROFEN','594060','G')
,('F1-B20170128151307684','594059',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594060','C')
,('F1-B20170128151307684','594059',3,'IBUPROFEN','594060','I')
,('F1-B20170128151307684','594060',1,'IBUPROFEN','594059','G')
,('F1-B20170128151307684','594060',2,'NONSTEROIDAL ANTI-INFLAMMATORY','594059','C')
,('F1-B20170128151307684','594060',3,'IBUPROFEN','594059','I')
,('F1-B20170128151307684','594064',1,'QUEtiapine Fumarate','594065','G')
,('F1-B20170128151307684','594064',2,'ANTIPSYCHOTIC AGENTS','594065','C')
,('F1-B20170128151307684','594064',3,'QUETIAPINE','594065','I')
,('F1-B20170128151307684','594065',1,'QUEtiapine Fumarate','594064','G')
,('F1-B20170128151307684','594065',2,'ANTIPSYCHOTIC AGENTS','594064','C')
,('F1-B20170128151307684','594065',3,'QUETIAPINE','594064','I')
;
SELECT
T.VisitID
,T.PrescriptionID
,T.DuplicateSeqID
,T.DuplicateName
,T.DuplicatePrescriptionID
,T.DuplicateType
FROM #T T
WHERE T.DuplicateSeqID = 1;WITH BASE_DATA AS
(
SELECT
T.VisitID
,ROW_NUMBER() OVER
(
PARTITION BY T.PrescriptionID
,T.DuplicatePrescriptionID
ORDER BY T.DuplicateSeqID ASC
) AS RID
,T.PrescriptionID
,T.DuplicateSeqID
,T.DuplicateName
,T.DuplicatePrescriptionID
,T.DuplicateType
FROM #T T
)
SELECT
BD.VisitID
,BD.PrescriptionID
,BD.DuplicateSeqID
,BD.DuplicateName
,BD.DuplicatePrescriptionID
,BD.DuplicateType
FROM BASE_DATA BD
WHERE BD.RID = 1DROP TABLE #T;
Output (same for both)
VisitID PrescriptionID DuplicateSeqID DuplicateName DuplicatePrescriptionID DuplicateType
------------------------------ ------------------------------ -------------- ------------------------------ ------------------------------ -------------
F1-B20170128151307684 594059 1 IBUPROFEN 594060 G
F1-B20170128151307684 594060 1 IBUPROFEN 594059 G
F1-B20170128151307684 594064 1 QUEtiapine Fumarate 594065 G
F1-B20170128151307684 594065 1 QUEtiapine Fumarate 594064 G - Eirikur Eiriksson - Monday, January 30, 2017 10:39 AM
Here is what I need.
Looking at the first record, PrescriptionID=594059. The patient is given ibuprofen then, the MD orders ibuprofen again, DuplicatePrescriptionID=594060. If you look at the way the data is stored in the table there is a combination of PrescriptionID=594060 and the DuplicatePrescriptionID=594059. It's true that they are both duplicates of each other but, because 594059 is a lower number than 594060, 594060 should be the duplicate.
"> -
Unless I'm missing something, it's very simple.
SELECT *
FROM #T
WHERE PrescriptionID < DuplicatePrescriptionIDDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Monday, January 30, 2017 11:11 AM
I'm such an idiot................
Thanx. - drew.allen - Monday, January 30, 2017 11:11 AM
😎 - Eirikur Eiriksson - Monday, January 30, 2017 11:26 AMdrew.allen - Monday, January 30, 2017 11:11 AM
😎Exactly.
- NineIron - Monday, January 30, 2017 11:29 AMEirikur Eiriksson - Monday, January 30, 2017 11:26 AMdrew.allen - Monday, January 30, 2017 11:11 AM
😎Exactly.
can you really use < on varchar data and have it act reliably? Or do you have to convert it into a number?
- bgalway - Tuesday, January 31, 2017 7:13 AMNineIron - Monday, January 30, 2017 11:29 AMEirikur Eiriksson - Monday, January 30, 2017 11:26 AMdrew.allen - Monday, January 30, 2017 11:11 AM
😎Exactly.
can you really use < on varchar data and have it act reliably? Or do you have to convert it into a number?
Seems to work OK.
- bgalway - Tuesday, January 31, 2017 7:13 AM
Yes, you can use it reliably. The point was to be able to pick one of the two records. It doesn't matter which of the two you pick, as long as you only pick one of them. It doesn't matter that a different method (converting to numeric) might sometimes pick the other of the two records as long as either method will only pick one of them. It doesn't matter that changing the collation may change the results, it still only picks one of them.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - bgalway - Tuesday, January 31, 2017 7:13 AM
This is perfectly reliable provided you are comparing like with like- comparison operators operate on the character codes which means you need to be aware of the collations used as these will affect the sort order.
If the two operands have collation A you will always get the same result.
If they both have collation B then they will still produce a consistent result but it may not be the same as that of the first query.
If both your operands have different collations all bets are off. -
However ....
When comparing varchars
'100' < '99' - DesNorton - Wednesday, February 1, 2017 2:45 AM
That's because '1' < '9' in all collations I am aware of - you will also find that '099' < '100'
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply