?
Solved

Easy Question? How do I make a loop that takes each customer number from a table and places each of them in a set of queries?

Posted on 2003-03-04
2
Medium Priority
?
345 Views
Last Modified: 2008-03-06
I wrote a set of queries (all showed below) to get a list of customers with a credit status of "H" (Hold) and the total amount overdue for each of the customers. Then I wrote another set of queries that divides the total amount overdue into <30days,  31-60 days, 61-90 days, and >90 days.

What I would like to do is:
make a loop that places each customer number in the four queries at the bottom of the page and place all the results into one table (see example).

Desired output Example:

         <30      31-60     61-90     >90
100012
100118
100119
100450
106254
...


The following queries are what I came up with originally, but I have to individually plug in the customer_id for all the customers numbers if I want to see how much of the overdue amount is <30 days overdue, 31-60 days, etc.


/*Here are the queries that I have been using to get the amount overdue in a specific time period*/
--MY FOUR QUERIES

Select sum(total_amount) '<30' from table100
where customer_id = 100118
and days_overdue < 30

Select sum(total_amount) '31-60 'from table100
where customer_id = 100116
and days_overdue between 31 and 60

Select sum(total_amount) '61-90' from table100
where customer_id = 100116
and days_overdue between 61 and 90

Select sum(total_amount) '>09' from table100
where customer_id = 100116
and days_overdue > 90


I hope that my question is clear enough for someone to help me. I'm sure this is a simple procedure, but I'm just not sure how to do it because I started learning SQL about 1.5 months ago.

When answering the question, please provide as many comments as possible so that I can learn how to do loops on my own.

----------------------------------------------------------
Just in case it helps:
The following is all the code I used. You can probably tell from the code that I'm new at SQL.

--March-01-2003
/*Gathers balance and breaks up overdue balances by <30, 31-60, 61-90, and >90 For all customers with a credit_status of 'H'*/

/*These first two queries get all the customers with a credit status of 'Hold'
and sums the total amount due minus the total amount paid to give the customers Balance.*/

--Query #1

Drop table table1
Select m.customer_id, m.customer_name, m.credit_status, m.company_id,
sum(n.payment_amount + n.terms_amount + n.allowed_amount) Amount_paid
into Table1
from customer m inner join ar_receipts_detail n on
m.customer_id = n.customer_id
where m.credit_status = 'H' and
m.company_id <> '07'
and m.company_id = n.company_id
group by m.customer_id, m.customer_name, m.credit_status, m.company_id
order by m.company_id, m.customer_id

--Query #2

drop table table4
Select m.customer_id, m.customer_name, m.credit_status, m.company_id,
sum(n.total_amount) Due, m.amount_paid, sum(n.total_amount) - m.Amount_paid Balance
into table4
from invoice_hdr n inner join table1 m on m.customer_id = n.customer_id
where n.customer_id in (Select customer_id from table1) and n.company_no = m.company_id
group by m.customer_id, m.customer_name, m.credit_status, m.company_id, m.Amount_paid
order by m.company_id, m.customer_id

--Query #3

/*This query breaks down the info given in table4 and shows a detailed list of invoice numbers, their amount due, and the amount of days overdue.*/

Drop table table100
Select customer_id, bill2_name, company_no, invoice_no, total_amount,
DATEDIFF(day, net_due_date, getdate()) days_overdue
into table100
from invoice_hdr
where customer_id in (Select customer_id from table4)
and company_no <> '07'
order by company_no, customer_id, net_due_date

--Query #4
/*This query breaks down the info given in table4 and gives a detailed list of all the invoice numbers and how much was paid on each one.*/

drop table table101
Select customer_id, invoice_no, company_id,
sum(payment_amount + allowed_amount + terms_amount) Paid
into table101
from ar_receipts_detail
where invoice_no in (Select invoice_no from table100)
group by company_id, customer_id, invoice_no
order by company_id, customer_id
--Select * from table4

--Query #5

/*This query subtracts the amount paid on the invoices due.*/

Update table100
set total_amount = (total_amount - (paid))
from table101
where table100.invoice_no = table101.invoice_no

/*These 4 queries show the balance of overdue amounts and how many days they are overdue.*/

Select sum(total_amount) '<30' from table100
where customer_id = 100116
and days_overdue < 30

Select sum(total_amount) '31-60 'from table100
where customer_id = 100116
and days_overdue between 31 and 60

Select sum(total_amount) '61-90' from table100
where customer_id = 100116
and days_overdue between 61 and 90

Select sum(total_amount) '>09' from table100
where customer_id = 100116
and days_overdue > 90
0
Comment
Question by:Brian_Agee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 1

Accepted Solution

by:
AlbertoFrog earned 240 total points
ID: 8165423
You really want to try and avoid doing loops. It is possible to create a cursor on the server, but since a database is designed to loop through data, you may as well try and get it to do all the work.

So, the alternative is using subqueries. These aren't always very efficient themselves, but they can make a complicated query much simpler.

Each of your old queries becomes one column. The databse then 'loops' though the customer table, calculating the totals for each.

Believe me, doing it yourself would take a lot more effort and code. In general, if you need to loop through data then you shouldn't be doing it on the database.

Alberto


SELECT
customer_id,
(SELECT SUM(total_amount) FROM table100 subtab WHERE subtab.customer_id=maintab.customer_id AND days_overdue < 30) AS '<30',
(SELECT SUM(total_amount) FROM table100 subtab WHERE subtab.customer_id=maintab.customer_id AND days_overdue between 31 and 60) AS '31-60',
(SELECT SUM(total_amount) FROM table100 subtab WHERE subtab.customer_id=maintab.customer_id AND days_overdue between 61 and 90) AS '61-90',
(SELECT SUM(total_amount) FROM table100 subtab WHERE subtab.customer_id=maintab.customer_id AND days_overdue > 90) AS '>90'
FROM customer
0
 

Author Comment

by:Brian_Agee
ID: 8166731
Thanks for the comment Alberto. I appreciate that you took some time out of your day to answer my question!  I ended up getting the output I wanted a few days after I submitted the question, but the code I used is really rough and inefficient (but it works!!!!). I will play with the code you submitted to see if I can make everything run smother. Thanks again!
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The well known Cerber ransomware continues to spread this summer through spear phishing email campaigns targeting enterprises. Learn how it easily bypasses traditional defenses - and what you can do to protect your data.
Hey fellow admins! This time, I have a little fairy tale for you. As many tales do, it starts boring and then gets pretty gory. I hope you like it. TL;DR: It is about an important security matter, you should read it if you run or administer Windows …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question