Need Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Query

Author

Topic

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-06-05 : 05:30:24

In one table iam having id,itemid,availableQuantity -Iam showing this details in grid.

In grid iam having another column Alloted Qty....So,If i enter data in alloted qty then

Click on save the data will be saved in one table (A) which is having all these fields and at the same time it should update remaining tables:

Such as B,C

then the trigger on storedprocedure i have to write so that while clicking save

in B table insert the same data but those column names are diff,Such as in A...

id,itemid,availableQuantity,allotedQty - A

No,id,Qty,Qtyalloted -B
In C Table (Main table),the fields are - we are having the same fields as in A but one extra field to know the CurrentQty (availableQty - Alloted Qty)
So,Based on the alloted Qty the currentQty Should be Updated

So While Inserting in A the data should be Updated in C
Intially both availableQty and currentQty are same ,But while inserting the data should be updated such as

If available qty is 14 , Alloted Qty In grid is 12
Initially in C id,itemid,availableQuantity, currentQty
then the data in C is initially

id -1
itemid-A
Available Qty-14
CurrentQty-14

Now from frontend 12 is given then the data in A :

So inserting the record in database such that

id,itemid,availableQuantity,allotedQty

1 A 14 12

In table B:

No,id,Qty,Qtyalloted

1 A 14 12

In Table C (i have to update the CurrentQty which is intially 14)

id -1
itemid-A
Orginal Qty-14
CurrentQty-14-12 =2

Thanks

ToniMarieM
Starting Member

6 Posts

Posted - 2008-06-06 : 13:29:25

You could do the insert and in it declare the formula (add the two columns as the name for the new column ==> (itemid+availqty as currentqty).

Example:

if (SELECT OBJECT_ID('tempdb..#table','U')) is not null -- clean up if needed
drop table #table

declare @table table (id varchar(2), itemid int, availqty int) -- test table 1

insert into @table
select '1',2,3
union all
select '2',3,5

select * from @table -- show contents

select *, itemid+availqty as currentqty into -- build second table adding column for sums
#table from @table

insert into #table -- do some inserts too
select id+1,itemid+2,availqty*3, itemid+availqty as currentqty from @table

select * from #table
order by id

drop table #table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 13:32:30

Isnt this same as

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104320

ToniMarieM
Starting Member

6 Posts

Posted - 2008-06-06 : 13:42:59

It sure looks similar. I was just going from the link on the team newsletter for Need Query.

Cross-postings are not very useful on most sites (like this one) I would think.

Toni

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources