March 10, 2014 at 12:54 pm
I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row. Is this possible?
March 10, 2014 at 1:26 pm
care to share some DDL for your tables / sample data / and expected results from your sample data....would help us all in providing assistance.
kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 10, 2014 at 6:47 pm
WITH BabyTemp (Baby, DT, Temp) AS
(
SELECT 1, '2013-03-11 08:45', 98.6
UNION ALL SELECT 1, '2013-03-11 11:15', 98.7
UNION ALL SELECT 1, '2013-03-11 16:30', 98.5
),
BabyWgt (Baby, DT, Wgt) AS
(
SELECT 1, '2013-03-11 08:50', 21
UNION ALL SELECT 1, '2013-03-11 11:30', 22
UNION ALL SELECT 1, '2013-03-11 16:45', 23
)
SELECT a.Baby, a.DT, b.DT, a.Temp, b.Wgt
FROM BabyTemp a
CROSS APPLY
(
SELECT TOP 1 DT, Wgt
FROM BabyWgt b
WHERE a.Baby = b.Baby
AND b.DT > a.DT -- Use if you want the wgt DT > temp DT
ORDER BY ABS(DATEDIFF(millisecond, a.DT, b.DT) )
) b;
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply