General information
Category:
Coding, syntax & commands
>I do something like this:
>
>
>m.dStartDate={10/01/2000}
>m.dEndDate={10/31/2000}
>
>SELECT prenotes.bankname, prenotes.portfolio, ;
>SUM(IIF(!isnull(mid),1,0)) AS foundit, ;
>SUM(IIF(isnull(mid),1,0)) AS notfound, ;
> COUNT(prenotes.mid) AS tot_prnote ;
> FROM prenotes ;
> WHERE BETWEEN(prenotes.settdate, m.dStartDate, m.dEndDate) ;
> GROUP BY prenotes.bankname ;
> INTO CURSOR mis_prenotes
>
>
>I get a record with bankname,foundit,notfound,tot_prnote.
>
>If there are more than one bank I get a record for each bank. If a bank is missing, I get nothing. I want to get a record for all the banks that are in the table regardless of there being a numbers for it or not. For example, one record would have bank A with numbers in the fileds and Bank B would have all zeroes in it. Is this possible?
>
>Thank you in advance for your help! :-)
>randall
You can do a UNION of your original query and one that finds all records that don't match your selection criteria. I think the following will also work although it may be a bear on a big table
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
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only