Max - query from two tables - 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

Max - query from two tables

Author

Topic

Hym1988
Starting Member

4 Posts

Posted - 2015-03-05 : 16:12:27

Hello,

Iv got a problem with my query,

[url]http://2imgs.com/7be4c1eb38[/url]

I want to say:
Pull all users whos status is New and they have at least one Completed delivery
The problem is , iam getting multiple delivery records form one patient, but I would like to see only newest one
So instead of this result:

2014-05-10 00:00:00.000 Completed Reed Brewer New 830
2014-06-10 00:00:00.000 Completed Reed Brewer New 830
2014-07-10 00:00:00.000 Completed Reed Brewer New 830
2014-07-02 00:00:00.000 Completed Colton Duke New 920

I would like to get this:

2014-07-10 00:00:00.000 Completed Reed Brewer New 830
2014-07-02 00:00:00.000 Completed Colton Duke New 920

I have tryed to use Max , but it does not work for me ;x

select 
		Max(delivery.deliveryDate),
		delivery.deliveryStatus,
		patient.userName,
		patient.userStatus,
		delivery.accountid
	  
from clx_delivery as delivery
	left join clx_patienttherapy as patient on delivery.accountid = patient.accountid
where deliveryStatus = 'Completed' and userStatus = 'New'

group by 
	delivery.accountid,
	delivery.deliveryDate,
	delivery.deliveryStatus,
	patient.userName,
	patient.userStatus,
	delivery.accountid

jleitao
Posting Yak Master

100 Posts

Posted - 2015-03-06 : 13:56:09

You need remove the deliverydate form the group by. you want the max date, you don't want "group" the records by deliveryDate.
And you are calling accountId twice in group by, it's not necessary.

select
Max(delivery.deliveryDate),
delivery.deliveryStatus,
patient.userName,
patient.userStatus,
delivery.accountid

from clx_delivery as delivery
left join clx_patienttherapy as patient on delivery.accountid = patient.accountid
where deliveryStatus = 'Completed' and userStatus = 'New'

group by
delivery.accountid,
delivery.deliveryStatus,
patient.userName,
patient.userStatus

------------------------
PS - Sorry my bad english

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources