Problem in sql logic RSS

• • Reply
  

  KumarHarsh

  All-Star

  15155 Points

  3658 Posts

  Re: Problem in sql logic

  Mar 03, 2010 11:14 AM|LINK

  see,first of all,i made same table and same data in it.If you find it correct then, its 25% complete .I hv commented the part which is not complete.Also if you hv the same table name and its data then just run it in QA.
  

  CREATE TABLE #TranTable (RowNumber int IDENTITY (1, 1),GRPOID int ,GRPONo int,GRPODate datetime ,
  GRPOVendor varchar (50),GRPOItemCode varchar (50),GRPOTotQty int)

  insert into #TranTable(GRPOID, GRPONo, GRPODate, GRPOVendor, GRPOItemCode,GRPOTotQty)
  Select distinct GRPOID, GRPONo, GRPODate, GRPOVendor, GRPOItemCode,
  GRPOTotQty from GRPOData

  Declare @count int
  Declare @flag int
  Declare @TotQty int
  Declare @itemcode varchar (50)
  set @flag=1
  Select @count=count(*) from #trantable
  while(@flag<=@count)
  Begin
  Select @TotQty=GRPOTotQty,@itemcode=GRPOItemCode from #trantable where RowNumber=@flag
  --start Further calculation and adjustment here,(this part is incomplete)
  Select distinct a.InvoiceID, a.InvoiceNO,
  a.InvoiceDate, a.InvoiceVendor, a.InvItem, a.InvoiceQty from InvoiceData a,GRPOData b
  where a.InvItem=b.GRPOItemCode and a.InvoiceDate>=b.GRPODate and a.InvItem=@itemcode

  --End--

  set @flag=@flag+1
  End

  Warm Regards
  Kumar Harsh
  
