[Not a question] find height ,weigt, patientID from table for each moth for patient having same date of birth

Question

1.00/5 (1 vote)

See more:

table below shows a view of patient and their heightweight table . from this table i want to select height, weight, PatientId with max date in each moth for each patient ,Patient must have same birth date

PatientID	Height	MeasuredDate	BirthDate....	Weight
1	150	12/12/2013	3/2/1987	60
1	150	2/12/2013	3/2/1987	64
1	150	24/12/2013	3/2/1987	59
2	160	12/12/2013	3/2/1987	65
2	160	19/12/2013	3/2/1987	63
2	160	26/12/2013	3/2/1987	60
2	160	5/12/2013	3/2/1987	66
1	150	5/11/2013	3/2/1987	67
1	150	12/11/2013	3/2/1987	66.5
1	150	19/11/2013	3/2/1987	66
1	150	26/11/2013	3/2/1987	67
2	150	12/11/2013	3/2/1987	67
2	150	4/11/2013	3/2/1987	68
2	150	19/11/2013	3/2/1987	67
2	150	29/11/2013	3/2/1987	68
3	160	12/12/2013	3/2/1987	65
3	160	19/12/2013	3/2/1987	63
3	160	26/12/2013	3/2/1987	60
3	160	5/12/2013	3/2/1987	66
4	160	26/12/2013	3/2/1989	60
4	160	5/12/2013	3/2/1989	66

ie result is

PatientID	Height	MeasuredDate	BirthDate....	Weight
1	150	24/12/2013	3/2/1987	59
2	160	26/12/2013	3/2/1987	60
1	150	26/11/2013	3/2/1987	67
2	150	29/11/2013	3/2/1987	68
3	160	26/12/2013	3/2/1987	60

patient 4 is omitted since birth date is different and maximum date for each month is taken for each patient and there is value each month
PLZ help

Posted 19-Dec-13 20:12pm

rajin kp

Updated 19-Dec-13 20:51pm

Sergey Alexandrovich Kryukov

v3

Add a Solution

Comments

Maciej Los 20-Dec-13 2:29am

"maximum date for each month is taken" - it's not true. Patients (1 and 2) are taken two times.

rajin kp 20-Dec-13 4:05am

sorry for my bad English . what i mean that in each month last entered value for each patient taken

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-12-19T20:36:00

Solution 1

Please, read my comment to the question first.

Try this:

SQL

SELECT t1.PatientID, t2.Height, t1.MeasuredDate, t1.BirthDate, t2.Weight
FROM (
    SELECT PatientID, BirthDate, MONTH(MeasuredDate), MAX(MeasuredDate) AS MeasuredDate
    FROM PatiensTbl
    GROUP BY PatientID, BirthDate, MONTH(MeasuredDate)
    ) AS t1 INNER JOIN PatiensTbl AS t2 ON t1.PatientId = t2.PatientId AND t1.MeasuredDate = t2.MeasuredDate

Posted 19-Dec-13 20:36pm

Maciej Los

Updated 19-Dec-13 20:37pm

v2

Comments

rajin kp 20-Dec-13 4:37am

tks Maciej

Maciej Los 20-Dec-13 6:31am

You're welcome ;)