July 16, 2015 at 9:24 am
I have a nested query that retrieves a min value. I would like to know
how do i retrieve the corresponding time with that.
my data looks like this
valuevaluetime
1212/31/14 14:51
12.42/19/15 23:30
[highlight=#ffff11]10.92/21/15 6:40[/highlight]
13.21/25/15 20:47
My min value is 10.9 and i need the date 02/21/15
my nested query is as follows
( select min(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)),
f.valuetime
from bvfindings f
where f.ObjectName = 'Hb'
but i get all values then.
Please help :exclamationmark:
July 16, 2015 at 9:34 am
You could change your nested query like this:
( select TOP 1
cast(f.valuestr as float) min_valuestr,
f.valuetime
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time
ORDER BY min_valuestr)
However, you might be able to improve the performance by using a ROW_NUMBER() solution, but I would need to see more of your code to provide a certain answer.
July 16, 2015 at 9:59 am
that query will work perfectly but I am having trouble with placement in my sp.
i receive an error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
i looked this up on how to fix the errror which i why i think my placement is off.
SELECT Name
FROM Production.Product
WHERE EXISTS
(SELECT *
FROM Production.ProductSubcategory
WHERE ProductSubcategoryID =
Production.Product.ProductSubcategoryID
AND Name = 'Wheels')
************** MY QUERY BELOW ****************
select DISTINCT
s.facilityid,
s.sessionid,
s.MRNAS MRN,
s.LastName + ', ' + s.FirstNameAS Patient,
F.OBJECTNAMEAS TYPE_,
( select min(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time
)AS MINIMUM_VALUE,
( select TOP 1
cast(f.valuestr as float) min_valuestr,
f.valuetime
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time
ORDER BY f.valuestr) AS MINIMUM_RECORD_TIME,
( select max(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)AS MAXIMUM_VALUE,
T.DeliveryTimeAS DELIVERY_TIME,
T.CHARTSTARTTIMEAS CHARTSTARTTIME,
CASE WHEN DT.VaginalDelivery ='YES' THEN 'VAGINAL'
WHEN DT.CesareanDelivery = 'YES' THEN 'CESAREAN' ENDAS DELIVERY_TYPE
FROM
dbo.BLSession_Extended AS s
INNER JOIN
dbo.BVFindings AS f ON s.sessionID = f.sessionId
INNER JOIN
dbo.MO_Times AS T ON T.SessionID = s.sessionID
INNER JOIN
dbo.MO_DeliveryTypePatient_table AS DT ON s.sessionID = DT.SessionID
WHERE F.ObjectName = 'Hematocrit'
AND s.FacilityID = 1--@FacilityID
and T.DeliveryTime between '01/01/2015' AND'07/01/2015'--@StartTimeOut and @EndTimeOut
July 16, 2015 at 10:13 am
That's because you're returning 2 values to assign a single scalar value.
Here's a refactored version of your query to avoid an additional hit on the table.
select DISTINCT
s.facilityid,
s.sessionid,
s.MRNAS MRN,
s.LastName + ', ' + s.FirstNameAS Patient,
F.OBJECTNAMEAS TYPE_,
mv.MINIMUM_VALUE,
mv.MINIMUM_RECORD_TIME,
( select max(cast(f.valuestr as float))
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time)AS MAXIMUM_VALUE,
T.DeliveryTimeAS DELIVERY_TIME,
T.CHARTSTARTTIMEAS CHARTSTARTTIME,
CASE WHEN DT.VaginalDelivery ='YES' THEN 'VAGINAL'
WHEN DT.CesareanDelivery = 'YES' THEN 'CESAREAN' ENDAS DELIVERY_TYPE
FROM dbo.BLSession_Extended AS s
JOIN dbo.BVFindings AS f ON s.sessionID = f.sessionId
JOIN dbo.MO_Times AS T ON T.SessionID = s.sessionID
JOIN dbo.MO_DeliveryTypePatient_table AS DT ON s.sessionID = DT.SessionID
CROSS APPLY( select TOP 1
cast(f.valuestr as float) MINIMUM_VALUE,
f.valuetime MINIMUM_RECORD_TIME
from bvfindings f
where f.ObjectName = 'Hematocrit'
and f.sessionid = s.sessionid
and f.ValueTime > s.open_time
ORDER BY MINIMUM_VALUE) mv
WHERE F.ObjectName = 'Hematocrit'
AND s.FacilityID = 1--@FacilityID
and T.DeliveryTime between '01/01/2015' AND '07/01/2015'--@StartTimeOut and @EndTimeOut
EDIT: Code format
July 16, 2015 at 10:23 am
You need to use CROSS APPLYs (CA) rather than an INNER JOIN (IJ). CA only, without the IJ, assumes you need to see only the low and high hematocrit readings -- if you need to see all readings, but include the low and high readings on every line, you'll still need the IJ -- in that case, uncomment the IJ below.
select DISTINCT
s.facilityid,
s.sessionid,
s.MRNAS MRN,
s.LastName + ', ' + s.FirstNameAS Patient,
f_min.ObjectName AS TYPE_,
f_min.valuestr AS MINIMUM_VALUE,
f_min.valuetime AS MINIMUM_RECORD_TIME,
f_max.valuestr AS MAXIMUM_VALUE,
--f_max.valuetime AS MAXIMUM_RECORD_TIME,
T.DeliveryTimeAS DELIVERY_TIME,
T.CHARTSTARTTIMEAS CHARTSTARTTIME,
CASE WHEN DT.VaginalDelivery ='YES' THEN 'VAGINAL'
WHEN DT.CesareanDelivery = 'YES' THEN 'CESAREAN' ENDAS DELIVERY_TYPE
FROM
dbo.BLSession_Extended AS s
--INNER JOIN
--dbo.BVFindings AS f ON s.sessionID = f.sessionId
CROSS APPLY (
SELECT TOP (1) *
FROM BVFindings AS f
WHERE
f.ObjectName = 'Hematocrit' AND
f.session_id = s.sessionid AND
f.ValueTime > s.open_time
ORDER BY f.valuestr
) AS f_min
CROSS APPLY (
SELECT TOP (1) *
FROM BVFindings AS f
WHERE
f.ObjectName = 'Hematocrit' AND
f.session_id = s.sessionid AND
f.ValueTime > s.open_time
ORDER BY f.valuestr DESC
) AS f_max
INNER JOIN
dbo.MO_Times AS T ON T.SessionID = s.sessionID
INNER JOIN
dbo.MO_DeliveryTypePatient_table AS DT ON s.sessionID = DT.SessionID
WHERE s.FacilityID = 1--@FacilityID
and T.DeliveryTime >= '01/01/2015' AND T.DeliveryTime < '07/01/2015'--@StartTimeOut and @EndTimeOut
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 16, 2015 at 10:28 am
thank you thank you thank you for teaching me something new!!!!
July 16, 2015 at 10:37 am
You're welcome!
Btw, note that for "SELECT TOP (1) *", the * is not a problem, because SQL can determine that it needs to get only the columns that are actually referenced in the outer query. You can verify that by looking at the OUTPUT section for that table in the query plan -- it should include only the column actually referenced by f_min and f_max aliases, not all the columns in the f table.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply