Re: QM Query to Break on Range of Days

Don't think you even need the CTE,

Just a CASE statmenent in the select, though the CTE might make it
easier to sort :)

Charles

On Thu, Aug 2, 2012 at 3:32 PM, Carel Teijgeler <coteijgeler@xxxxxxxxx> wrote:

Emily,

Use a CTE to mark accounts below 90 days and above 90 days.

Then use that mark to be a break level in the FORM.

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR ***********

On 2-8-2012 at 18:53 Emily Smith wrote:

I currently have a query management query that lists past due loans. It
is in days past due order with totals on balances and fees. It also breaks
on the number of days past due. The resulting report looks something like
this:
Account Current Balance Days Past Due
Total Late Fees Etc....
------------- -----------------------
---------------------- ---------------------- ---------
xxxxxx $1,500.00 21
$500.00
xxxxxx2 $1,200.00
$300.00
Total Loans 21 days past due: $2,700.00
$800.00
xxxxxx3 $750.00 23
$100.00
xxxxxx4 $200.00
$25.00
Total Loans 23 days past due: $950.00
$125.00
Final Totals: $3650.00
$925.00

Now the user wants to change this so that instead of breaking and totaling
on the number of days past due, it lists the past due loans in order with
loans 1 to 90 days past due as one group with totals followed by loans 90
days or older with totals and then of course final totals at the end.
Something like this:
Account Current Balance Days Past Due
Total Late Fees Etc....
------------- -----------------------
---------------------- ---------------------- ---------
xxxxxx $1,500.00 21
$500.00
xxxxxx2 $1,200.00 35
$300.00
Total Loans < 90 days past due: $2,700.00
$800.00
xxxxxx3 $750.00 93
$100.00
xxxxxx4 $200.00 120
$25.00
Total Loans > 90 days past due: $950.00
$125.00
Final Totals: $3650.00
$925.00
I know I can accomplish this by running two qm queries (or running one
twice with different parameters), outputting the results to a file. But
I'm curious if there is a way to accomplish what I want to do just within
the one query. Any advice, or even Google search terms (because nothing
I've used has returned what I'm looking for) would be appreciated. TIA


Emily Smith
Programmer/Analyst
BankData Services
317 West Park Street
Edwardsville, IL 62025
618-659-4550
Phone: 618-659-4550
Fax: 618-656-2406
esmith@xxxxxxxxxxxxxxxxxxxx
[BDS signature logo.jpg]

CONFIDENTIALITY NOTICE: This email message is private, confidential
property of the sender, and the materials may be privileged communications
intended solely for the receipt, use, benefit, and information of the
intended recipient, you are hereby notified that any review, disclosure,
distribution, copying, or taking of any other action in reference to the
contents of this message is strictly prohibited, and may result in legal
liability on your part. If you have received this message in error,
please notify the sender immediately and delete this message from your
system. We believe that this email and any attachments are free of any
virus or other defect that might affect any computer system that it is
received and opened in, however, it is the responsibility of the recipient
to ensure that it is virus free and the sender accepts no responsibility
for any loss or damage.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


-----
Geen virus gevonden in dit bericht.
Gecontroleerd door AVG - www.avg.com
Versie: 10.0.1382 / Virusdatabase: 2437/5167 - datum van uitgifte:

07/31/12



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.