Click here to Skip to main content
13,141,637 members (58,661 online)
Rate this:
 
Please Sign up or sign in to vote.
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 5-Sep-12 22:41pm
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?
GoggatjieLiesl 6-Sep-12 4:58am
   
Busy studying, still new
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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:
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!!!
  Permalink  
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web01 | 2.8.170915.1 | Last Updated 15 Sep 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100