Hi all,
I need sone help on generating a SQL statement
i had 3 tables now
Customer Name Acct No Address Phone
Invoice Invoice no Customer no
Invoice Details Invoice No Commence Date Expiry Date Amount
Every month, the customer will come in to pay for the bills. 1
seperate row is generated for each invoice.
I need to generate a report stating customer who had came in to pay
for the month of march (expiry date=31/3/2004) but still have not make
payment for the month of April (date commence >1/4/2004).
Can anyone help? 4 2444
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Right now, Customers have no relationship to Invoices.
Is this what you meant to post?
CREATE TABLE Customers
(cust_nbr INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(35) NOT NULL,
…);
CREATE TABLE Invoices
(invoice_nbr INTEGER NOT NULL PRIMARY KEY,
cust_nbr INTEGER NOT NULL
REFERENCES Customers(cust_ nbr)
ON UPDATE CASCADE
ON DELETE CASCADE);
CREATE TABLE InvoiceDetails
(invoice_nbr INTEGER NOT NULL
REFERENCES Invoices (invoice_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
commence_date DATETIME NOT NULL,
expiry_date DATETIME NOT NULL,
CHECK (commence_date < expiry_date),
amount DECIMAL (12,4) DEFAULT 0.00 NOT NULL,
PRIMARY KEY (invoice_nbr, commence_date)) ; customers who have paid for the month of march, but still have not
make payment for the month of April <<
SELECT C1.cust_nbr, C1.name
FROM Customers AS C1,
Invoices AS I1,
InvoiceDetails AS D1,
InvoiceDetails AS D1,
InvoiceDetails AS D2
WHERE C1.cust_nbr = I1.invoice_nbr
AND I1.invoice_nbr = D1.invoice_nbr
AND I1.invoice_nbr = D2.invoice_nbr
AND D1.commerce_dat e = '2004-03-01'
AND D2.commerce_dat e = '2004-04-01'
AND D1.amount > 0.00
AND D2.amount = 0.00
My first comment is that based on what you have described, the Invoice
table is redundant and should be combined with the Invoice Details
tables.
I post some [rough] DDL below:
create table Customer
(
C_Code varchar( 10 ),
C_Name varchar( 30 ) not null,
)
create table Invoice
(
C_Invoice_No varchar( 10 ) not null,
C_Customer_Code varchar( 10 ) not null,
C_Commences datetime not null,
C_Expires datetime not null,
C_Amount int
)
insert Customer( C_Code, C_Name ) values ( '001', 'Kevin' )
insert Customer( C_Code, C_Name ) values ( '002', 'Paul' )
insert Customer( C_Code, C_Name ) values ( '003', 'Roger' )
insert Invoice( C_Invoice_No, C_Customer_Code , C_Commences, C_Expires
)
values ( '1', '001', '01-March-2004', '31-March-2004' )
insert Invoice( C_Invoice_No, C_Customer_Code , C_Commences, C_Expires
)
values ( '2', '002', '01-March-2004', '31-March-2004' )
insert Invoice( C_Invoice_No, C_Customer_Code , C_Commences, C_Expires
)
values ( '3', '003', '01-February-2004', '28-February-2004' )
The query below assumes that:
a. The billing period is the same for all customers. That is, everyone
is billed from 1st of month to 31st (or last day) of the month. The
SQL below won't work unless this fact holds true.
b. The billing period has no breaks in it. That is, if the last day of
a billing period is 31st March, the commence date of the next period
is 1st April, not 2nd April etc.
select * from Customer
where C_Code in (
select distinct C_Customer_Code from Invoice
group by C_Customer_Code
having max( C_Expires ) = dateadd( dd, -1, '01-April-2004' )
)
Effectively '01-April-2004' is the date of your next "Commence" period
so this query simply looks for customers having an invoice with an
expiry date which matches the expiry date of the previous billing
period.
At the end of the day, however, you can vary the having clause to get
the type of result you need.
I suspect that I have misunderstood the requirement and resultant
answer in my original post. I assumed that there was no invoice
details entry yet. Guess that's what happens without DDL and sample
data...
Logistically, however, I wonder why a system would create an "Invoice
Details" record with a zero amount, so presumably there must be an
"Amount" column and an "Amount Paid" column?
>> I wonder why a system would create an "Invoice
Details" record with a zero amount, so presumably there must be an
"Amount" column and an "Amount Paid" column? <<
I agree; my mental model was a payment coupon book with one coupon per
month and the total amount due in the Invoices table. But even that is
awkward.
--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: LRW |
last post by:
I'm trying to get the difference between two dates, formatted in:
Time elapsed: X years, X months, X days
but I can't seem to get it right.
Here's my last attempt:
<?php
$oldtime = "2004-01-05";
$oldtime = explode("-", $oldtime);
$otY = $oldtime;
|
by: ecPunk |
last post by:
Hi,
We have a web application where we want a user to be able to change his/her
password if the password has expired but we are unable to do this with ASP
(at the moment) because we can't log the user into the database without a
valid password. We do not want to store any "admin" user info to connect to
the database to change the users password for security issues. Does anyone
have any ideas of how we could go about doing this? Any...
|
by: The Blob |
last post by:
Hi all,
I have a problem trying to generate the u/m list of customer.
I am trying to generate a list of customer whoes last commence date is
jan 04 to current.
It is part of a billing system which the customer come in and pay for
their season parking in carpark. They can pay for various period
shortest being 1 week.
|
by: Chris Langston |
last post by:
I have a Web Server running IIS 5 or 6 on Windows 2K and Windows 2003 Server
that is experiencing strange shutdown problems. We are using ASP.NET v1.1
and our application is written in VB.NET
Here's the scenario:
1. .NET Windows Client on a remote machine makes a web service call to
update tables on a Web Server running SQL Server 2000.
2. The Update is updating about 1000 - 3000 records doing simple update
statements like "Update...
|
by: vvenk |
last post by:
Hello:
I am working with VB.Net and Oracle 10g database on a Windows 20043 server.
I added a OleDBConnection to a form, configured the connection strings. And,
I added a OleDBDataAdapter and configured that o point to a single table with
the statement, "Select * from Customer". I Then created a dataset and
accepted the suggested dataset name dataset2.
I then dropped a Wingrid on the form and when I looked at the code, the
| |
by: rbronco28 |
last post by:
I am pretty new at doing web development. I can make simple pages, but recently received a request from a small limo business I do web development for.
We are currently working on a reservation form online for customers to fill out personal info, event details, and payment options. In the form we ask the customer to select what limo they want in addition to how many hours they want the limo for.
The problem I am running into is getting a...
|
by: Gibble |
last post by:
Hi,
We have a javascript on a page that calls a .NET web service. Oddly,
in our web servers event logs the following errors kept repeating
every minute (that made sense since the web service was on a minute
timer).
Event code: 4005
Event message: Forms authentication failed for the request. Reason:
The ticket supplied has expired.
|
by: sharsy |
last post by:
Hello guys,
I would like some help in generating query criteria that will identify
credit cards that have expired on an access database.
The specific Field is formatted with a Data Type of
'Text' and has an input mask of '00/00', so all expiry dates are set
out for example as 10/13 (which represents October 2013).
I have hada brief go at trying to work it out, but I was unable to
|
by: DanicaDear |
last post by:
I have set up a form to contain customer info. From that form, I have a button "Show Order" that links it to a Order Number form. That order number form contains a subform showing order details (qty, items). When I am on the customer info form and click SHOW ORDER it only shows me the orders associated with that particular customer number. (That is good.) But when I want to enter a new order, how can I get it to automatically fill in the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |