show recent date with value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

show recent date with value

Author

Topic

murd
Starting Member

24 Posts

Posted - 2012-09-12 : 12:08:06

I am trying to show the recent date of an entry for each person. here is what i have so far

select pp.searchname, pp.birthdate, max(o.obsdate) as obs_date, o.OBSVALUE
from patientprofile pp
inner join document d on pp.pid = d.pid
inner join obs o ON o.SDID = d.SDID
inner join obshead h ON o.HDID = h.HDID
where h.NAME = 'BMI' and pp.Birthdate between '9-12-1994' and '9-12-2007'
group by pp.searchname, pp.birthdate , o.OBSVALUE
order by pp.searchname

results
Name | birthdate | date entry | value
Jane Doe 2003-08-31 2008-09-11 15:31:13.000 14.43
Jane Doe 2003-08-31 2011-03-23 18:06:36.000 14.69
Jane Doe 2003-08-31 2009-12-21 14:28:49.000 16.60
John Doe 2002-09-27 2009-09-11 10:26:06.000 13.67
John Doe 2002-09-27 2011-10-10 10:29:13.000 14.69

so i would like to just see
Jane Doe 2003-08-31 2011-03-23 18:06:36.000 14.69
John Doe 2002-09-27 2011-10-10 10:29:13.000 14.69

what am i doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 12:16:59

[code]
select searchname, birthdate, obs_date, OBSVALUE
from
(
select row_number() over (partition by Name,birthdate order by o.OBSVALUE DESC) AS Seq,
pp.searchname, pp.birthdate, max(o.obsdate) as obs_date, o.OBSVALUE
from patientprofile pp
inner join document d on pp.pid = d.pid
inner join obs o ON o.SDID = d.SDID
inner join obshead h ON o.HDID = h.HDID
where h.NAME = 'BMI' and pp.Birthdate between '9-12-1994' and '9-12-2007'
group by pp.searchname, pp.birthdate , o.OBSVALUE
)t
where Seq=1
order by searchname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

murd
Starting Member

24 Posts

Posted - 2012-09-12 : 13:18:27

hmm...i get this

Msg 8120, Level 16, State 1, Line 1
Column 'obshead.NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'obshead.NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 13:21:27

[code]
select searchname, birthdate, obs_date, OBSVALUE
from
(
select row_number() over (partition by pp.searchname,pp.birthdate order by o.OBSVALUE DESC) AS Seq,
pp.searchname, pp.birthdate, max(o.obsdate) as obs_date, o.OBSVALUE
from patientprofile pp
inner join document d on pp.pid = d.pid
inner join obs o ON o.SDID = d.SDID
inner join obshead h ON o.HDID = h.HDID
where h.NAME = 'BMI' and pp.Birthdate between '9-12-1994' and '9-12-2007'
group by pp.searchname, pp.birthdate , o.OBSVALUE
)t
where Seq=1
order by searchname

[/code]

just use correct field name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

murd
Starting Member

24 Posts

Posted - 2012-09-12 : 13:26:27

YES! your the man thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 13:53:46

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

murd
Starting Member

24 Posts

Posted - 2012-09-12 : 14:50:46

one more question..

I am trying to add some more columns to this.

select searchname, birthdate, obs_date, OBSVALUE
from
(
select row_number() over (partition by pp.searchname,pp.birthdate order by o.OBSVALUE DESC) AS Seq,
pp.searchname, pp.birthdate, max(o.obsdate) as obs_date, o.OBSVALUE, ob.OBSVALUE, obb.OBSVALUE
from patientprofile pp
inner join document d on pp.pid = d.pid
inner join obs o ON o.SDID = d.SDID
inner join obs ob ON ob.SDID = d.SDID
inner join obs obb ON obb.SDID = d.SDID
inner join obshead h ON o.HDID = h.HDID
inner join obshead hh ON ob.HDID = hh.HDID
inner join obshead hhh ON obb.HDID = hhh.HDID
where h.NAME = 'BMI' and hh.name = 'HEIGHT' and hhh.name = 'WEIGHT' and pp.Birthdate between '9-12-1994' and '9-12-2007'
group by pp.searchname, pp.birthdate , o.OBSVALUE, ob.OBSVALUE, obb.OBSVALUE
)t
where Seq=1
order by pp.searchname

it says OBSVALUE was specified multiple times for 't'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 14:54:34

you cant have multiple columns with same name. so give them alises


select searchname, birthdate, obs_date, OBSVALUE,OB_OBSVALUE,OBB_OBSVALUE
from
(
select row_number() over (partition by pp.searchname,pp.birthdate order by o.OBSVALUE DESC) AS Seq,
pp.searchname, pp.birthdate, max(o.obsdate) as obs_date, o.OBSVALUE, ob.OBSVALUE AS OB_OBSVALUE, obb.OBSVALUE AS OBB_OBSVALUE
from patientprofile pp
inner join document d on pp.pid = d.pid
inner join obs o ON o.SDID = d.SDID 
inner join obs ob ON ob.SDID = d.SDID 
inner join obs obb ON obb.SDID = d.SDID 
inner join obshead h ON o.HDID = h.HDID 
inner join obshead hh ON ob.HDID = hh.HDID 
inner join obshead hhh ON obb.HDID = hhh.HDID
where h.NAME = 'BMI' and hh.name = 'HEIGHT' and hhh.name = 'WEIGHT' and pp.Birthdate between '9-12-1994' and '9-12-2007' 
group by pp.searchname, pp.birthdate , o.OBSVALUE, ob.OBSVALUE, obb.OBSVALUE
)t
where Seq=1
order by pp.searchname

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

murd
Starting Member

24 Posts

Posted - 2012-09-12 : 15:01:38

oh ok great! learned something new! thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 15:37:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources