Complicated SQL Server statement

This is similar to a question I have already asked - sorry.

 have a table that has a number of columns including Patient Id, DateofVisit, Value and a flag indicating first visit.  There is also a column called Change  that is currently zero.

Patient ID, Date Of Visit, First Visit,  Value,  Change
1              2009-01-01     T               0           0
1              2009-01-11     F              10          0
1              2009-02-01     F              31          0
2              2009-01-01     T               0           0
2              2009-01-04     F               3           0
2              2009-02-12     F              11          0
3              2009-01-01     T              null         0
3              2009-01-04     F               3           0
3              2009-02-12     F              11          0

I want to go through the recordset and update the Change column so it reflects the change of Value against the original value given on the first visit.

So the table should look like:

Patient ID, Date Of Visit, First Visit,  Value,  Change
1              2009-01-01     T               8           0
1              2009-01-11     F              10          3
1              2009-02-01     F              31          23
2              2009-01-01     T               1           0
2              2009-01-04     F               3           2
2              2009-02-12     F              11          10
3              2009-01-01     T              null         0             NOTE NULL HERE
3              2009-01-04     F               3           0             FIRST ROW WITH DATA.
3              2009-02-12     F              11          8

To find the first visit I think we should actually look for the earliest date for the patient where the contents of Value is not null.  This is because it may be null at anytime.

Finding a null later in the data means that the value of Change should be the same as the previous one.


soozhCEOAsked:
Who is Participating?
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.

momi_sabagCommented:
i don't understand how you calculate change
for example, 10-8 is 2 and not 3
0
soozhCEOAuthor Commented:
ok - you got me there... but it is a simple subtraction.

Find the first value for the patient and then use that to calculate the change for each subsequent visit except where Value is null
0
momi_sabagCommented:
here you go

with a as (
select PatientID, DateOfVisit, FirstVisit, ISNULL(Value,0) value, Change,
  row number() over(partition by PatientID order by DateOfVisit as rown
  from yourTable
)
, b as (
select t1.PatientId, t2.DateOfVisit, t2.value - t1.value as diff
from a t1 inner join a t2 on t1.PatientId=t2.PatientId and t1.rown = t2.rown-1
)

update tt
set  Change= diff
from yourTable tt join b xx
      on tt.PatientId=  xx.PatientId and tt.DateOfVisit = xx.DateOfVisit
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

soozhCEOAuthor Commented:
hello,

I think there is a syntax problem near

...over(partition by PatientID order by DateOfVisit as rown from yourTable

a missing ) somewhere?

0
momi_sabagCommented:
yes, sorry
try

with a as (
select PatientID, DateOfVisit, FirstVisit, ISNULL(Value,0) value, Change,
  row number() over(partition by PatientID order by DateOfVisit) as rown
  from yourTable
)
, b as (
select t1.PatientId, t2.DateOfVisit, t2.value - t1.value as diff
from a t1 inner join a t2 on t1.PatientId=t2.PatientId and t1.rown = t2.rown-1
)

update tt
set  Change= diff
from yourTable tt join b xx
      on tt.PatientId=  xx.PatientId and tt.DateOfVisit = xx.DateOfVisit
0
soozhCEOAuthor Commented:
I have tried your sql (and had to convert a few column names to swedish).

I get an error message "ncorrect syntax near '('." but it is hard to see where.

Is row number a valid function?


with a as (
select PatientID, Besöksdag, ÄrUrsprung, ISNULL(EDTRSAktuelltÖga,0) EDTRSAktuelltÖga, EDTRSÄndring,
  row number() over(partition by PatientID order by Besöksdag) as rown
  from #ReportData
)
, b as (
select t1.PatientId, t2.Besöksdag, t2.value - t1.value as diff
from a t1 inner join a t2 on t1.PatientId=t2.PatientId and t1.rown = t2.rown-1
)

update tt
set  EDTRSÄndring= diff
from #ReportData tt join b xx
      on tt.PatientId=  xx.PatientId and tt.Besöksdag = xx.Besöksdag


select * from #ReportData
order by kli_kliniknr, PatientID, Besöksdag
0
momi_sabagCommented:
it should be
row_number()

with an _
0
soozhCEOAuthor Commented:
we are getting there however the subtraction must be always be made against the first value.  i.e. the change compared to the original value and not the previous one as we have now.
0
soozhCEOAuthor Commented:
i have removed all the null values so it should be easier now
0
momi_sabagCommented:
so you can try this

with a as (
select PatientID, Besöksdag, ÄrUrsprung, ISNULL(EDTRSAktuelltÖga,0) EDTRSAktuelltÖga, EDTRSÄndring,
  min(value) over(partition by PatientID) as minVal,
  row number() over(partition by PatientID order by Besöksdag) as rown
  from #ReportData
)
, b as (
select t1.PatientId, t2.Besöksdag, t2.value - t1.MINvAL as diff
from a t1 inner join a t2 on t1.PatientId=t2.PatientId and t1.rown = t2.rown-1
)

update tt
set  EDTRSÄndring= diff
from #ReportData tt join b xx
      on tt.PatientId=  xx.PatientId and tt.Besöksdag = xx.Besöksdag


select * from #ReportData
order by kli_kliniknr, PatientID, Besöksdag
0
soozhCEOAuthor Commented:
this only works some of the time and I think it is this:

  min(value) over(partition by PatientID) as minVal,
  row number() over(partition by PatientID order by Besöksdag) as rown

I need to always compare against the value given on the first visit.  The minimumn value of Besöksdag gives the record that represents the first visit.

0
momi_sabagCommented:
you are correct, my mistake

with a as (
select PatientID, Besöksdag, ÄrUrsprung, ISNULL(EDTRSAktuelltÖga,0) EDTRSAktuelltÖga, EDTRSÄndring,
  min(value) over(partition by PatientID) as minVal,
  row number() over(partition by PatientID order by Besöksdag) as rown
  from #ReportData
)
, b as (
select t1.PatientId, t2.Besöksdag, t2.value - (select value from a t3 where t1.PatientId=t3.PatientId and t3.rown=1)  as diff
from a t1 inner join a t2 on t1.PatientId=t2.PatientId and t1.rown = t2.rown-1
 )

update tt
set  EDTRSÄndring= diff
from #ReportData tt join b xx
      on tt.PatientId=  xx.PatientId and tt.Besöksdag = xx.Besöksdag


select * from #ReportData
order by kli_kliniknr, PatientID, Besöksdag
0
soozhCEOAuthor Commented:
Yes that worked better but I have just realised that there is one other column we need to take into account.

We are dealing with eye measurements, and the patients have TWO eyes! So the unique identity is not just PatientID but also Eye.  

Eye can be 'L' or 'R'.  

I have tried to modify the sql but without success.  Perhaps you can sugest a modification?
0
momi_sabagCommented:
with a as (
select PatientID, EyeId, Besöksdag, ÄrUrsprung, ISNULL(EDTRSAktuelltÖga,0) EDTRSAktuelltÖga, EDTRSÄndring,
  min(value) over(partition by PatientID, EyeId) as minVal,
  row number() over(partition by PatientID, EyeId order by Besöksdag) as rown
  from #ReportData
)
, b as (
select t1.PatientId, t1.EyeId, t2.Besöksdag, t2.value - (select value from a t3 where t1.PatientId=t3.PatientId and t1.eyeId = t3.Eyeid and t3.rown=1)  as diff
from a t1 inner join a t2 on t1.PatientId=t2.PatientId and t1.EyeId = t2.Eyeid and t1.rown = t2.rown-1
 )

update tt
set  EDTRSÄndring= diff
from #ReportData tt join b xx
      on tt.PatientId=  xx.PatientId and tt.Besöksdag = xx.Besöksdag and tt.EyeId= xx.EyeId


select * from #ReportData
order by kli_kliniknr, PatientID, Besöksdag
0

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
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.