April 14, 2009 at 2:06 pm
CREATE TABLE [dbo].[MemberLabResult] (
[MemberLabId] [int] IDENTITY (1, 1) NOT NULL ,
[MemberId] [int] NOT NULL ,
[LabDate] [datetime] NOT NULL ,
[LabId] [int] NOT NULL ,
[LabValue] [float] )
ON [PRIMARY]
GO
INSERT INTO MemberLabResult
(MemberID, LabDate, LabID, Labvalue)
SELECT '1','10/17/2008','1','3.2' UNION ALL
SELECT '1','11/17/2008','1','3.9' UNION ALL
SELECT '1','12/17/2008','1','2.0' UNION ALL
SELECT '2','10/17/2008','1','4.2' UNION ALL
SELECT '2','11/17/2008','1','3.0' UNION ALL
SELECT '2','12/17/2008','1','2.0' UNION ALL
SELECT '3','10/17/2008','1','3.2' UNION ALL
SELECT '3','11/17/2008','1','2.0' UNION ALL
SELECT '3','12/17/2008','1','4.0'
Greetings -
Based on the above data structure, I would like to pull the avg for the top
two months for each MemberID where the avg lab value is less than four or
greater than four. The data can be in the table in most any order.
This query works for averaging all values:
Select Memberid, avg(labvalue)
From MemberLabResult
where labid = 1
Group by Memberid
Having
avg(labvalue) > 4 or
avg(labvalue) < 4
What would be the most efficient way to limit the average value to only the
most two recent months ??
Thanks
Todd
April 14, 2009 at 2:49 pm
Select Memberid, avg(labvalue)
From MemberLabResult
where labid = 1
Group by Memberid
Having
avg(labvalue) > 4 or
avg(labvalue) < 4
Add your date filter to the where clause:
WHERE labid = 1
AND LabDate >= dateadd(month, datediff(month, 0, getdate()) - 2, 0)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2009 at 3:08 pm
Jeffrey -
Thank you for the reply. I see that your solution will work if the lab
values are there every month, but what if they skip one or two months ?
I can't guarantee that these values will be added to the table. Any thoughts
on how to average the top two regardless of date ??
Thanks
Todd
April 14, 2009 at 3:32 pm
Hi,
try this:
select memberid, AVG(labvalue)
FROM
(
Select Memberid
, labvalue
, ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY LabDate DESC) RowNum
From MemberLabResult
WHERE LabId = 1
) result
WHERE RowNum <= 2
GROUP BY MemberID
HAVING
avg(labvalue) > 4 or
avg(labvalue)
Hope this helps 🙂
-----------------------
SQL Server Database Copy Tool at Codeplex
April 14, 2009 at 3:46 pm
Jetro -
Thanks for the great solution but unfortunately I am still using SQL server 2000
and have no row_numbers !!! Any other ideas ??
Thanks
Todd
April 14, 2009 at 3:59 pm
Instead of the ROW_NUMBER() function you can use the TOP 2 clause in a derived table to get the 2 most recent rows.
SELECT MemberId, AVG(LabValue)
FROM (
SELECT TOP (2) MemberId, LabValue, LabDate
FROM MemberLabResult
WHERE (LabId = 1)
ORDER BY LabDate DESC
) Result
GROUP BY MemberID
HAVING AVG(LabValue) <> 4
April 14, 2009 at 4:02 pm
Well, do you want the last two months average - or the average from the last two entries? Is it possible that you could have results for the last two weeks? Or, can you only have one result per month?
For this particular test - if the patient misses a month, how does that affect the average? If in fact this test is performed monthly?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2009 at 4:15 pm
Andrew -
Thanks for the suggestion but the "top" clause appears
to literally return 2 rows - not 2 rows per member.
Jeffrey -
The query should not be data dependent, meaning that it should
take the two most current rows regardless of date or interval.
Thanks for you help
Todd
April 14, 2009 at 4:15 pm
andrewd.smith (4/14/2009)
Instead of the ROW_NUMBER() function you can use the TOP 2 clause in a derived table to get the 2 most recent rows.
SELECT MemberId, AVG(LabValue)FROM (
SELECT TOP (2) MemberId, LabValue, LabDate
FROM MemberLabResult
WHERE (LabId = 1)
ORDER BY LabDate DESC
) Result
GROUP BY MemberID
HAVING AVG(LabValue) <> 4
This will only return the two most recent rows across all members. Not the two most recent rows per member. To get the two most recent rows per member - you are going to have to identify the minimum date for each member.
Something along the lines of:
Select MemberId, Labdate
Into #LabDates
From MemberLabResult m1
Where labdate = (select top 1 labdate
from (Select top 2 labdate
from MemberLabResult m2
where m2.memberid = m1.memberid
order by labdate)
order by labdate desc);
Select Memberid, avg(labvalue)
From MemberLabResult m1
Join #LabDates l On l.MemberID = m1.MemberID
And l.labdate = m1.labdate
where labid = 1
Group by Memberid
Having
avg(labvalue) > 4 or
avg(labvalue) < 4
The above is not tested at all - and, there are probably better ways of approaching this, but it should give you a good idea.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 14, 2009 at 5:09 pm
Sorry, I really should pay more attention.
This modification uses a correlated subquery to get the 2 latest rows for each MemberId.
It is untested so there could be more stupid mistakes.
SELECT MLROUTER.MemberId, AVG(MLROUTER.LabValue)
FROM MemberLabResult MLROUTER
WHERE MLROUTER.MemberLabId IN (
SELECT TOP (2) MLRINNER.MemberLabId
FROM MemberLabResult MLRINNER
WHERE (MLRINNER.LabId = 1)
AND (MLRINNER.MemberId = MLROUTER.MemberId)
ORDER BY MLRINNER.LabDate DESC
)
GROUP BY MLROUTER.MemberId
HAVING AVG(MLROUTER.LabValue) <> 4
April 15, 2009 at 11:07 am
Todd, if you can use temp tables, maybe this will work for you. It seems to work with the sample data you provided, but may have unforeseen issues with a larger set of data.
--Create temp table with ordered values by MemberID and date
SELECT
IDENTITY(INT,1,1) AS ID,
MemberID,
LabDate,
LabID,
LabValue
INTO #lab
FROM MemberLabResult
ORDER BY MemberID,LabDate DESC
--Now see the results
SELECT
MemberID,
AVGLabValue = AVG(LabValue)
FROM
(
SELECT
l.*
FROM #lab l,
(
SELECT
ID = MIN(id),
MemberID
FROM #lab
GROUP BY MemberID
) t1
WHERE t1.id = l.id
OR t1.id +1 = l.id
) t2
GROUP BY t2.MemberID
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
April 15, 2009 at 11:22 am
I just realized I did not address your need to exclude where the average = 4, so I added another member ID to test, where the average would be 4, and included the having clause. Sorry for the miss.
INSERT INTO MemberLabResult
(MemberID, LabDate, LabID, Labvalue)
SELECT '1','10/17/2008','1','3.2' UNION ALL
SELECT '1','11/17/2008','1','3.9' UNION ALL
SELECT '1','12/17/2008','1','2.0' UNION ALL
SELECT '2','10/17/2008','1','4.2' UNION ALL
SELECT '2','11/17/2008','1','3.0' UNION ALL
SELECT '2','12/17/2008','1','2.0' UNION ALL
SELECT '3','10/17/2008','1','3.2' UNION ALL
SELECT '3','11/17/2008','1','2.0' UNION ALL
SELECT '3','12/17/2008','1','4.0' UNION ALL
SELECT '4','10/17/2008','1','4.0' UNION ALL
SELECT '4','11/17/2008','1','4.0' UNION ALL
SELECT '4','12/17/2008','1','4.0'
And add this after the last group by
HAVING AVG(LabValue) <> 4
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
April 15, 2009 at 11:29 am
Thanks a lot Greg,
Your code works great and I'll use it as a solution !!
Todd
April 15, 2009 at 1:55 pm
Todd, if you've got data like this:
INSERT INTO MemberLabResult
(MemberID, LabDate, LabID, Labvalue)
SELECT '1','10/17/2008','1','3.2' UNION ALL
SELECT '1','12/17/2008','1','2.0' UNION ALL
what should be the average for last 2 months?
1.0 or 2.6?
_____________
Code for TallyGenerator
April 15, 2009 at 2:05 pm
Sergiy -
The average would be 2.6 - ( (3.2 + 2.0) / 2 ) using the
"labvalue" field.
Todd
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply