Max Date by group not returning if null

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 2000 Forums

Transact-SQL (2000)

Max Date by group not returning if null

Author

Topic

bonaboard
Starting Member

7 Posts

Posted - 2009-11-22 : 21:32:36

My query returns the MAX CLINICALDATE for the group but if the MAX CLINICALDATE is null then it won't return that GROUP.

SELECT PERSON.MEDRECNO, MEDICATE.INSTRUCTIONS, PERSON.LASTNAME, PERSON.FIRSTNAME, MEDICATE.STARTDATE, MEDICATE.STOPDATE, MEDICATE.STOPREASON, MEDICATE.DESCRIPTION, PRESCRIB.REFILLS, PRESCRIB.QUANTITY, PRESCRIB.CLINICALDATE
FROM
((ML.MEDICATE MEDICATE
INNER JOIN
ML.PERSON PERSON
ON (MEDICATE.PID = PERSON.PID))
LEFT OUTER JOIN
ML.PRESCRIB PRESCRIB
ON (MEDICATE.PID = PRESCRIB.PID) AND (MEDICATE.MID = PRESCRIB.MID))
LEFT OUTER JOIN
ML.USRINFO USRINFO
ON (PRESCRIB.PVID = USRINFO.PVID)
WHERE PERSON.MEDRECNO='20-14-38' AND (MID, CLINICALDATE) IN
(SELECT MID, MAX(CLINICALDATE) FROM PRESCRIB GROUP BY MID) and (STOPREASON is null or STOPREASON = ' ') and (MEDICATE.STOPDATE = to_date('4700-12-31 00:00:00', 'yyyy/mm/dd hh24:mi:ss'))
ORDER BY MEDICATE.DESCRIPTION

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-22 : 21:55:14

what is that mysql?

my guess is that you need to replace the NULL values with a known value...

this is a MS SQL Server forum. Might have better luck posting on a mysql forum.

SQL Server would return the values as expected in similar case

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-23 : 03:34:59

[code]SELECT PERSON.MEDRECNO,
MEDICATE.INSTRUCTIONS,
PERSON.LASTNAME,
PERSON.FIRSTNAME,
MEDICATE.STARTDATE,
MEDICATE.STOPDATE,
MEDICATE.STOPREASON,
MEDICATE.DESCRIPTION,
PRESCRIB.REFILLS,
PRESCRIB.QUANTITY,
PRESCRIB.CLINICALDATE
FROM ML.MEDICATE AS MEDICATE
INNER JOIN ML.PERSON AS PERSON ON MEDICATE.PID = PERSON.PID
LEFT JOIN ML.PRESCRIB AS PRESCRIB ON MEDICATE.PID = PRESCRIB.PID
AND MEDICATE.MID = PRESCRIB.MID
LEFT JOIN ML.USRINFO AS USRINFO ON PRESCRIB.PVID = USRINFO.PVID
INNER JOIN (
SELECT MID,
MAX(CLINICALDATE) AS t
FROM PRESCRIB
GROUP BY MID
) AS w ON w.MID = MID AND w.t = CLINICALDATE
WHERE PERSON.MEDRECNO = '20-14-38'
AND (STOPREASON IS NULL OR STOPREASON = '')
AND MEDICATE.STOPDATE = '47001231'
ORDER BY MEDICATE.DESCRIPTION[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources