Link to home
Start Free TrialLog in
Avatar of Brian_Agee
Brian_Agee

asked on

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?

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
ASKER CERTIFIED SOLUTION
Avatar of AlbertoFrog
AlbertoFrog

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brian_Agee
Brian_Agee

ASKER

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!