September 21, 2010 at 6:43 am
What will be the best way to select the data from one table which delivery date are in between admit date and discharge date in another table. I have the common field MRN.
see sample:
First table;
Log Labor & Delivery Delivery Date Time MRN G/P GA
7/31/2010 10:07:00 AM 7050164 3/0 33/0 !
7/31/2010 9:25:00 PM 7752728 2/0 40/0 !
7/31/2010 3:58:00 PM 2944973 2/1 40/0 !
7/31/2010 11:14:00 AM 8709198 2/1 39/6 !
7/31/2010 9:17:00 AM 8604142 3/3 39/3 !
Second table;
Query1 MRNO AdmDate DischargeDate
5536164 20100718 20100723
10000506 20100718 20100719
10000509 20100718 20100720
8649402 20100718 20100721
10000510 20100718 20100721
8878951 20100718 20100720
10000511 20100718 20100720
8014664 20100718 20100721
here is the sample date
temp1 Delivery Date MRN AdmDate DischargeDate PNO
7/24/2010 7686967 20100622 20100727 12730891
7/24/2010 7686967 20100622 20100727 12730891
7/24/2010 7686967 20100622 20100727 12730891
7/24/2010 7686967 20100622 20100727 12730891
7/15/2010 5721121 20100706 20100717 12737193
7/15/2010 5721121 20100706 20100717 12737193
For example, if first patient delivery date 07/24 between admit date 20100622 and discharge date 20100727 with the same mrn, that is the same patient. I will put true otherwise I will put false there.
Thanks.
September 21, 2010 at 6:51 am
Sounds like you want something like this:
SELECT
*
FROM
table1 T1 JOIN
table2 T2
ON T1.mrn = T2.mrno AND
T1.DeliveryDate >= T2.AdmDate AND
T1.DeliveryDate <= T2.DischargeDate
Can DischargeDate ever be NULL?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 21, 2010 at 6:54 am
thx. I think I need to get the table date as mm/dd/yy first.
I modified the string as
SELECT
*
FROM
table1 T1 inner JOIN
table2 T2
ON T1.mrn = T2.mrno AND
T1.DeliveryDate >= T2.AdmDate AND
T1.DeliveryDate <= T2.DischargeDate
September 21, 2010 at 7:04 am
Frances L (9/21/2010)
thx. I think I need to get the table date as mm/dd/yy first.
If the data is stored as one of the date datatypes then you do not need to format to any specific date format, SQL Server knows how to handle it. If the data is not stored as one of the date datatypes then I suggest you get that fixed because you will just have problems in the future and end up with invalid dates in those date columns.
If you are stuck with dates in character (char/nchar/varchar/nvarchar) columns I'd suggest creating a view that converts them to the proper date datatype and then query off the view. You may even want to go with an indexed view to help performance (http://technet.microsoft.com/en-us/library/cc917715.aspx)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply