max date in select - 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 date in select

Author

Topic

eugz
Posting Yak Master

210 Posts

Posted - 2012-08-07 : 11:07:35

Hi All.
I have two table:
Request
=============================
Request_Id
,CustomerNo
,LName
,FName
,ServiceDate

and
Payment
=============================
Payment_Id
,Request_Id
,PaidDate
,Amount

I would like join both table and select all fields from Request table with max(PaidDate) from Payment table.
How it to do?

I tried like:

select 
r.Request_Id
,Customer_No
,LName
,FName
,s.Status
,x.EffDate
,Amount
from dbo.Request r,
	(SELECT Status_Id, Request_Id, MAX(PaidDate) AS EffDate
	  FROM dbo.Payments
	  GROUP BY Status_Id) x
join dbo.v_Status s
on x.Status_Id = s.Status_Id
where r.Request_Id = x.Request_Id

but that is wrong.

Thanks.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-07 : 11:26:29

what is the error you getting?

--------------------------
Joins are what RDBMS's do for a living

eugz
Posting Yak Master

210 Posts

Posted - 2012-08-07 : 11:29:57

Thanks for replay.
The error is:
"Column 'dbo.Payments.Request_Id' 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-08-07 : 11:35:03

dont mix old and new join syntaxes. use consistent method
Also i dont think relationship is proper. my guess is it would be something like below
if you can post sample data it might be more clear for us


select 
r.Request_Id
,Customer_No
,LName
,FName
,s.Status
,p.EffDate
,Amount
from dbo.Request r
INNER JOIN dbo.Payments p
ON p.Request_ID = r.Request_ID
INNER JOIN (SELECT Request_Id,Status_Id, MAX(PaidDate) AS EffDate
	  FROM dbo.Payments
	  GROUP BY Request_ID,Status_Id) x
ON  p.Request_Id = x.Request_Id
AND p.Status_Id = x.Status_Id
Inner join dbo.v_Status s
on p.Status_Id = s.Status_Id

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

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-08-07 : 11:36:22

you never selected Request_id inside the inner query

(SELECT Status_Id, MAX(PaidDate) AS EffDate
	  FROM dbo.Payments
	  GROUP BY Status_Id) x

and you are trying to call it using an an alia here

where r.Request_Id = x.Request_Id

try to add it inside the inner query, if this won't change the group by result.

--------------------------
Joins are what RDBMS's do for a living

eugz
Posting Yak Master

210 Posts

Posted - 2012-08-07 : 11:56:11

Base on visakh16 code I got result of select:

54 12345678911 Dou John Adjustment 2012/08/06 100.00
54 12345678911 Dou John Redetermination 2012/08/07 100.00

But I would like to get in select records only with latest PaidDate.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 11:58:29

quote:
Originally posted by eugz

Base on you code I got result of select:

54 12345678911 Dou John Adjustment 2012/08/06 100.00
54 12345678911 Dou John Redetermination 2012/08/07 100.00

But I would like to get in select record only with latest PaidDate.

Thanks.

how are tables related?
please post some data from tables in below format

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

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

eugz
Posting Yak Master

210 Posts

Posted - 2012-08-07 : 12:17:40

Hi visakh16.

That is tables and data
Payments
[Payment_Id] [bigint] IDENTITY(1,1) NOT NULL,
[Request_Id] [bigint] NULL,
[Status_Id] [bigint] NULL,
[PaidDate] [datetime] NULL,
[PaidAmount] [smallmoney] NULL

57 54 1719 NULL 2012-08-06 00:00:00.000 False
58 54 1728 NULL 2012-08-07 00:00:00.000 False

Request
[Request_Id] [bigint] IDENTITY(1,1) NOT NULL,
[CustomerNo] [varchar](11) NULL,
[LName] [varchar](50) NULL,
[FName] [varchar](50) NULL,
[ServiceDate] [datetime] NULL,
[Status_Id] [bigint] NULL,
[Amount] [smallmoney] NULL

54 12345678911 Dou John 2012-08-06 00:00:00.000 NULL 100.0000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 12:18:00

it can even be this

but I'm not sure unless you show me how the data is in your tables


select 
r.Request_Id
,Customer_No
,LName
,FName
,s.Status
,p.EffDate
,Amount
from dbo.Request r
INNER JOIN dbo.Payments p
ON p.Request_ID = r.Request_ID
INNER JOIN (SELECT Request_Id,MAX(PaidDate) AS EffDate
	  FROM dbo.Payments
	  GROUP BY Request_ID) x
ON  p.Request_Id = x.Request_Id
AND p.PaidDate = x.EffDate
Inner join dbo.v_Status s
on p.Status_Id = s.Status_Id

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

eugz
Posting Yak Master

210 Posts

Posted - 2012-08-07 : 15:05:55

Thanks. It works.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-07 : 15:09:53

welcome

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

eugz
Posting Yak Master

210 Posts

Posted - 2012-08-08 : 16:01:10

Hi visakh16.

I found problem. If Request table has new record and Payment table doesn't has record for that Request_Id, that new record not display in select. How to fix it?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 16:08:59

[code]
select
r.Request_Id
,Customer_No
,LName
,FName
,s.Status
,p.EffDate
,Amount
from dbo.Request r
LEFT JOIN
(
SELECT p.*
FROM dbo.Payments p
INNER JOIN (SELECT Request_Id,MAX(PaidDate) AS EffDate
FROM dbo.Payments
GROUP BY Request_ID) x
ON p.Request_Id = x.Request_Id
AND p.PaidDate = x.EffDate
)t
ON t.Request_ID = r.Request_ID
Left join dbo.v_Status s
on t.Status_Id = s.Status_Id
[/code]

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

eugz
Posting Yak Master

210 Posts

Posted - 2012-08-08 : 16:37:27

Hi visakh16.

p.EffDate is red wave underline with tooltip - The multi-part identifier "p.EffDate" could not be bound.
I tried to say x.EffDate but result the same.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 17:22:45

quote:
Originally posted by eugz

Hi visakh16.

p.EffDate is red wave underline with tooltip - The multi-part identifier "p.EffDate" could not be bound.
I tried to say x.EffDate but result the same.

Thanks.

it should be t


select 
r.Request_Id
,Customer_No
,LName
,FName
,s.Status
,t.EffDate
,Amount
from dbo.Request r
LEFT JOIN
(
SELECT p.*
FROM dbo.Payments p
INNER JOIN (SELECT Request_Id,MAX(PaidDate) AS EffDate
	  FROM dbo.Payments
	  GROUP BY Request_ID) x
ON  p.Request_Id = x.Request_Id
AND p.PaidDate = x.EffDate
)t
ON t.Request_ID = r.Request_ID
Left join dbo.v_Status s
on t.Status_Id = s.Status_Id

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

eugz
Posting Yak Master

210 Posts

Posted - 2012-08-08 : 17:27:10

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources