I have 2 tables,

MPW1400
LeaseRecNo
PropNo
BldgNo
UnitNo
LeaseStartDate

MPW1482
LeaseRecNo
EffectiveDate
AnnualAmount
CertNo

My Objective is to get the total of 'AnnualAmount' Where MPW1400.LeaseRecNo = MPW1482.LeaseRecNo and CertNo = '1'

Example:

MPW1400
-------------------------------------------------------
LeaseRecNo | PropNo | BldgNo | UnitNo | LeaseStartDate
-------------------------------------------------------
12345 | RG52 | 24 | 5A | 12/25/2008
12346 | RG52 | 24 | 5B | 01/22/2007
12347 | RG52 | 24 | 3A | 08/05/2004
12348 | RG52 | 24 | 2A | 10/19/2008
12349 | RG52 | 24 | 1A | 11/16/2006
12350 | RG52 | 24 | 1B | 04/09/2001
-------------------------------------------------------

MPW1482
-------------------------------------------------------
LeaseRecNo | CertNo | AnnualAmount | EffectiveDate
-------------------------------------------------------
12345 | 1 | 3015. | 12/25/2008
12345 | 1 | 400.45 | 12/25/2008
12345 | 1 | 6000. | 12/25/2008
12345 | 2 | 1800. | 12/25/2008
12345 | 3 | 24000. | 12/25/2008
12345 | 1 | 9000. | 12/25/2008
-------------------------------------------------------

So the Desired Query would result in the following output;

PropNo | BldgNo | UnitNo | LeaseStartDate | EffectiveDate | AnnualAmount
-------------------------------------------------------------------------
RG52 | 24 | 5A | 12/25/2008 | 12/25/2008 | 18415.45

I have put together many different combinations of select statemets and subselects to only get mixed results non of which is my desired result.... Help?

Try this:

select
propno,
bldgno,
unitno,
convert(varchar(12), leasestartdate, 101) as leasestartdate,
convert(varchar(12), effectivedate, 101) as effectivedate,
sum(annualamount)
from mpw1400 a
inner join mpw1482 b
on a.leaserecno = b.leaserecno
where b.certno = 1
group by
propno,
bldgno,
unitno,
convert(varchar(12), leasestartdate, 101),
convert(varchar(12), effectivedate, 101)

It should do the trick.

I get an err "The second parameter LeaseStartDate for CONVERT is invalid." with that query....

Might be because of the way the column datatype is defined. In my test, it was a datetime datatype. If yours is a varchar, just don't use the "convert" function in either the "select" clause or the "group by" clause

I may be doing something wrong still, this is what I have for the query,

SELECT
PropNo,
BldgNo,
UnitNo,
LastName,
FirstName,
LeaseStartDate,
EffectiveDate,
sum(AnnualAmount) AS TotalAmount
FROM
MPW1400 a
INNER JOIN
mpw1482 b
ON
a.LeaseRecNo = b.LeaseRecNo
WHERE
b.CertNo = 1
GROUP BY
PropNo,
BldgNo,
UnitNo,
LastName,
FirstName,
LeaseStartDate,
EffectiveDate
ORDER BY PropNo,BldgNo,UnitNo ASC

And this is what I get for a result;

WED69     46        1136      Scruggs                                          Maissha                  2/11/2009     2/4/2009                   2088.0
WED69     47        1116      Hudgens                                          Baletha                   3/9/2007    1/31/2007                  2947.88
WED69     47        1116      Hudgens                                          Baletha                   3/9/2007     2/2/2007                   4218.0
WED69     47        1116      Jefferson                                        Matika                   5/14/2007    5/14/2007                  20404.6
WED69     47        1122      Fakir                                            Fatima                   11/1/2005    11/1/2005                   2484.0
WED69     48        1102      Brown                                            Charinease               11/1/2005    11/1/2005                  15070.0
WED69     48        1102      Craig                                            Felicia                  9/22/2008    8/28/2008                   2088.0
WED69     48        1102      Craig                                            Felicia                  9/22/2008    8/29/2008                   5004.0
WED69     48        1106      Bradley                                          Folonda                  11/1/2005    11/1/2005                  12000.0
WED69     49        1103      Dunbar                                           Terone                  12/21/2009    9/23/2009                   9334.0
WED69     49        1103      Johnson                                          Jameela                   2/1/2006     2/1/2006                  11400.0
WED69     49        1103      Renfro                                           Reginald                 6/15/2007    6/11/2007                  15600.0
WED69     49        1103      Renfro                                           Reginald                 6/15/2007    6/14/2007                   6000.0
WED69     49        1105      Evans                                            Dione                     6/1/2006     6/1/2006                  14560.0

Notice the records that arent get totaled...maybe because the have different cert dates?

I know in my example I showed all the Effective dates as the same but they are different even with the same CertNo

I appreciate you helping figure this out!

Is cert date the same as EffectiveDate? If so, yes you are correct. If you remove "EffectiveDate" from the grouping and the column list, then it will total.

THATS AWESOME! but how do I display the earliest Effective Date in the same query?

Just include "min(effectivedate) as MinEffDate" before the sum(annualamount)

That should do the trick.

commented: Perfect Imeddiate Accurate Answers!!! +1
commented: Perfect as always. +8

Perfect! here is my final code if anyone is interested.....

SELECT
PropNo,
BldgNo,
UnitNo,
LastName,
FirstName,
LeaseStartDate,
MIN(EffectiveDate) AS CertDate,
sum(AnnualAmount) AS TotalAmount
FROM
MPW1400 a
INNER JOIN
mpw1482 b
ON
a.LeaseRecNo = b.LeaseRecNo
WHERE
b.CertNo = '1'
GROUP BY
PropNo,
BldgNo,
UnitNo,
LastName,
FirstName,
LeaseStartDate
ORDER BY
PropNo,
BldgNo,
UnitNo
ASC
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.