# Problem in sql logic RSS

## 27 replies

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

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

4275 Points

749 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
http://shawpnendu.blogspot.com

Member

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

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

5212 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?

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

All-Star

15133 Points

3647 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

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

All-Star

15133 Points

3647 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

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