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.
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?
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 ?
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.
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.
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.
Abhit_kumar
0 Points
53 Posts
Problem in sql logic
Feb 23, 2010 08:23 AM|LINK
Now i have a problem in InvoiceQty which is coming here.
The final result should be:-
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
shawpnendu
Contributor
4275 Points
749 Posts
Re: Problem in sql logic
Feb 23, 2010 11:04 AM|LINK
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.
MCTS
http://shawpnendu.blogspot.com
jaiprakash.b...
Member
118 Points
19 Posts
Re: Problem in sql logic
Feb 23, 2010 12:46 PM|LINK
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.
Abhit_kumar
0 Points
53 Posts
Re: Problem in sql logic
Feb 25, 2010 04:32 AM|LINK
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.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140285
Regards,
ABHI
kpyap
Contributor
5212 Points
989 Posts
Re: Problem in sql logic
Feb 25, 2010 08:41 AM|LINK
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
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?
Maybe you can explain further?
Abhit_kumar
0 Points
53 Posts
Re: Problem in sql logic
Feb 25, 2010 10:10 AM|LINK
Hello Kpyap,
Thanks for the reply.
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
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: Problem in sql logic
Feb 26, 2010 06:34 AM|LINK
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 ?
Kumar Harsh
Abhit_kumar
0 Points
53 Posts
Re: Problem in sql logic
Feb 26, 2010 10:48 AM|LINK
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
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: Problem in sql logic
Feb 27, 2010 05:25 AM|LINK
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.
Kumar Harsh
Abhit_kumar
0 Points
53 Posts
Re: Problem in sql logic
Mar 03, 2010 09:31 AM|LINK
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