I have two tables:

purchase (ItemId, PurchQuantity, PurchaseDate);
sales (ItemId, SalesQuantity, SalesDate);


With these two table could I able to find:

1. Quantity in Stock at any given date of any item.
2. Purchase and Sales and their OpeningStock and ClosingStock Report
Ordered by date


If i could not do it, can I maintain a table like this

StockLevel (ItemId, OpeningStock, ClosingStock, AdjustedQuantity,
TransactionType);

where transaction type = purchase/sales.

1. the problem i'm facing with this table is i want to update this
table each and every
time the purchase or sales table is updated.
2. purchase or sales transaction can be inserted, deleted or updated
for any date, this makes
this table more complex.
forex:

please give me any suggestions or better solutions,

Is there any smart way to handle these kinds of transactions.

thanks in advance

prabuinet schrieb:
And is this a homework assignment? You sure go about it like if it is.

Volker
--
For email replies, please substitute the obvious.

prabuinet wrote:

purchase (ItemId, PurchQuantity, PurchaseDate);
sales (ItemId, SalesQuantity, SalesDate);


With these two table could I able to find:

1. Quantity in Stock at any given date of any item.
YES
2. Purchase and Sales and their OpeningStock and ClosingStock Report
Ordered by date
YES

You can get both information using good old SQL. Hint: use grouping
and join.

Volker Hetzer wrote:
Thanks for response,

This is not an home work assignment, I just simplified my tables and
put it like this,
actually my real tables are:

create table PMPurchase(
PMPurchaseId integer primary key autoincrement,
RefNo varchar(50) unique,
SellerId integer,
PurchaseDate smalldatetime);

create table PMPurchaseDetails
(PMPurchaseDetailsId integer primary key autoincrement,
PMPurchaseId Integer,
ItemId Integer,
GodownId Integer,
Quantity Real,
Rate Real);

Create table StockLedger(
StockLedgerId integer primary key autoincrement,
ItemId integer,
GodownId integer,
TransId integer,
TransDate SmallDateTime,
OpeningBalance Integer,
ClosingBalance Integer,
AdjustedQuantity Integer,
RecordType Integer);

create index ixall on StockLedger
(TransDate, ItemId, GodownId, TransId, RecordType);


With these tables, it seem to be very difficult to maintain the
stockledger table, I tried to write trigger for this:

Create trigger trg_PmPurchDet_Ins
after insert on PMPurchasedetails
begin
insert into StockLedger
(ItemId, GodownId, TransId, TransDate, OpeningBalance, ClosingBalance,
AdjustedQuantity, RecordType)
values
(new.ItemId, /* Item Id */
new.GodownId, /* Godown Id */
new.PMPurchaseDetailsId, /* TransId */
(select PurchaseDate from PMPurchase where PMPurchaseId =
new.PMPurchaseId), /* TransDate */
coalesce ((select coalesce(ClosingBalance, 0) from StockLedger where
/* Opening Balance */
ItemId=new.ItemId and GodownId=new.GodownId and TransId
=
(select TransId from StockLedger where TransDate <= (select
PurchaseDate from PMPurchase where PMPurchaseId=new.PMPurchaseId) order
by TransDate Desc, TransId Desc limit 1)), 0),

coalesce ((select coalesce(ClosingBalance, 0) from StockLedger where
/* Closing Balance */
ItemId=new.ItemId and GodownId=new.GodownId and TransId
=
(select TransId from StockLedger where TransDate <= (select
PurchaseDate from PMPurchase where PMPurchaseId=new.PMPurchaseId) order
by TransDate Desc, TransId Desc limit 1)), 0) + new.Quantity,

new.Quantity, /* Adjusted Quantity */

2); /* Record Type */

update StockLedger set
OpeningBalance = OpeningBalance + new.quantity,
ClosingBalance = ClosingBalance + new.quantity
where TransDate > (select PurchaseDate from PMPurchase where
PMPurchaseId = new.PMPurchaseId)
and ItemId = new.ItemId and GodownId = new.GodownId;
end;

Create trigger trg_PmPurchDet_Upd
after update on PMPurchasedetails
begin
update StockLedger set
OpeningBalance = OpeningBalance - old.quantity,
ClosingBalance = ClosingBalance - old.quantity
where TransDate > (select PurchaseDate from PMPurchase where
PMPurchaseId = old.PMPurchaseId)
and ItemId = old.ItemId and GodownId = old.GodownId;

