SQL query - 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

General SQL Server Forums

New to SQL Server Programming

SQL query

Author

Topic

Rens1976
Starting Member

2 Posts

Posted - 2010-02-24 : 21:51:57

Hello all
I'm running mysql 5.1
I need a query for my database. I'm a real newbie at SQL. I can do some basic select statements but I need something more complex.
I have 2 tables:
1) resident
2) weight

Resident has the following columns:
Resident_ID primary key and auto_incrament
Surname
First_Name
Upper_weight_limit
Lower_weight_limit
Active (check box. Value returned is 1 or 0)

Weight has the following columns:
Weight_ID primary key and auto_increment
Resident_ID
date
weight
Action_taken

Resident_ID field would be the JOIN field for the two tables.

End users of the database enter weights for residents every 2-6 weeks.
A second end user needs to be alerted if the weight meets certain criteria (the query that I need)so they can complete the Action_take field.
I require a query that will list for me the Surname, First_Name and a third column (described below)of all residents where Active=1 and Action_taken is Null who's last recorded weight is either above the Upper_weight_limit, Below the Lower_Weight Limit or who have lost more than 10% of their weight in the last 6 months.
As stated the query needs to return the resident's Surname, First_Name and a third column. The third column should tell the end user what criteria the result matches. For example:
Smith Joan weight>upper_weight_limit
Jones Frank weight loss > 10% over 6 months (keeping in mind weights are entered at random dates every 2-6 weeks).

I hope I have explained myself well.
Thanks in advance for all your help.
Regards
Rens

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-24 : 22:08:43

Hi, welcome to SQLTeam.

FYI, SQLTeam.com is a Microsoft SQL Server forum. For MySQL, please post over MySQL forum like at mysql.com or dbforums.com

KH
[spoiler]Time is always against us[/spoiler]

Sachin.Nand

2937 Posts

Posted - 2010-02-24 : 22:40:23

Try this


select First_Name +'' + surname,
case when W.weight>R.Upper_weight_limit then 'Upper_weight_limit'
     when W.weight<R.Lower_weight_limit then 'Lower_weight_limit'
     end as weighttype
 from resident R inner join weight W on W.Resident_ID=R.Resident_ID
where W.weight.date=
(
select top 1 weight.date from where W.Resident_ID=resident.Resident_ID
order by weight.date desc

)

I am just not quite to sure about the 6 months criteria.It will be more helpful if u can post some sample data.Please have a look at the link below to understand how to do it

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:21:00

quote:
Originally posted by Idera

Try this

select First_Name +'' + surname,
case when W.weight>R.Upper_weight_limit then 'Upper_weight_limit'
when W.weight<R.Lower_weight_limit then 'Lower_weight_limit'
end as weighttype
from resident R inner join weight W on W.Resident_ID=R.Resident_ID
where W.weight.date=
(
select top 1 weight.date from where W.Resident_ID=resident.Resident_ID
order by weight.date desc

)

I am just not quite to sure about the 6 months criteria.It will be more helpful if u can post some sample data.Please have a look at the link below to understand how to do it

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PBUH

I dont think you've TOP in MySQL. you need to use LIMIT instead

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 09:48:02

quote:
Originally posted by Idera

Try this

select First_Name +'' + surname,
case when W.weight>R.Upper_weight_limit then 'Upper_weight_limit'
when W.weight<R.Lower_weight_limit then 'Lower_weight_limit'
end as weighttype
from resident R inner join weight W on W.Resident_ID=R.Resident_ID
where W.weight.date=
(
select top 1 weight.date from where W.Resident_ID=resident.Resident_ID
order by weight.date desc

)

I am just not quite to sure about the 6 months criteria.It will be more helpful if u can post some sample data.Please have a look at the link below to understand how to do it

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PBUH

This can be converted to


select First_Name +'' + surname,
case when W.weight>R.Upper_weight_limit then 'Upper_weight_limit'
     when W.weight<R.Lower_weight_limit then 'Lower_weight_limit'
     end as weighttype
 from resident R inner join weight W on W.Resident_ID=R.Resident_ID
where W.weight.date=
(
select max(weight.date) from where W.Resident_ID=resident.Resident_ID
)

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:50:59

W.weight.date? what does that mean? isnt W just alias for table?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources