Information générale
Catégorie:
Codage, syntaxe et commandes
>This gives me the one line again. The others are still missing.
I used the following test data
Record# BANKNAME SETTDATE MID PORTFOLIO
1 chase 01/01/00 1 mine
2 chase 02/01/00 1 mine
3 chase 03/01/00 1 mine
4 uaf 01/01/00 1 mine
5 uaf 01/01/00 .NULL. mine
6 uaf 03/01/00 1 mine
7 abc 03/01/00 1 mine
ran the following query
m.dstartdate={^2000-01-01}
m.denddate={^2000-02-15}
SELECT prenotes.bankname, prenotes.portfolio, ;
SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) AND !isnull(mid),1,0)) AS foundit, ;
SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) AND isnull(mid),1,0)) AS notfound, ;
SUM(IIF(BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate),1,0)) AS tot_prnote ;
FROM prenotes ;
GROUP BY prenotes.bankname ;
INTO CURSOR mis_prenotes
and got the following results
Record# BANKNAME PORTFOLIO FOUNDIT NOTFOUND TOT_PRNOTE
1 abc mine 0 0 0
2 chase mine 2 0 2
3 uaf mine 1 1 2
As Trey suggests, some sample data and expected results should help you get the answers you need.
One more thing.
I didn't create multiple portfolios within each bank because you did not include that field in the GROUP BY clause. If you have multiple portfolios within a bank your result will show the last portfolio for each bank.
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement