help to make this complex query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help to make this complex query

Author

Topic

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2009-04-27 : 07:15:30

Dear friends i m trying to make a query in my database which updates the counter fields. but i m not able to make such a complex query all by myself. please help me. The problem is given below.

i have two tables

P_I_and_items Invoices
Item_code Item_code
Pi_No Invoice_No
Order_Qty Qty
Dispatched Buyer_Code
Cancel
Buyer_Code
Rate

P_I_and_items table can have more then one record of same item in a same proforma with different rates. a buyer can have more then one proforma invoice but the rate should be change. an invoice is basically buyer based. we can put the qty for an item in a single invoice to satisfy all the proformas.

the example data for these table is given below:

P_I_and_items
Item_Code Pi_No Order_Qty Dispatched Cancelled Rate Buyer_code
A 1 50 0 0 15 MXX
A 1 25 0 0 10 MXX
A 2 200 0 20 15 MXX
B 2 40 0 0 24 MXX

Invoices

Item_code Invoice_No Qty Buyer_Code
A 23 155 MXX
B 23 40 MXX

My problem is that how to design a query in which dispatched field should be updated in P_I_and_items on the basis of buyer invoice but
Dispatched field should not have more qty then the (order-cancelled-dispatched) and if any qty left in invoice after dispatching from first p/I, it should be set off against next proforma

for example A item code with 155 qty should be distributed between the p/Is in such a way that no p/I have more dispatch then the (order-cancel-dispatch) .

Please help its urgent and if i m not able to clear myself then please ask me again.

thanks in advance

Shiv Preet

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2009-04-28 : 12:04:02

please any sql expert who could help me or guide me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 12:15:06

Help us help you by providing table layout and sample data in a format we can use.
See http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.63"
N 56°04'39.26"

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2009-04-29 : 00:37:48

Dear friend paso thanks for your help. but please tell me how can i give u data in table format. i mean i tried to create the post with tabular format but when i preview it mixes everything. I mean preview removes all the formatting. you can check my first post i have tried to given the data in tabular format only. but still if there is any better way then please tell me. thanks again for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 00:44:57

Use the code block, [ code ] to start a block (tag without spaces) and [ /code ] to end a code block (also without spaces in the tag).

E 12°55'05.63"
N 56°04'39.26"

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2009-04-29 : 01:22:02

[code]

i have two tables

P_I_and_items Invoices
Item_code Item_code
Pi_No Invoice_No
Order_Qty Qty
Dispatched Buyer_Code
Cancel
Buyer_Code
Rate

P_I_and_items table can have more then one record of same item in a same proforma with different rates. a buyer can have more then one proforma invoice but the rate should be change. an invoice is basically buyer based. we can put the qty for an item in a single invoice to satisfy all the proformas.

the example data for these table is given below:

P_I_and_items
Item_Code Pi_No Order_Qty Dispatched Cancelled Rate Buyer_code
A 1 50 0 0 15 MXX
A 1 25 0 0 10 MXX
A 2 200 0 20 15 MXX
B 2 40 0 0 24 MXX

Invoices

Item_code Invoice_No Qty Buyer_Code
A 23 155 MXX
B 23 40 MXX

My problem is that how to design a query in which dispatched field should be updated in P_I_and_items on the basis of buyer invoice but
Dispatched field should not have more qty then the (order-cancelled-dispatched) and if any qty left in invoice after dispatching from first p/I, it should be set off against next proforma

for example A item code with 155 qty should be distributed between the p/Is in such a way that no p/I have more dispatch then the (order-cancel-dispatch) .

[/code]

I hope this information will be sufficient for you my friend.

thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-29 : 02:26:02

Please also post code to create your table and to populate your table with relevant sample data.
See same topic as above http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
to do this.

E 12°55'05.63"
N 56°04'39.26"

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2009-04-29 : 02:58:48

Dear Friend

I have used the sql server management studio to create the table

'This is the design of table


P_I_and_items   table
 
field_name     Datatype

Pi_no          varchar (50)
Item_code      Varchar(50)
Order_Qty      bigint
Dispatched     bigint
Cancel         bigint 
Buyer_Code     varchar(3)
Rate           float




Invoices   Table

field_name    data type

Item_code     varchar(50)
Invoice_No    Varchar(50) 
qty           bigint 
Buyer_Code    varchar(3)

This was the structure of my table.

and friend i m asking that what code/query i should write to enter data in proper way. you can read my post again if my problem is unclear but basically i do not know any query through which i could setoff data of one record in invoices table against multiple records of P_I_and_items table.

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources