The Business Logic
Before writing code, we must understand the requirements. Our system needs to track:
The Source: Which Agency is supplying the stock? (e.g., "Fresh Fruits Co.")
The Transaction:
What is the Total Bill Amount?
How much was paid immediately (Initial Amount)?
How much is pending (Balance Amount)?
When was the balance finally cleared (Balance Date)?
To achieve this, we use a One-to-Many Relationship between the Agency and their Bill Details.
The Database Schema
We need two tables to handle this effectively.
Agency Table (Master): Stores the static details of the agencies.
Agency_Bill_Details Table (Transaction): Stores the stock arrival, payments, and balance dues.
Table 1: Agency (Master)
create table Agency22(
Agencyid Nvarchar(50) primary key,
Name varchar (20))
Table 2: Agency Details table.
create table Agency010(
ORDER_ID Nvarchar(50)primary key ,
Agencyid Nvarchar(50) FOREIGN KEY REFERENCES Agency22(Agencyid),
DATE DATETIME DEFAULT SYSDATETIME(),
NAME Varchar (20),
TOTALBOX int ,
AMOUNT int ,
INITIALAMOUNT int,
INITIALDATE DATETIME,
BALANCEAMOUNT int NULL,
BALANCEDATE DATETIME NULL,
fruitid NVARCHAR(MAX) FOREIGN KEY REFERENCES fruit1(fruitid))
Scenario A: Stock Arrival & Initial Payment
Let's say we receive 100 boxes of Apples worth ₹50,000. We pay ₹20,000 immediately.
The Logic: Balance = Total Amount - Initial Amount
Query:
insert into Agency010(Agencyid,NAME,TOTALBOX,AMOUNT,INITIALAMOUNT,
INITIALDATE,BALANCEAMOUNT,BALANCEDATE) VALUES('A001','bcd','125',
'630000','601100','2025-5-12','15000','2025-5-13')
Scenario B: Clearing the Dues
A week later, we pay the remaining balance. We need to update the record to reflect that the debt is cleared.
Query:
UPDATE Agency_Bill_Details
SET
Balance_amount = 0.00, -- Debt cleared
Balance_date = GETDATE() -- Date of final payment
WHERE
ORDER_ID = 1; -- Targeting the specific order
Top comments (0)