select max(propno) as propno, max(bldgno) as bldgno, max(unitno) as unitno, max(leasestartdate) as leasestartdate, max(effectivedate) as effectivedate, sum(annualamount) as annualamount from mpw1 inner join mpwdemo on mpw1.leaserecno = mpwdemo.leaserecno group by mpw1.leaserecno;
Here max() doesnt means we are taking the maximum value always, it also means taking one value if there are many. Like three same emp
_no are there so if we take max(), it helps us to take one value..
That works but as you suspected the EffectiveDate's can differ, I guess key here for me is to get a single row result based on leaserecno and certno totaling annualamount....
This indicates that you want to show (at least) one column too much. When the EffectiveDate's can differ, you shouldn't include that column in your SELECT list.
You want the ANNUALAMOUNT, so it would be better to change EffectiveDateto to YEAR(EffectiveDate) in the SELECT and the GROUP BY clauses.
The same will hold for LEASESTARTDATE. I don't know the business rules behind these two columns, you may have to drop EffectiveDate altogether and use YEAR(LEASESTARTDATE). That is up to you to decide.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages