July 16, 2008 at 3:22 am
I have a tables as follows :
Visit_Id Med_Id DateDescDosage Qty Value Doctor
6898108552 07-08-2008 XXX2 days 118John
6898109219 07-08-2008XXX7 days 217.5John
6898109024 07-12-2008XXX10 days 241.5John
1234A123 07-08-2008 XXX 7 days 16.5Mark
1234B123 07-08-2008 XXX7 days 16.5Mark
I want to find for the same visit id which doctor is prescribing a medicine on a different date.For example in the above table Dr John for the visit id 689810 is prescribing medcine on 12th july also.(the date is automatically captured during data entry.
July 16, 2008 at 3:54 am
So what would you want the results to look like for this sample data?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
July 16, 2008 at 4:06 am
The query should fetch the all the visit-ids which is for another date like below for the sample data I gave.
Visit_Id Med_Id Date Desc Dosage Qty Value Doctor
689810 9024 07-12-2008 XXX 10 days 2 41.5 John
July 16, 2008 at 4:28 am
Could you add more examples in order to undertand what you are trying to do?
Find the doctor that prescribed in the last date?
Find diferent dates of prescribing in a visit?
Find all the doctor that prescribin in a visit?
July 16, 2008 at 5:15 am
mathewspsimon (7/16/2008)
The query should fetch the all the visit-ids which is for another date like below for the sample data I gave.Visit_Id Med_Id Date Desc Dosage Qty Value Doctor
689810 9024 07-12-2008 XXX 10 days 2 41.5 John
Thanks. This is what I think you're asking for...
--sample data
declare @t table (Visit_Id int, Med_Id varchar(5), Date datetime, [Desc] varchar(10), Dosage varchar(10), Qty int, Value decimal(5, 2), Doctor varchar(10))
insert @t
select 689810, '8552', '07-08-2008', 'XXX', '2 days', 1, 18, 'John'
union all select 689810, '9219', '07-08-2008', 'XXX', '7 days', 2, 17.5, 'John'
union all select 689810, '9024', '07-12-2008', 'XXX', '10 days', 2, 41.5, 'John'
union all select 1234, 'A123', '07-08-2008', 'XXX', '7 days', 1, 6.5, 'Mark'
union all select 1234, 'B123', '07-08-2008', 'XXX', '7 days', 1, 6.5, 'Mark'
--query (the bit you need)
select * from @t a where not exists
(select * from @t group by visit_id having visit_id = a.visit_id and min(date) = a.date)
/* results
Visit_Id Med_Id Date Desc Dosage Qty Value Doctor
----------- ------ ----------------------- ------- ---------- ------- -------- ----------
689810 9024 2008-07-12 00:00:00.000 XXX 10 days 2 41.50 John
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply