Solved

SQL correlated join query

Posted on 2012-04-01
4
434 Views
Last Modified: 2012-04-03
Hi,

I am trying the following query. Could you please give me some direction or answer?
I have 2 tables. The first one is CLIENTS with with client_number as the key and city or location. The second one is BILLING with biil_number as the key, Client_number, date_of_bill and billing_amount.

How can I get the following report with a single SQL (if possible)

            Client-number   Location    Sum(Amount)


Of course each client can have many bills in a pre_determined period of time.
If there is no billing records for a client ZERO shoud be displayed.
0
Comment
Question by:Enrique Gomez Esteban
[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
4 Comments
 
LVL 9

Accepted Solution

by:
OCDan earned 250 total points
ID: 37793653
select c.client_number,
c.location,
isnull(sum(billing_amount),0) 'Amount'
from CLIENTS c
LEFT JOIN BILLING b ON c.client_number = b.clientnumber
WHERE date_of_bill BETWEEN date1 and date2
GROUP BY c.client_number, c.location

Just change date1 and date2 with whatever you need, if you wanted the last 30 days then try this:
date_of_bill between DateAdd(day, datediff(day,0,getdate()-30), 0)
and DateAdd(day, datediff(day,0,getdate()), 0)

I like this method of comparing dates as it strips the time, and allows the query to be SARG-able resulting in a very quick query.
0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 37795015
Another classical way would be:

SELECT client.id, client.name, client.address, client.city, SUM(billing.amount) AS amount;
  FROM client, billing;
  WHERE client.id = billing.clientid;
  GROUP BY client.id;
  ORDER BY client.name;
  INTO CURSOR query

Open in new window

0
 
LVL 27

Expert Comment

by:CaptainCyril
ID: 37795017
SELECT client.id, client.name, client.address, client.city, SUM(billing.amount) AS amount;
  FROM client, billing;
  WHERE client.id = billing.clientid AND billing date >= dDateFrom AND billing date <= dDateTo;
  GROUP BY client.id;
  ORDER BY client.name;
  INTO CURSOR query

Open in new window

0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 37796683
With sqlengine>70 you will need to change group by to include all fields non aggregated.
Also changed to the given table and field names and included your demand to get 0 as sum for clients having no bills in the time period queried.

All you need to preset before running this query is two variables dDateFrom and dDateTo with the min and max date to query about:

SELECT clients.client_number;
   , clients.location;
   , SUM(NVL(billing.billing_amount,00000.0000)) AS sum_amount;
   FROM clients 
   LEFT JOIN billing on clients.client_number = billing.client_number;
   AND (billing.date_of_bill Between dDateFrom AND dDateTo);
   GROUP BY clients.client_number, clients.location;
   ORDER BY clients.client_number;
   INTO CURSOR curReport

Open in new window


I would suggest you also include filtering for paid bills, too, if you eg have a logical field for that or a date field like date_of_payment.

By the way this just is a simple join, no need for a correlated subquery here.

Bye, Olaf.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to create an alias for a column name in ORACLE sql ? 2 73
Help with Oracle IF statment 5 48
Loading flat file data in tables 2 99
SQL Syntax Question 9 56
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

732 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