Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
i have two table stock and product_order.in first table i having three columns item_code,product_name,stock_on_hand and in second table product_name,product_quan,check_bit.... i want that when the value of check_bit is change 0 to 1 then the stock_on_hand=(stock_on_hand+product_quan) ....what should i do
Posted 3-Oct-12 2:31am
Edited 3-Oct-12 19:03pm
v3
Comments
aspnet_regiis at 3-Oct-12 9:00am
   
Write a SQL trigger for update query
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

u should try like that :
select SUM(st.Prd_stock)Stock_Sum,SUM(prd.Prd_qnt)Product_Sum from stock st inner join prodcut prd on st.Prd_Name=prd.Prd_Name and prd.Prd_chk=1
  Permalink  
v2
Comments
Rashid Choudhary at 4-Oct-12 1:04am
   
not work properly
solanki.net at 4-Oct-12 1:17am
   
can u explain what problem r u facing ?
Rashid Choudhary at 4-Oct-12 1:23am
   
my table is
Product_name Product_quan Delivery_date Uname P_id Extend_date st_bit flag check_bit
b 1 2012-08-13 nupur123 135 2012-10-04 1 0 1
c 4 2012-08-22 nupur123 136 2012-10-03 0 0 1
b 1 2012-09-03 priya@21 143 2012-09-27 1 0 0
a 2 2012-08-31 priya@21 144 1900-01-01 0 0 0
c 5 2012-09-18 priya@21 145 1900-01-01 1 0 0
a 1 2012-09-03 nupur123 146 NULL 1 0 0
b 1 2012-09-04 nitin123 148 NULL 1 0 0
 

and second table is
 
item_code Product_name stock_on_hand
1 a 4
2 b 5
3 c 8
Reply
Rashid Choudhary - 1 sec ago
and my prob is that when check_bit is change 0 to 1 then in second table automatically
add product_quan with stock_on_hand
solanki.net at 4-Oct-12 1:30am
   
it means finally u want to show sum of these two column ?
Rashid Choudhary at 4-Oct-12 1:30am
   
yes
solanki.net at 4-Oct-12 1:37am
   
declare @Stock int ,@Product int,@Total int
set @Stock=(select SUM(st.Prd_stock) from stock st inner join prodcut prd on st.Prd_Name=prd.Prd_Name and prd.Prd_chk=1)
set @Product=(select SUM(prd.Prd_qnt)Product from prodcut prd inner join stock st on st.Prd_Name=prd.Prd_Name and prd.Prd_chk=1)
 
set @Total=@Stock + @Product
Rashid Choudhary at 4-Oct-12 1:35am
   
quantity add with stock on hand with same product name
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

select SUM(stock_on_hand) + SUM(prd.Prd_product_quan)as total_stock from stock st inner join prodcut prd on stock.product_Name=product.product_Name and product.product_chk=1
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 210
1 Richard MacCutchan 100
2 kbrandwijk 90
3 ProgramFOX 80
4 Mukesh Bhagat 65
0 Sergey Alexandrovich Kryukov 9,050
1 OriginalGriff 8,151
2 CPallini 2,613
3 Richard MacCutchan 2,221
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 4 Oct 2012
Copyright © CodeProject, 1999-2014
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