Query syntax

Using MS SQL Server 2008.

I have a table which holds patient visits to a number of clincs.  At each visit there is a date and a recorded value.

Table PatientVisits:

Unitid integer -- the clinic number
PatId integer  -- the patients id
msr_Date Date -- date of visit
NPIPoints integer -- the measured value.


I need to know the average change in the NPIPoints between the last two visits for each patient for each clinic.

So if the data in the table was as follows:
UnitId  PatId   msr_date             NPIPoints
1           1         2012-01-01         40
1           1         2012-05-03         32
1           1         2012-09-23         26
2           4         2011-01-16         30      
2           5         2013-03-31         60
2           5         2013-12-31         55
2           6         2011-04-20         30
2           6         2012-06-30         27

Open in new window

We would get
UnitId    Ave change in NPIPoints
  1           6  
  2           4

Open in new window


Note that patient nr 4 is not included because they one have one measurement.

Also it’s the latest two measurements for patient nr 1 that are used.  

Can anyone suggest a query?
soozhCEOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Why 2 has 4:
UnitId    Ave change in NPIPoints
  1           6  
  2           4
YZlatCommented:
I think results should be:

1       1       6
2       5       5
2       6       3

or

 1       6
5       5
6       3
MlandaTCommented:
I hope that is self explanatory enough:
with data(UnitId, PatId, msr_date, NPIPoints) as (
	select 1, 1, '2012-01-01', 40
	union select 1, 1, '2012-05-03', 32
	union select 1, 1, '2012-09-23', 26
	union select 2, 4, '2011-01-16', 30
	union select 2, 5, '2013-03-31', 60
	union select 2, 5, '2013-12-31', 55
	union select 2, 6, '2011-04-20', 30
	union select 2, 6, '2012-06-30', 27
), DataWithRowNumbers as (
        --we will rely on the row_number to find the last 2 visits
	select *, row_number() over(partition by PatId order by msr_date desc) RowNumber 
	from data
), PatientNPIPointsChange as (
        --we need to join on the 1st and 2nd data items, then calculate the difference in NPIPoints
	select D1.*, D2.msr_date msr_date_2, D2.NPIPoints NPIPoints_2, D2.NPIPoints - D1.NPIPoints AS NPIPointsChange /*D1-D2 or D2-D1???? Use ABS(D2.NPIPoints - D1.NPIPoints) if it doesnt matter*/
	from DataWithRowNumbers D1
		inner join DataWithRowNumbers D2 on D1.PatId=D2.PatId and D1.RowNumber=1 and D2.RowNumber=2
)
--armed with that data, we can then calculate the averages
select UnitId, AVG(NPIPointsChange) AS [Ave change in NPIPoints] 
from PatientNPIPointsChange
group by UnitId

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
@YZlat,

Your post helped me to figure out how the desired result is calculated. you had:

1       1       6
2       5       5
2       6       3

but the average of 5 and 3 is 4 hence:
UnitId    Ave change in NPIPoints
  1           6  
  2           4

per the original post. In preparation to the final solution, I was able to exclude single entries by producing;
UnitId  PatId       msr_date         NPIPoints
1           1             2012-09-23      26
1           1             2012-05-03      32
2           5             2013-12-31      55
2           5            2013-03-31      60
2           6            2012-06-30      27
2           6            2011-04-20      30

using:
 ;With a
 as
 (Select rank() over(partition by UnitId, PatId order by msr_date desc ) As TheDate
   , count(*) over(partition by PatId) As Qty
   , UnitId, PatId, msr_date, NPIPoints
  from #t
  )
  Select UnitId, PatId, msr_date, NPIPoints
  --, (select  x.NPIPoints from a x where y.UnitId=x.UnitId and y.PatId=x.PatId and y.msr_date =x.msr_date and y.NPIPoints=x.NPIPoints and TheDate=2 ) -
 -- (select  x.NPIPoints from a x where y.UnitId=x.UnitId and y.PatId=x.PatId and y.msr_date =x.msr_date and y.NPIPoints=x.NPIPoints and TheDate=1 )  as v
  --(select NPIPoints from a where TheDate=2) v2
  From a 
  Where Qty>1 and TheDate<=2
  --Group By UnitId
  Order By UnitId, PatId, TheDate;

Open in new window


I think it ought to be done in a table-valued function or in a proc. Not clear what is database environment (SQL Server, Oracle, etc?).

I am including this code in case someone could continue with it.

The following is the temp table (#t) used in this demo:
create table #t(UnitId int,  PatId int,   msr_date date,             NPIPoints int)
insert into #t(UnitId,  PatId,   msr_date ,            NPIPoints) values
(1,           1,         '2012-01-01',          400)
,(1 ,          1 ,        '2012-05-03',         32)
,(1,           1 ,        '2012-09-23',         26)
,(2 ,          4 ,        '2011-01-16',         30 )     
,(2 ,          5 ,        '2013-03-31',         60)
,(2,           5 ,        '2013-12-31',         55)
,(2,           6 ,        '2011-04-20',         30)
,(2,           6 ,        '2012-06-30',         27);
select * from #t;

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.