External data query using MAX/SUM

[amarch00]

I'm trying to create an external data link using Microsoft Query. How
do I write SQL in Microsoft query that will return a max date and sum
an amount field for the max date only?

Current SQL is as follows:
SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME,
Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT),
Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS)
FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DMN
CB_PORTFOLIO_DMN
WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID
GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME

This returns the sum of the exposure amount for all rows, not just the
max post date.


--
amarch00Posted from - http://www.officehelp.in

[Ron Coderre]

Not knowing what kind of database you're using I did 2 things to try to help:

1)I built a simple model of the technique using Excel "tables"
2)I edited your SQL to make it resemble what you should try to achieve.

1)
The XL list is named myTable and has 2 columns: myDate and Amount.

To get the MaxDate as a usable field, I embedded a subquery in the SQL.
Here's the SQL I put in MS Query:

SELECT
myTable1.myDate,
SUM(myTable1.Amount) AS myAmtSum
FROM `C:\Excel Stuff\ForumHelp\QryReturnDatesMatchingMaxDate`.myTable
myTable1,
(SELECT Max(myTable2.myDate) AS MaxDate
FROM `C:\Excel Stuff\ForumHelp\QryReturnDatesMatchingMaxDate`.myTable
myTable2) tblMax
WHERE
myTable1.myDate = tblMax.MaxDate
GROUP BY
myTable1.myDate

2)My probably feeble attempt as correcting your code:
SELECT
CB_PORTFOLIO_DMN.PORTFOLIO_NAME,
CB_POOL_PORTFOLIO_1.POST_DATE,
Sum(CB_POOL_PORTFOLIO_1.EXPOSURE_AMT),
Count(CB_POOL_PORTFOLIO_1.NBR_AVG_MATY_YRS)
FROM
CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO_1,
CB06U.CB_PORTFOLIO_DMN CB_PORTFOLIO_DMN,
(SELECT MAX(CB_POOL_PORTFOLIO_2.POST_DATE) AS MaxDate
FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO_2) tblMax
WHERE
CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID
CB_POOL_PORTFOLIO_1.POST_DATE = tblMax.MaxDate
GROUP BY
CB_PORTFOLIO_DMN_1.PORTFOLIO_NAME,
CB_POOL_PORTFOLIO_1.POST_DATE

I suggest playing with the XL model first, then try to adapt the technique
to your "real" situation.

Comment: It may be possible to eliminate the subquery by using some form of
DMAX function, but I'm afraid I don't have enough time to work through that
scenario.

Good luck

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"amarch00" wrote:

>
> I'm trying to create an external data link using Microsoft Query. How
> do I write SQL in Microsoft query that will return a max date and sum
> an amount field for the max date only?
>
> Current SQL is as follows:
> SELECT DISTINCT CB_PORTFOLIO_DMN.PORTFOLIO_NAME,
> Max(CB_POOL_PORTFOLIO.POST_DATE), Sum(CB_POOL_PORTFOLIO.EXPOSURE_AMT),
> Count(CB_POOL_PORTFOLIO.NBR_AVG_MATY_YRS)
> FROM CB06U.CB_POOL_PORTFOLIO CB_POOL_PORTFOLIO, CB06U.CB_PORTFOLIO_DMN
> CB_PORTFOLIO_DMN
> WHERE CB_PORTFOLIO_DMN.PORTFOLIO_ID = CB_POOL_PORTFOLIO.PORTFOLIO_ID
> GROUP BY CB_PORTFOLIO_DMN.PORTFOLIO_NAME
>
> This returns the sum of the exposure amount for all rows, not just the
> max post date.
>
>
> --
> amarch00Posted from - http://www.officehelp.in
>
>