February 4, 2011 at 8:41 am
I have the data from ADT
MRNECDNoAdmDtAdmTmDischDt
62322011000605252010-11-2212:522010-11-24
62322011000663612010-12-0613:032010-12-09
62322011000680592010-12-0912:222010-12-12
and OBlog
MRNDelivery DateDelivery Time
623220112/9/201015:27
I need to write a query to extract the ECDNo: 100068059
which the time closer to my date Delivery Time 12/9/201015:27
Thanks.
February 4, 2011 at 12:36 pm
I don't have SQL 2008, but this may help
DECLARE @ADT TABLE (MRN int,ECDNo int,AdmDt date,AdmTm Time,DischDt date)
INSERT INTO @ADT
SELECT 6232201, 100060525,'2010-11-22','12:52','2010-11-24' UNION
SELECT 6232201, 100066361,'2010-12-06','13:03','2010-12-09' UNION
SELECT 6232201, 100068059,'2010-12-09','12:22','2010-12-12'
DECLARE @OBlog TABLE(MRN int, DelDate date,DelTime time)
INSERT @OBlog
SELECT 6232201, '12/9/2010', '15:27'
Jim
select * from
(
select a.MRN,a.ECDNO
,DATEDIFF(HH,a.admdt,o.deldate) as daysBetween
,DATEDIFF(MINUTE,a.admtm,o.deltime) as minsBetween
,[RANK] = RANK() over(partition by a.mrn order by DATEDIFF(HH,a.admdt,o.deldate) asc,DATEDIFF(MINUTE,a.admtm,o.deltime))
from @ADT a
inner join @OBlog o
on a.MRN = o.MRN
) a
where [RANK] = 1
February 4, 2011 at 1:20 pm
Alternately:
DECLARE @ADT TABLE (MRN int,ECDNo int,AdmDt date,AdmTm Time,DischDt date)
INSERT INTO @ADT
SELECT 6232201, 100060525,'2010-11-22','12:52','2010-11-24' UNION
SELECT 6232201, 100066361,'2010-12-06','13:03','2010-12-09' UNION
SELECT 6232201, 100068059,'2010-12-09','12:22','2010-12-12'
DECLARE @OBlog TABLE(MRN int, DelDate date,DelTime time)
INSERT @OBlog
SELECT 6232201, '12/9/2010', '15:27'
SELECT TOP 1 ECDNo
FROM @OBlog AS O
CROSS JOIN @ADT AS A
ORDER BY ABS(DATEDIFF(day, AdmDt, DelDate)), ABS(DATEDIFF(minute, AdmTm, DelTime));
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2011 at 1:30 pm
Will that still work if there is more than one MRN?
Jim
February 4, 2011 at 1:37 pm
skt5000 (2/4/2011)
Will that still work if there is more than one MRN?Jim
It'll depend on the exact need of the query.
If you need one per MRN, I'd probably do it through a Cross Apply based on that column.
I'd have to performance-test a variety of solutions.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 4, 2011 at 6:29 pm
I need the [highlight=#ffff11]ECDNo.[/highlight]
February 7, 2011 at 6:10 am
We gave you 2 different ways to get what you wanted. Did you try either of them?
Jim
February 7, 2011 at 11:55 am
thanks. I found my own solution before any one posting.My application is in MS ACCESS . No ranking in MS ACCESS. So skt5000
will not able to be tested. GSquared method seems work. Thanks.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply