0

I have a Table TBLmaterial where current Stock is defined for particular Materailid .

enter image description here

And i have a tbldrawingtable where required quantity is listed for a particular material . It should update present column as required quantity and status to Available if current stock is available .

for example : Materialid =2 have 4 requiredQTY in 102=drawid and 2 requiredQTY in 105=drawid . IT should update because total 6 CurrentStock is present for the material id 2.

enter image description here

As per latest update of code .below result will displayed which is correct.

enter image description here

If i check for material id 1 once again . it should update drawing id 107 for the material id 1 to Not available because no more current stock is available to update . my code below :

 Alter procedure dbo.FetchInto(@Matid int)
 as
 begin

 declare @CurrentStock int
 declare @required Int
 declare @present int

 select @CurrentStock=M.CurrentStock
 from Tblmaterial M
 inner join
 TblDrawingTable D
 on M.Matid=D.Matid
 where M.Matid =@Matid


select top 1 @required=d.RequiredQty from Tblmaterial M
inner join
TblDrawingTable D
on M.Matid=D.Matid
 where m.Matid =@Matid

select  top 1 @present=(m.CurrentStock-isnull(d.Present,0)) 
from TblDrawingTable D
inner join
Tblmaterial M
on D.Matid=m.Matid
where D.Matid=@Matid


if exists(select 1 from TblDrawingTable where Matid=@Matid and Present is 
null)
begin
if (@required<=@CurrentStock and @present >0)
UPDATE TblDrawingTable SET present=@required,status='Available' where Drawid 
= (select top 1 Drawid from TblDrawingTable where
Matid=@Matid) and Matid=@Matid
end    
else if(@present=@required)
update TblDrawingTable SET status='Not Available',Present=@present where  
Drawid <> (select top 1 Drawid from TblDrawingTable where
Matid=@Matid) and Matid=@Matid


 if exists(select 1 from TblDrawingTable where Matid=@Matid and Present is 
 null)
 begin
if (@required<=@CurrentStock and @present>0)
UPDATE TblDrawingTable SET present=(@CurrentStock-
@required),status='Available' where  Drawid <> (select top 1 Drawid from 
TblDrawingTable where
Matid=@Matid) and Matid=@Matid
end    
else if(@present=@required) 
update TblDrawingTable SET status='Not Available',Present=@present where  
Matid=@Matid
end
sampath acharya
  • 133
  • 1
  • 3
  • 14

1 Answers1

1

I didn't check your code, but if I understood correctly you need to:

  • collect all the existing material from drawings
  • compare with in stock
  • update consequentially

I would work with CTE

Edit: I add some partial code that can be used to solve the issue

;WITH ExistingMat AS (
  SELECT Matid, SUM(RequiredQty) ExistingQty
  FROM TBLDrawing
  GROUP BY Matid
),
CompareMat AS (
  SELECT m.*, ISNULL(e.ExistingQty, 0) ExistingQty
  FROM TBLMaterial m LEFT OUTER JOIN ExistingMat e ON m.Matid = e.Matid
)
SELECT d.*, c.CurrentStock, c.ExistingQty, CASE WHEN c.ExistingQty <= c.CurrentStock THEN 'Available' ELSE 'Not Available' END UpdStatus, ExistingQty UpdPresent
FROM TBLDrawing d INNER JOIN CompareMat c ON d.Matid = c.Matid
Simone
  • 1,828
  • 1
  • 13
  • 20
  • how to compare Individually please help me Simone – sampath acharya Nov 24 '17 at 15:06
  • its not existing.. its required qnty – sampath acharya Nov 24 '17 at 15:23
  • are you referring to the names? Feel free to change them as you like – Simone Nov 24 '17 at 15:49
  • No .just Material ID – sampath acharya Nov 24 '17 at 16:38
  • sorry I don't understand, please reformulate with more details – Simone Nov 25 '17 at 11:53
  • hi Simone . i have modified the code . please have a check . It should update present column as required quantity and status to Available if current stock is available . – sampath acharya Nov 27 '17 at 11:11
  • Hi Sampath, sorry but I cannot check your code just on the assumption "it should update". In my humble opinion that would be your task, and only after not being successful you can try to ask for help clarifying what you've done and where it is not working – Simone Nov 29 '17 at 13:20
  • I have modified by checking for different condition for current stock, required stock, present stock.for example:If i check for material id 1 once again . it should update drawing id 107 for the material id 1 to Not available because no more current stock is available to update . my code below : – sampath acharya Nov 30 '17 at 02:47
  • Hi Sampath, the first thing that jumps to my eyes is this: "select @required=RequiredQty from TblDrawingTable where Matid =@Matid" You are just getting the required quantity for one random row instead of summing up all the needed one as per the first rows of my code – Simone Nov 30 '17 at 09:10
  • second, in the main part you check for present to be null and then compare it to numbers???? – Simone Nov 30 '17 at 09:11
  • third, what does this aim to: "select top 1 @present=(m.CurrentStock-d.Present)" ---- random result from the whole table – Simone Nov 30 '17 at 09:13
  • general considerations, choose a typing style (Capital or not) and adhere to that, indent correctly your code – Simone Nov 30 '17 at 09:14
  • hi simone. Ya i will check for @required by summing . Thank you – sampath acharya Nov 30 '17 at 09:28
  • first time i will check for null to update first row of every first material id. that is updated as top 1 . then i am greater than zero , that is something is updated greater than zero to check for second row of same material id – sampath acharya Nov 30 '17 at 09:30
  • 80 % but failing in one scenario . please find modified Code . – sampath acharya Dec 05 '17 at 04:16