Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Good day everyone,

I need help im stuck again how do i write a sp on sql for folowwing tables content
Payments table
PaymentId
Amount
Paymnet date

Customer table
customerid
customername
customervatno

Cycle table
cycleid
cyclenumber
startdate
enddate

waybill table
waybillid
customername
waybillamount
waybilldate

now im stuck. i want to write a sp to link to my reportviewer in C#
I want user to input name of customer he want to generate a invoice with the cycle he wants.

The data must return al customer waybills data between cycle period, all the payments that customer made and be put into a table name Invoice.

Hope this makes sense im so lost at this moment.

Please can someone just assist me with following, or if i dont make any sense i will try to explain more

Thanks
Posted
Updated 5-Sep-12 22:43pm
v2
Comments
VIPR@T 6-Sep-12 4:48am    
Is that no any relations between this tables?
GoggatjieLiesl 6-Sep-12 4:51am    
no i dont have any relations, will i have to put relations?
Dylan Morley 6-Sep-12 4:53am    
yes
GoggatjieLiesl 6-Sep-12 4:57am    
ok i will do so and how do i go from there then
Oshtri Deka 6-Sep-12 4:57am    
Schema looks strange. Is this school or professional project?

1. This is truly a friendly advice and tone of this post is meant to be harsh, but not insulting.

2. It doesn't make much sense. Shouldn't there be connection between payments and waybills? Schema looks strange.

3. Do yourself a favor and invest time to learn more about relational databases and SQL.
You've made connection with attribute instead of FK connection.

Query for getting waybills looks like this:
SQL
DECLARE @startDate date, @endDate date;
SET @startDate = (SELECT StartDate FROM Cycle WHERE CycleID = @CycleID);
SET @endDate = (SELECT EndDate FROM Cycle WHERE CycleID = @CycleID); 

--waybills you want
SELECT WB.* FROM WayBill WB
INNER JOIN Customer C
--this is just wrong, but this your database
ON WB.CustomerName = C.CustomerName
WHERE WB.CustomerName = @CustomerName 
AND 
WB.WayBillDate >= @startDate
AND
WB.WayBillDate <= @endDate


I can't make Invoices for Customer because there isn't connection between Customers and Payments!!!
 
Share this answer
 
v2
Comments
GoggatjieLiesl 6-Sep-12 5:12am    
Hey, I wil link my tables now with relationships but thank you very much for your answer it gives me a good idea on were to begin. Thanks again
CREATE TABLE [dbo].[Waybills](
[WaybillID] [int] IDENTITY(1,1) NOT NULL,
[SenderName] [varchar](50) NULL,
[SenderAddress] [varchar](50) NULL,
[SenderContact] [int] NULL,
[ReceiverName] [varchar](50) NULL,
[ReceiverAddress] [varchar](50) NULL,
[ReceiverContact] [int] NULL,
[UnitDescription] [varchar](50) NULL,
[UnitWeight] [int] NULL,
[DateReceived] [date] NULL,
[Payee] [varchar](50) NULL,
[CustomerID] [int] NULL,
PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[customerName] [varchar](30) NULL,
[CustomerAddress] [varchar](30) NULL,
[CustomerContact] [varchar](30) NULL,
[VatNo] [int] NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
)
CREATE TABLE [dbo].[Cycle](
[CycleID] [int] IDENTITY(1,1) NOT NULL,
[CycleNumber] [int] NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Payments](
[PaymentID] [int] IDENTITY(1,1) NOT NULL,
[Amount] [money] NULL,
[PaymentDate] [date] NULL,
[CustomerID] [int] NULL,
PRIMARY KEY CLUSTERED

Create table Invoices
(
InvoiceID int IDENTITY(1,1),
InvoiceNumber int,
InvoiceDate date,
BalanceBroughtForward money,
OutstandingAmount money,
CustomerID int,
WaybillID int,
PaymentID int,
CycleID int
PRIMARY KEY (InvoiceID),
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID),
FOREIGN KEY (WaybillID) REFERENCES Waybills(WaybillID),
FOREIGN KEY (PaymentID) REFERENCES Payments(PaymentID),
FOREIGN KEY (CycleID) REFERENCES Cycle(CycleID)
)

This is my new tables with relationships, does it look better?\\Will you be able to help me now with SP
 
Share this answer
 
Comments
Sandeep Mewara 15-Sep-12 16:07pm    
This is not an answer. Please use 'Have a Question or Comment' link to respond to an answer. It will notify answerer of your comment such that he can come back and reply. Or if needed, use Improve Question link to edit/update your question at anytime.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900