• • Reply
  

  kpyap

  Contributor

  5212 Points

  989 Posts

  Re: Problem in sql logic

  Mar 04, 2010 05:10 AM|LINK

  Hi Abhi,

  I think I understand what you mean.

  Please try the query below, and whether it produce the result you want?
  

  -- prepare data structure (start)
  declare @GRPOData as table (
  	GRPOID int,
  	GRPONo int,
  	GRPODate datetime,
  	GRPOVendor varchar(20),
  	GRPOItemCode varchar(20),
  	GRPOTotQty int
  )
  
  
  declare @InvoiceData as table (
  	InvoiceID int,
  	InvoiceNo int,
  	InvoiceDate datetime,
  	InvoiceVendor varchar(20),
  	InvItem varchar(20),
  	InvoiceQty int
  )
  
  
  insert into @GRPOData
  select 100, 550, '01 Jan 2010', 'ABC Corp', 'ItemXXXA', 25
  union
  select 200, 550, '03 Feb 2010', 'DEF Corp', 'ItemXXXA', 100
  
  
  insert into @InvoiceData
  select 11, 100, '02 Feb 2010', 'VDC Corp', 'ItemXXXA', 15
  union
  select 22, 200, '03 Feb 2010', 'HJK Corp', 'ItemXXXA', 25
  union
  select 33, 300, '05 Feb 2010', 'MMM Corp', 'ItemXXXA', 35
  union
  select 44, 400, '06 Feb 2010', 'GGG Corp', 'ItemXXXA', 45
  union
  select 15, 333, '07 Feb 2010', 'HJK Corp', 'ItemXXXA', 15
  union
  select 17, 444, '07 Feb 2010', 'GGG Corp', 'ItemXXXA', 20
  ;
  -- prepare data structure (end)
  
  
  declare @invCount as int
  declare @counter as int
  declare @currentSum as int
  declare @currentGRPOID as int
  declare @currentGRPOTotQty as int
  declare @currentGRPODate as datetime
  
  
  select @invCount = count(*) from @InvoiceData
  set @counter = 0
  set @currentSum = 0
  
  
  select GRPOID, GRPOItemCode, GRPODate, GRPOTotQty, cast(0 as bit) as isUsed
  into #grpoTemp
  from @GRPOData
  order by GRPODate
  
  
  -- prepare the output result
  select
  	row_number() over(order by InvoiceDate) as rowNo, *, cast(0 as int) as CorrectQty
  into #invTemp
  from @InvoiceData inv
  
  
  -- loop thru the invoice data
  while @counter <= @invCount
  begin
  
  
  	set @counter = @counter + 1
  
  
  	-- get available GRPO data
  	select top 1 @currentGRPOID = GRPOID, @currentGRPOTotQty = GRPOTotQty, @currentGRPODate = GRPODate
  	from #grpoTemp
  	where GRPOItemCode = (select InvItem from #invTemp where rowNo = @counter)
  		and isUsed = 0 -- check availability
  	order by GRPODate, GRPOID
  
  
  	-- PO date should be less than or equal to invoice date
  	if @currentGRPODate <= (select InvoiceDate from #invTemp where rowNo = @counter)
  	begin
  		-- GRPO total quantity is greater than as at invoice sum
  		if @currentGRPOTotQty > (select @currentSum + InvoiceQty from #invTemp where rowNo = @counter)
  		begin
  			update #invTemp set CorrectQty = InvoiceQty where rowNo = @counter
  			-- increment current sum
  			select @currentSum = @currentSum + InvoiceQty from #invTemp where rowNo = @counter
  		end
  
  
  		else
  		begin
  			update #invTemp set CorrectQty = @currentGRPOTotQty - @currentSum where rowNo = @counter
  			-- insert adjustment row
  			insert into #invTemp
  			select null, InvoiceId, InvoiceNo, InvoiceDate, InvoiceVendor, InvItem, InvoiceQty,
  					@currentGRPOTotQty - (@currentSum + InvoiceQty)
  			from #invTemp
  			where rowNo = @counter
  			-- reset sum total
  			set @currentSum = 0
  			-- update usage flag
  			update #grpoTemp
  			set isUsed = 1
  			where GRPOID = @currentGRPOID
  		end
  	end
  end
  
  
  select * from #invTemp order by InvoiceDate, InvoiceId
  
  
  drop table #invTemp, #grpoTemp

  Hope it help.
   

  • Edited by kpyap on Mar 04, 2010 05:16 AM
• • Reply
  

  Abhit_kumar

  0 Points

  53 Posts

  Re: Problem in sql logic

  Mar 08, 2010 09:57 AM|LINK

  Thanks alot for u r reply.

  Please note that your Output is like it which is already done by me, can u plz see my second post at

  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140285(see second post).

  U r output is ok for my first post but now work for second post scenarion.

   

  rowNo InvoiceID   InvoiceNo   InvoiceDate  InvoiceVendor      InvItem  InvoiceQty  CorrectQty
  1 11  100 2010-02-02  VDC Corp ItemXXXA 15 15
  2 22  200 2010-02-03  HJK Corp ItemXXXA 25 10
  NULL 22  200 2010-02-03  HJK Corp ItemXXXA 25 -15
  3 33  300 2010-02-05  MMM Corp ItemXXXA 35 35
  4 44  400 2010-02-06  GGG Corp ItemXXXA 45 45
  5 15  333 2010-02-07  HJK Corp ItemXXXA 15 15
  6 17  444 2010-02-07  GGG Corp ItemXXXA 20 5
  NULL 17  444 2010-02-07  GGG Corp ItemXXXA 20 -15

  
   

  Mine output should be like this:-

  
   

  GRPOID  GRPONo GRPODate GRPOItemCode  GRPOTotQty InvoiceID InvoiceNO InvoiceDate InvoiceVendor InvItem InvoiceQty CorrectQty
  100 550 01/01/2010 ItemXXXA 25 11 100 02/02/2010 VDC Corp ItemXXXA 15 15
  100 550 01/01/2010 ItemXXXA 25 22 200 03/02/2010 HJK CORP ItemXXXA 25 10
  200 555 03/02/2010 ItemXXXA 100 22 200 03/02/2010 HJK CORP ItemXXXA 25 15
  200 555 03/02/2010 ItemXXXA 100 33 300 05/02/2010 MMM CORP ItemXXXA 35 35
  200 555 03/02/2010 ItemXXXA 100 44 400 06/02/2010 GGG corp ItemXXXA 45 45
  200 555 03/02/2010 ItemXXXA 100 15 333 07/02/2010 HJK CORP ItemXXXA 15 5

  
   

  
  Correct Qty is not still not coming correct, when there is different GRPO no comes for same item in same sales period.

  Regards,
  abhi

  
   

• • Reply
  

  KumarHarsh

  All-Star

  15155 Points

  3658 Posts

  Re: Problem in sql logic

  Mar 08, 2010 10:27 AM|LINK

  wht about trying my script with your.
  In my script where i hv commented(--),there you can do the calculation part.
  There you get each distinct records from GRPOData,you can calculate and again put it into temp table.
  
  I am not getting time,else i would hv tried.

  Warm Regards
  Kumar Harsh
  
• • Reply
  

  kpyap

  Contributor

  5212 Points

  989 Posts

  Re: Problem in sql logic

  Mar 09, 2010 02:27 AM|LINK

  Hi abhi,

  I'm referring to your second post in SQL Team forum.

  Can you explain the result below:
  15 333 07/02/2010 HJK CORP ItemXXXA 15 5

  Why the CorrectQty is 5?
  I think 100 - (35 + 45 + 15)  > 0, it should show 15.

   

  • Edited by kpyap on Mar 09, 2010 02:29 AM
• • Reply
  

  Abhit_kumar

  0 Points

  53 Posts

  Re: Problem in sql logic

  Mar 09, 2010 11:59 AM|LINK

  Hello Kpyap,

   

  I think u r going in wrong direction.

   

  Please see GRPOID 100 and GRPONO 550 ITS GRPO TOTQTY IS 25 AND ITS DATE IS 01/01/2010.

  so grpoNO 550 SHOULD HAVE SALES DATA EQUAL AND AFTER 01/01/2010.

   

  SO BASED UPON THIS CONDTION, FOLLOWING INVOICE NO HAS BEEN FOUND.

  100

  200

  200

  300

  400

  333

  444

   AND CORRESPONDING QTY IS :

  15 25 25 35 45 15 20

  SINCE grpo NO HAVE TOT QTY ONLY HAVE 25 SO IT TAKES ONLY TWO INVOICENO 100 AND 200 AND  PUT CORRECT QTY AS 15 AND 10. For invoice no 200 it invoice qty is 25 but since it would be more then grpono 550 hence only 10 would be consider and remaining 15 would be adjusted into next grpo no 555 with 15 correct qty.

   

  If you go closely to my sample data then u will get an full idea that whats mine requirement.

   

   

• • Reply
  

  kpyap

  Contributor

  5212 Points

  989 Posts

  Re: Problem in sql logic

  Mar 10, 2010 02:56 AM|LINK

  Hi Abhi,

  Thank you for your explaination. I really overlook 3rd row which using GRPNo: 555.

  May I know why Invoice No: 333 is having CorrectQty: 5?
  I think 100 - (15+35+45+15) = -10.

   

• • Reply
  

  Abhit_kumar

  0 Points

  53 Posts

  Re: Problem in sql logic

  Mar 10, 2010 05:41 AM|LINK

  Hello Kpyap,

  Thanks for replying me back.

  Please note that correct qty should come based upon first unique GRPONO and its total qty.

  As per your question that why InvNo 333 having correct qty is 5?

  the answer is that:-

  Please look the GRPONo 555, its bring sales data = on and after 03/02/2010.

  Hence Invoice would be

  200
  300
  400
  333

  And note that invoice no 200 is already we have adjusted in GRPONo 500,  but we have adjusted only 10 qty against GRPONO 500, GRPOtotal qty 25.

  Hence remaining 15 qty is pending for Invoice no which is has been used for Inv no 555 and its tot qty=100

  So actaul sales data qty would be now:-

  200 15
  300 35
  400 45
  333

  Please note that we GRPOTotQty never be less then INVQty, hence correct qty would never b negative.When we add 15+35+45=95, so remaining 5 only we need to adjust agaist GRPO 555.

  so 100 - (15+35+45) = 5, so 5 we need to put for Inv 333.

  So now Invno 333 correct qty should be 5 and reamining its 10 qty would be adjusted against other GRPONo for same item after this GRPO.

  I think this would me now more helpful.

  If u see mine sample data output at second post at sqlteam, you get an why its required.

  Thanks,
  Abhi

   

• • Reply
  

  kpyap

  Contributor

  5212 Points

  989 Posts

  Re: Problem in sql logic

  Mar 11, 2010 12:18 AM|LINK

  Hi Abhi,

  Thank you for the clarification. I think I get the clear picture now. Hopefully...

  Below is the SQL for your reference:
  <Please refer latest post for the code>

  You may run it and see whether it produce your required output.

  Hope it help.

   

  • Edited by kpyap on Mar 11, 2010 07:48 AM
• • Reply
  

  Abhit_kumar

  0 Points

  53 Posts

  Re: Problem in sql logic

  Mar 11, 2010 05:17 AM|LINK

  Hi Kpyap,

  Thanks for the reply.

  Your correct qty output seems to be correct.

  But see this insert command used by you in ur query.

  insert into @GRPOData
  select 100, 550, '01 Jan 2010', 'ABC Corp', 'ItemXXXA', 25
  union
  select 200, 550, '03 Feb 2010', 'DEF Corp', 'ItemXXXA', 100

  
  you have inserted same grpono 550 with tow different qty 25 and 100.

  GRPO No always be unique.

  Hence please consider first GRPONo as 500 and second as 550.

  Also im not getting the output of GRPOData, im only getting sales data.

  Because from output i need to verify that for particular GRPO No what would be the sales data.

  I think u get me correct, please let me know if possible.

  Regards,

  ABHI