update StockLedger set
ItemId = new.ItemId,
GodownId = new.GodownId,
ClosingBalance = OpeningBalance + new.quantity,
AdjustedQuantity = new.Quantity
where TransId = old.PMPurchaseDetailsId and RecordType = 2;

update StockLedger set
OpeningBalance = OpeningBalance + new.quantity,
ClosingBalance = ClosingBalance + new.quantity
where TransDate > (select PurchaseDate from PMPurchase where
PMPurchaseId = old.PMPurchaseId)
and ItemId = new.ItemId and GodownId = new.GodownId;
end;

create trigger trg_PmPurchaseDet_Del
after delete on PMPurchasedetails
begin
update StockLedger set
OpeningBalance = OpeningBalance - old.quantity,
ClosingBalance = ClosingBalance - old.quantity
where (TransDate > (select TransDate from StockLedger where TransId =
old.PMPurchaseDetailsId and RecordType = 2 and ItemId = old.ItemId and
GodownId = old.GodownId)
(TransDate = (select TransDate from StockLedger where TransId =
old.PMPurchaseDetailsId and RecordType = 2 and ItemId = old.ItemId and
GodownId = old.GodownId)
and ItemId = old.ItemId and GodownId = old.GodownId;



delete from StockLedger where
TransId = old.PMPurchaseDetailsId and RecordType = 2;
end;

See how nasty my trigger looks, I'm using sqlite.

My problem is whether it would affect the speed of the application...
or is there any other neat way to do this?

Volker Hetzer wrote:

Nah, he's just some guy in India who isn't really qualified to do your
job, and wants you to do it for him so he can get paid instead of you.

Bob Badour wrote:
hi Bob,

I accept your comments, every one is working to earn money, I know i'm
inexperienced to do this job. Due to my lack of experience i got a
doubt, and i asking for help from others, but I dont want others to do
my job.

May be the way i'm asking my question would look getting work from
others.
Since i'm not good in english, it happens...
If it seems that i'm wasting your time, i'm sorry ...

i'm new to database programming,
i don't want to say,
but the way u commented made me to tell this,
I have designed an operating system by my own, if u can try it :-)

Prabu

prabuinet schrieb:
What are the balances?
And what is the godownid?

Lots of Greetings!
Volker

PS: To those who complain about indians doing "their" jobs:
- If you work at US company X and X's management goes and hires
in india, why do you complain to the indians? Shouldn't you
talk to your own management instead?
- If your management hires in india, why should the indians refuse?
- If an american asks in this group, does he get shitted by a bunch
of indians, complaining that he's underqualified and botches a job
indians could do better and which therefore should be theirs?
--
For email replies, please substitute the obvious.

Volker Hetzer wrote:

I don't work at US company X and my company's management doesn't hire in
India so none of that is relevant.


Indeed. And if they can find someone in the US or Europe dumb enough to
do the actual work for them, why should they refuse that either?


Nah! Sensible people ignore him just like the sensible people ignored
prabuinet. Of course, the people handing out answers to homework
assignments get shit on just like folks doing incompetent people's work
for them get ridiculed.

prabuinet wrote:

Hi Prabu,

I haven't done any OS design work in over 15 years, so I admit I am a
little rusty. I have done a little of it, and I was educated for it.

For the record, the west has no shortage of incompetent people
pretending to do database design, and I have never made any secret of
that or of my disdain for it. From time to time, one even shows up here
asking folks to do his job for him. So, the phenomenon is not in any way
restricted to Indians.

While I hope you learn data management theory _before_ practising it any
further, my comment was directed more at Volker. The usual response to
"I cannot do my job. Will you do it for me?" is silence--like the
silence you received from most of the regulars of the newsgroup.

Also, for the record, some of the world's best dbms implementers are
Indians who--without doubt--contribute greatly to the IIT's reputation
for worldwide excellence and who--also without doubt--have forgotten
more about data management than I will ever learn.

Bob Badour wrote:

Guess what a racist says when he gets busted?
Oh NO I am not racist, see my dog is black!!!