Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
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
Medium Priority
374 Views
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
Question by:Brian_Agee

LVL 1

Accepted Solution

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

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

Question has a verified solution.

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

What happened when I tried to make my phone, a Samsung Galaxy S8, stop dropping all WiFi signals was a saga I won't repeat here, but what you might want to know is how it started and how I got it working again.
I’m willing to make a bet that your organization stores sensitive data in your Windows File Servers; files and folders that you really don’t want making it into the wrong hands.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses
Course of the Month12 days, 1 hour left to enroll