# Problem in sql logic RSS

## 27 replies

Last post Mar 26, 2010 02:45 AM by kpyap

None

0 Points

53 Posts

### Problem in sql logic

```GRPOID GRPONo  GRPODate GRPOVendor  GRPOItemCode  GRPOTotQty

100 550 01/01/2010 ABC Corp ItemXXXA 25```
```InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem		InvoiceQty
11		100		02/02/2010	VDC Corp	ItemXXXA	15
22		200		03/02/2010	HJK CORP	ItemXXXA	25
33		300		05/02/2010	MMM CORP	ItemXXXA	35
44		400		06/02/2010	GGG corp	ItemXXXA	45```

```GRPOID GRPONo  GRPODate GRPOVendor  GRPOItemCode  GRPOTotQty

100 550 01/01/2010 ABC Corp ItemXXXA 25

InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem		InvoiceQty
11		100		02/02/2010	VDC Corp	ItemXXXA	15
22		200		03/02/2010	HJK CORP	ItemXXXA	25
33		300		05/02/2010	MMM CORP	ItemXXXA	35
44		400		06/02/2010	GGG corp	ItemXXXA	45```

Now i have a problem in InvoiceQty which is coming here.

The final result should be:-

```GRPOID GRPONo  GRPODate GRPOVendor  GRPOItemCode  GRPOTotQty

100 550 01/01/2010 ABC Corp ItemXXXA 25

InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem		InvoiceQty
11		100		02/02/2010	VDC Corp	ItemXXXA	15
22		200		03/02/2010	HJK CORP	ItemXXXA	10```

Means first it should compare the GRPOTotQty (25) with InvoiceQty (15) and put 15 at first row,and then it go to second row of invoiceqty, its 25. But first row and second row invqty is greater then GRPOTotQty hence it will keep 10 at second row of Inv qty and it will ignore all the below row of Invoice.

Thanks,
ABHI

Contributor

3091 Points

876 Posts

### Re: Problem in sql logic

Use left join instead of inner join Where in left side kept invoice table & in right side use GRPOdata with your other conditions like date.

Shawpnendu Bikash Maloroy
MCTS
www.codedisplay.com

Member

80 Points

19 Posts

### Re: Problem in sql logic

try using Left join... instead of inner join.. also upload me the procedure if u cudn't find out..along with the table structures

To help others, If the post helps you, plz click the 'Mark As Answer'

With Thanks & Regards,
JAI PRAKASH R,
Sr. Software Engineer.

None

0 Points

53 Posts

### Re: Problem in sql logic

Hello Frnds, i think u all are not clear with my requirements, please visit this thread its gives you the detailed information about my requirement.

Regards,

ABHI

Contributor

3281 Points

989 Posts

### Re: Problem in sql logic

Hi Abhi,

Is this question similar to http://forums.asp.net/t/1521779.aspx?

BTW, I think it should WHERE InvoiceDate > g.GRPODate. If using >= sign, I will result in multiple records if InvoiceDate equal to GRPODate

 InvoiceID InvoiceNo InvoiceDate InvoiceVendor InvItem InvoiceQty CorrectQty 11 100 2/2/2010 VDC Corp ItemXXXA 15 15 22 200 3/2/2010 HJK CORP ItemXXXA 25 10 22 200 3/2/2010 HJK CORP ItemXXXA 25 15 33 300 5/2/2010 MMM CORP ItemXXXA 35 35 44 400 6/2/2010 GGG corp ItemXXXA 45 45 15 333 7/2/2010 HJK CORP ItemXXXA 15 5

I cannot understand why it is 15 for third row.
You mean you need to insert a new row from second row but with CorrectQty is remaining of 15 (25 - 10)?

If there is the case, then you should have this row after InvoiceNo 333, is it correct?

 InvoiceID InvoiceNo InvoiceDate InvoiceVendor InvItem InvoiceQty CorrectQty 15 333 7/2/2010 HJK CORP ItemXXXA 15 95

Maybe you can explain further?

None

0 Points

53 Posts

### Re: Problem in sql logic

Hello Kpyap,

No its not same as question similar to http://forums.asp.net/t/1521779.aspx. we need to differntiate with GRPOID and their sales data.

Actually

Actaully the third row for COrrect qty = 15 has been adjusted for new GRPOID.

Because InvoiceTotalQty is 25, 10 would be adjusted to first GRPO and 15 would be adjusted to next GRPO.

We have to keep 10 in first GRPO beacuse if you sum 15+10=25, and GRPO TOTAL QTY is 25, so dont put more then ao in second row.

Its better if you go to forum mentioned by me in above post. The code given by visakh is helpful but its not working in the one the case.

I have provided the sample data for better analysis purpose.

Regards,
abhi

Star

9276 Points

3926 Posts

### Re: Problem in sql logic

with refrence to your last sample post in,
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140285
Posted - 02/24/2010 :  01:33:21,

My query is can't you first of all introduce identity id column in both the table to distinguish between two record and it will also help in loop.

Frankly,as soon as i saw your first sample there and got it and started working on it untill i saw your second post and didn't get it.
Even when similar records are added in any table,its own id will distinguish it.

Like I didn't get the second post,its output sample.
Anyway,when you join or even query on InvoiceData table on InvItem='ItemXXXA' and InvoiceDate<=''

you get 4 records and you hv actual qunatity(GRPOTotQty)=25 it need to be adjusted in InvoiceData.
Since you get 4 records and you can adjust in any of the four records. right ?
There is no particular criteria to choose from this four records. right ?

I think ,you will get itemcode and date from front end as parameter.
I would go for set base programming.like using while loop,declaring few variable calculating on it then storing them in temptable(if req).
and displaying.
keep plugging the error and logic while testing.

I am still trying to understand your second post there.

HAVE YOU POSTED THE SAMPLE CORRECT DATA ?WHY SAME KIND OF RECORDS GETTING REPEATED ?

Warm Regards
Kumar Harsh

None

0 Points

53 Posts

### Re: Problem in sql logic

yeah in second post, the sample data is correct.

Acutally based upon the itemcode matching  and GRPODate>=InvoiceDate we  need to fetch sales data.

So in some scenario one two more GRPO can be made for the same Item in first GRPO sales data, hence due to that duplication of sales records come, one for first GRPO and same sales data for next GRPO.

Im get stuck then onward, if u can then help me

Star

9276 Points

3926 Posts

### Re: Problem in sql logic

i)If problem solved then explain us.

ii) I am considering only your second sample data in that post.

iii)if second sample data is ok,then don't post data again,but kindly take pain to explain the requirement one again.
wht i hv understood is that,i hv pick record from "GRPOData" table and adjust its quantity in InvoiceData table.Am i right.
And when finally adjusted then according to your output data,i hv to show records from,
GRPOData all that adjusted and InvoiceData .

iv) Else wht is the criteria to pick record from GRPOData and look into InvoiceData.
GRPOData.GRPOItemCode=InvoiceData.InvItem and GRPOData.GRPODate>=InvoiceData.InvoiceDate
it fetch 15 records(which ever join you apply).
for given sample itemcode always match.
There are duplicate records in both sample data as well as output data.Right ?
Doubt
GRPOID is unique,so even if it is repeating,it will be considered one records.Same for InvoiceID.
If yes,then correct quantity is wrongly calcualted.

hope you are getting me.

Warm Regards
Kumar Harsh

None

0 Points

53 Posts

### Re: Problem in sql logic

hello harsh,

Thanks for giving time for my post. I will really thankful if u can do help for me.

please consider only my second post.

GRPOID Is unique

yes we need pick record from GRPOData table and adjust its quantity in InvoiceTable.

You are correct that i need to fetch data from InvoiceData table based upon the

condition GRPOData.GRPOItemCode=InvoiceData.InvItem.

There can be the duplicate Invoice data in multiple GRPOID.

My requirement is as follow:-

GRPOData is the table used for incoming items entry.

Suppose ITEM A comes in 50 Qty for GRPOID =111.

And InvoiceData is basically used for the items which we are sold.

So if we want that when ITEM-A has been sold and with how much qty.

Hence we need to match condition GRPOData.GRPOItemCode=InvoiceData.InvItem and GRPOData.GRPODate>=InvoiceData.InvoiceDate

becoz sales always be done for that item after Incoming of that item.

But if suppose one more GRPO has been made for the same item, in the same period of sales data of previous GRPO, then its not displaying correct.In that case VISAKH given code is not working.

Regards

Star

9276 Points

3926 Posts

### Re: Problem in sql logic

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

Contributor

3281 Points

989 Posts

### Re: Problem in sql logic

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 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.

None

0 Points

53 Posts

### Re: Problem in sql logic

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

Star

9276 Points

3926 Posts

### Re: Problem in sql logic

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

Contributor

3281 Points

989 Posts

### Re: Problem in sql logic

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.

None

0 Points

53 Posts

### Re: Problem in sql logic

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.

Contributor

3281 Points

989 Posts

### Re: Problem in sql logic

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.

None

0 Points

53 Posts

### Re: Problem in sql logic

Hello Kpyap,

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?

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

Contributor

3281 Points

989 Posts

### Re: Problem in sql logic

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.

None

0 Points

53 Posts

### Re: Problem in sql logic

Hi Kpyap,

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

Contributor

3281 Points

989 Posts

### Re: Problem in sql logic

Hi Abhi,

Glad that I'm getting you correct.

```set nocount on

-- 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, 500, '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
declare @toHold as bit

select @invCount = count(*) from @InvoiceData
set @counter = 0
set @currentSum = 0
set @toHold = 0

select GRPOID, GRPOItemCode, GRPODate, GRPOTotQty, cast(0 as bit) as isUsed
into #grpoTemp
from @GRPOData
order by GRPODate

-- prepare the output result
select
grp.*, row_number() over(order by InvoiceDate) as rowNo, inv.*, cast(0 as int) as CorrectQty
into #invTemp
from @InvoiceData inv
OUTER APPLY (select * from @GRPOData where 1=0) grp

-- loop thru the invoice data
while @counter <= @invCount
begin

if @toHold = 0 or @counter = @invCount
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
-- if no available GRPO data, go to next Inv Item
if @@rowcount < 1
begin
set @toHold = 0
goto nextInv
end

-- 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
if @toHold = 0
begin
update #invTemp
set CorrectQty = inv.InvoiceQty, GRPOID = grp.GRPOID, GRPONo = grp.GRPONo, GRPODate = grp.GRPODate,
GRPOVendor = grp.GRPOVendor, GRPOItemCode = grp.GRPOItemCode, GRPOTotQty = grp.GRPOTotQty
from #invTemp inv
OUTER APPLY (select * from @GRPOData where GRPOID = @currentGRPOID) grp
where rowNo = @counter

-- increment current sum
select @currentSum = @currentSum + InvoiceQty from #invTemp where rowNo = @counter
end

else
begin
select @currentSum = @currentSum + InvoiceQty
from #invTemp
where rowNo = @counter

insert into #invTemp (
GRPOID, GRPONo, GRPODate, GRPOVendor, GRPOItemCode, GRPOTotQty,
rowNo, InvoiceID, InvoiceNo, InvoiceDate, InvoiceVendor, InvItem, InvoiceQty, CorrectQty)
select grp.GRPOID, grp.GRPONo, grp.GRPODate, grp.GRPOVendor, grp.GRPOItemCode, grp.GRPOTotQty,
rowNo, InvoiceID, InvoiceNo, InvoiceDate, InvoiceVendor, InvItem, InvoiceQty,
CorrectQty = @currentSum
from #invTemp
OUTER APPLY (select * from @GRPOData where GRPOID = @currentGRPOID) grp
where rowNo = @counter

end

set @toHold = 0
end

else
begin
update #invTemp
set CorrectQty = @currentGRPOTotQty - @currentSum, GRPOID = grp.GRPOID, GRPONo = grp.GRPONo, GRPODate = grp.GRPODate,
GRPOVendor = grp.GRPOVendor, GRPOItemCode = grp.GRPOItemCode, GRPOTotQty = grp.GRPOTotQty
from #invTemp inv
OUTER APPLY (select * from @GRPOData where GRPOID = @currentGRPOID) grp
where rowNo = @counter

set @toHold = 1

-- reset sum total
select @currentSum = (@currentGRPOTotQty - @currentSum ) * -1

-- update usage flag
update #grpoTemp
set isUsed = 1
where GRPOID = @currentGRPOID

end

nextInv:

end
end

select * from #invTemp order by InvoiceDate, InvoiceId

drop table #invTemp, #grpoTemp

set nocount off```

Hope you can understand my code.

None

0 Points

53 Posts

### Re: Problem in sql logic

it will took some time to understand u r code, but i think u r ouput is ok now, but its not work when i have inserted GRPOdata for different item.

`select 100, 500, '08 Jan 2010', 'ABC Corp', 'ItemXXXBB', 20`

Its should check ItemXXXBB in salesdata table with date condition.

There can be same GRPONO for different items, means its unique in nature of GRPNO and ITEM.

GRPONo 550 have itemA

also GRPONo 550 can have ItemB.

We take first itemA and the get its sales data based upon the item match and date condition.

In real scenarion when i have tested its code its only executing..its takes approx 15 mins and then i stopped it.

In My GRPO table there is 656 rows and in sales data there is 6000 records. Is there any way to optimize this query because its takes too long time.

Sorry to make this thread so big :(

Contributor

3281 Points

989 Posts

### Re: Problem in sql logic

Hi Abhi,

I re-design the logic using cursor, hope it's ok for you.

Below is the SQL script for your reference:

```set nocount on

-- prepare data structure (start)
declare @GRPOData as table (
GRPOID int, -- this should be Primary Key
GRPONo int,
GRPODate datetime,
GRPOVendor varchar(20),
GRPOItemCode varchar(20),
GRPOTotQty int
)

declare @InvoiceData as table (
InvoiceID int,  -- this should be Primary Key
InvoiceNo int,
InvoiceDate datetime,
InvoiceVendor varchar(20),
InvItem varchar(20),
InvoiceQty int
)

insert into @GRPOData
select 100, 500, '01 Jan 2010', 'ABC Corp', 'ItemXXXA', 25
union
select 200, 550, '03 Feb 2010', 'DEF Corp', 'ItemXXXA', 100
union
select 300, 550, '03 Feb 2010', 'DEF Corp', 'ItemXXXB', 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)

-- prepare the output result
select
grp.*, InvoiceID, InvoiceNo, InvoiceDate, InvoiceVendor, InvItem, InvoiceQty, cast(0 as int) as CorrectQty
into #invTemp
from @InvoiceData inv
OUTER APPLY (select * from @GRPOData where 1=0) grp
WHERE 1=0

-- grpo variables
declare @currentGRPOID as int
declare @currentGRPOTotQty as int
declare @currentGRPODate as datetime
declare @currentGRPOItemCode as varchar(20)

-- invoice variables
declare @InvoiceID int,
@InvoiceNo int,
@InvoiceDate datetime,
@InvoiceVendor varchar(20),
@InvItem varchar(20),
@InvoiceQty int

-- other variables
declare @pendingInvNo as int
declare @currentSum as int

set @currentSum = 0

DECLARE grpo_cursor CURSOR FAST_FORWARD
FOR

SELECT GRPOID, GRPOItemCode, GRPODate, GRPOTotQty
FROM @GRPOData
ORDER By GRPOItemCode, GRPODate

OPEN grpo_cursor

FETCH NEXT FROM grpo_cursor
INTO @currentGRPOID, @currentGRPOItemCode, @currentGRPODate, @currentGRPOTotQty

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE invoice_cursor CURSOR FAST_FORWARD
FOR

SELECT InvoiceID, InvoiceNo, InvoiceDate, InvoiceVendor, InvItem, InvoiceQty
FROM @InvoiceData inv
WHERE InvItem = @currentGRPOItemCode
AND InvoiceDate >= @currentGRPODate
-- excluding those already in result table
AND NOT EXISTS (SELECT 1 FROM #invTemp i
WHERE i.InvoiceID = inv.InvoiceID -- all unique key should be here
AND i.InvoiceNo = inv.InvoiceNo
AND i.InvItem = inv.InvItem
AND i.InvoiceDate = inv.InvoiceDate)
UNION -- include invoice that it's quantity yet to be balanced
SELECT InvoiceID, InvoiceNo, InvoiceDate, InvoiceVendor, InvItem, MAX(InvoiceQty)
FROM #invTemp
WHERE InvItem = @currentGRPOItemCode
AND InvoiceDate >= @currentGRPODate
GROUP BY InvoiceID, InvoiceNo, InvoiceDate, InvoiceVendor, InvItem
HAVING SUM(CorrectQty) <> MAX(InvoiceQty)
ORDER BY InvoiceDate

OPEN invoice_cursor

FETCH NEXT FROM invoice_cursor
INTO @InvoiceID, @InvoiceNo, @InvoiceDate, @InvoiceVendor, @InvItem, @InvoiceQty

WHILE @@FETCH_STATUS = 0
BEGIN
-- GRPO total quantity is greater than as at invoice sum
if @currentGRPOTotQty > @currentSum + @InvoiceQty
begin
INSERT INTO #invTemp
select grp.*, @InvoiceID, @InvoiceNo, @InvoiceDate, @InvoiceVendor, @InvItem, @InvoiceQty,
CorrectQty = case when @pendingInvNo = @InvoiceID then @InvoiceQty + @currentSum else @InvoiceQty end
from @GRPOData grp
where GRPOID = @currentGRPOID -- if there are more columns to be unique for table GRPOData, include here

-- increment current sum
select @currentSum = @currentSum + @InvoiceQty

set @pendingInvNo = NULL -- reset pending invoice Id to NULL
end

else
begin
INSERT INTO #invTemp
select grp.*, @InvoiceID, @InvoiceNo, @InvoiceDate, @InvoiceVendor, @InvItem, @InvoiceQty,
CorrectQty = case when @pendingInvNo = @InvoiceID then @currentGRPOTotQty else @currentGRPOTotQty - @currentSum end
from @GRPOData grp
where GRPOID = @currentGRPOID -- if there are more columns to be unique for table GRPOData, include here

-- calculate remaining invoice amount to knock off
select @currentSum = (@currentGRPOTotQty - @currentSum) * -1

set @pendingInvNo = @InvoiceID -- this invoice Id is yet to have balance qty

goto nextInv
end

FETCH NEXT FROM invoice_cursor
INTO @InvoiceID, @InvoiceNo, @InvoiceDate, @InvoiceVendor, @InvItem, @InvoiceQty
END

nextInv:

CLOSE invoice_cursor
DEALLOCATE invoice_cursor

FETCH NEXT FROM grpo_cursor
INTO @currentGRPOID, @currentGRPOItemCode, @currentGRPODate, @currentGRPOTotQty
END

CLOSE grpo_cursor
DEALLOCATE grpo_cursor

select * from #invTemp --order by GRPOID, InvoiceDate, InvoiceId

drop table #invTemp

set nocount off```

The sample above is assuming GRPOID as primary key for @GRPOData and InvoiceID for @InvoiceData.

If you find duplication of the result table, change insert-select statement to #invTemp to have the unique columns defined for the table.

Also note that @pendingInvNo is used to store pending InvoiceID which InvoiceQty is yet to be balanced.
So, if InvoiceID is not the primary key, you may need to change some of the logic there.

Overview of the logic above is looping GRPO then find Invoice associate to it.
Compare both the quantity and adjust accordingly.

Hope it help.

None

0 Points

53 Posts

### Re: Problem in sql logic

since i was out of station, i will go through u r query and let u knw the outcome...:)

Thanks to help me.

Regards,

abhi

None

0 Points

53 Posts

### Re: Problem in sql logic

Hello Kpyap,

Your query is faster now, i have made GRPOID, GRPOITem made Primary key, as Primary key(GRPOID,GRPOItemCode). Same as InvoiceNo and Invoice item combination PK.

Your query is working correct when an GRPOID have single Item, please note that a single GRPOID can have multiple items and a single InvoiceNo also multiple items.

GRPOID 100 can have 5 multiple items(all are differernt i.e.unique)

ItemA

ItemB

ItemC

ItemD

ItemE

Hence first GRPONO goes to first item and then display its respective result, then it should go to next item for the same grpo and search to Inv table and display the respected result.

If this will work then mine expected output will 100% work.

Thanks to give alot of time in my post.

Regards,

abhi

Contributor

3281 Points

989 Posts

### Re: Problem in sql logic

Hi Abhi,

Please make a try to change the SQL using your defined primary key.

Please post if you find any issues.

None

0 Points

53 Posts

### Re: Problem in sql logic

i have changed it to PK, but not getting expected result yet

Contributor

3281 Points

989 Posts