Hi All,

I have two views
     TenantPayments - This holds tenant weekly rent payments to us
     OwnerPayments -  This holds monthly payments to property owner whom we pay

I'm trying to join these two views using the prop code column and get 297 rows when I run the select statement for a property with prop code 3856. The total no of rows should be 27

select ttp.[Trans Date],ttp.[Prop Code], ttp.[Weekly Amount] ,owp.Monthly_Total  from ownerpayment AS owp
LEFT OUTER JOIN tenantpayment ttp  ON ttp.[Prop Code] = owp.PROP_CODE
Where [Prop Code] = '3856'

What I really need is to sum the weekly payments ( i.e column weekly Amount) and get the Monthly_Total in the same row.

• Edited by jaggy99 1 hour 44 minutes ago added new info

You need to elaborate more on the join that is used between weekly amount and monthly total by "Prop Code". A "Prop Code" in ownerpayment may have multiple Prop Code with different month or so. Similarly in the other view as well. That is the reason why you are getting unwanted rows.

Aggregate the amount in both the view by Prop Code and then finally join it. i.e 1:1 relation.

Hi RSingh,

The tenantpayment view has weekly rent transactions for each property and the ownerpayment view has the total amount per property per month. As suggested im now aggregating the weekly transaction by month per property but for few months the total doesn't add up correctly. I have posted the SUM/Group function issue here
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d6c5ade1-8c6e-4258-9f94-85b62cea7cf1/sql-sum-and-group-by-function-wrong-result?forum=transactsql