CODEWITHSUNDEEP
sql-serverviewsum
Kasra
Kasra

Reputation: 149

Max of a table into a column of a view

I'm trying to figure it out how to put a column of max of another table into a view table! so here is my tables:

TblProprtyDetails

id    Property_id    amount     situation
 1         1           152         true
 2         1           545         false
 3         2            5          false
 4         2           87          true

TblExperties

id    PropertyDetails_id    ExpertiesDate     ExpertiesPrice
 1             1             2015-10-01             54
 2             1             2015-11-15             546
 3             2             2016-01-05             6895
 4             2             2016-08-01             654

now I want to put Max of ExpertiesDate and sum of amount into a view in this structure:

id    Property_id    amount     situation    LastExpertiesDate

Upvotes: 1

Views: 40

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

select
id ,Property_id,amount,situation,max(ExpertiesDate) as lastexpertisedate
from
TblProprtyDetails t1
join
TblExperties t2
on t1.id=t2.id
group by
id ,Property_id,amount,situation

You also can use cross apply

select
    id ,Property_id,amount,situation,b.*
    from
    TblProprtyDetails t1
cross apply
(
select max(ExpertiesDate) as lastexpertisedate from table t2 where 
t1.id=t2.id) b

Upvotes: 1

Related Questions