Problem in SQL query - SQL Server Forums

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)

Problem in SQL query

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-23 : 04:09:19

Hello Experts,

I am facing problem in this type of scenario,i have made one stored procedure for getting mine output.

I need to fetch values based upon the two tables and these two table are not linked with any type of relationship.

First Table:- GRPOData
Second Table:- InvoiceData

GRPOData Table gives following output:-

GRPOID GRPONo 	GRPODate	GRPOVendor 	GRPOItemCode 	GRPOTotQty

100	550	01/01/2010	ABC Corp	ItemXXXA	25

InvoiceData Table gives following output:-

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

GRPOData Table is for Particular Item incoming entry
and Invoice Data table is used for the containing sales data for item.

So i want to know based upon the particular GRPONO 550 we recived ItemXXXA for 30 qty
what shoulb the sales data i.e InvNo, InvQty etc for this paricular ItemXXXA for 30 qty by using this tables.

I have made inner join between this table by taking GRPOItemCode=InvItem, as excpet of that there is no relationship. And added one more condition to fetch values from InvoiceData table that the sales data should be have condition that invoiceDate =>GRPODate.Because by taking the incoming entry of Item, then only salesData has been made for particular item.

So now its showing data in this manner:-

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:47:58

seems like this


SELECT i.InvoiceID,
i.InvoiceNO,
i.InvoiceDate,
i.InvoiceVendor,
i.InvItem,
CASE WHEN i.InvoiceQty + i1.InvTotal > g.GRPOTotQty 
     THEN g.GRPOTotQty - i1.InvTotal
     ELSE i.InvoiceQty
END
FROM GRPOData g
CROSS APPLY (SELECT *
             FROM InvoiceData
             WHERE InvoiceDate > g.GRPODate
             AND InvItem =g.GRPOItemCode) i
OUTER APPLY (SELECT SUM(InvoiceQty) AS InvTotal
             FROM InvoiceData
             WHERE InvItem = i.InvItem
             AND InvoiceDate < i.InvoiceDate
            )i1
WHERE  i1.InvTotal <= g.GRPOTotQty

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-24 : 01:33:21

Thanks a lot visakh for your help. I have modified your query by adding ISNULL at IsNull(SUM(InvoiceItemQty),0)
and this condition WHERE InvoiceDate >= g.GRPODate in your condiotion.

Sorry I forget to mention one condition here, that if suppose in the same period one more GRPOID is made for the same itemcode and
the GRPO Date comes on the period of precious GRPOID sales data then sales data should come in some other manner. Please see below.

Sample Data(without using your code) - Your code is fine up to one GRPO case.

GRPOID	GRPONo 	GRPODate	GRPOVendor 	GRPOItemCode 	GRPOTotQty
100	550	01/01/2010	ABC Corp	ItemXXXA	25
100	550	01/01/2010	ABC Corp	ItemXXXA	25
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100

InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem		InvoiceQty
11		100		02/02/2010	VDC Corp	ItemXXXA	15
22		200		03/02/2010	HJK CORP	ItemXXXA	25
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
15		333		07/02/2010	HJK CORP	ItemXXXA	15
17		444		07/02/2010	GGG corp	ItemXXXA	20

Output should be like this:-

GRPOID	GRPONo 	GRPODate	GRPOVendor 	GRPOItemCode 	GRPOTotQty
100	550	01/01/2010	ABC Corp	ItemXXXA	25
100	550	01/01/2010	ABC Corp	ItemXXXA	25
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100


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

Look GRPOID (200), its date is 03/02/2010. so its fetch sales invoice data from 03/02/2010, so first it goes to Invoice No 200,
but invoice no 200 is already adjusted in previous GRPOID(100), so remaining qty we need to adjust in this GRPOID.
So we need to add one row for this to show its data. Look the Invoice ID(22) and InvoiceNO(200).

I have added CorrectQty Coloumn to show you what should be the data comes in InvoiceQty Column.
Look at CorrectQty column, that what I expect in this scenario.

Thanks,
ABHI

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 01:47:08

Hey Abhi,
Can you please post your modified query also to understand me exact scenario

Vabhav T

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-24 : 02:45:57

No need to send the query i understood....

It was really a very complex problem for me as it learnt me a lot things...
thanks abhi for the problem and visakh for the solution.

Vabhav T

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-24 : 03:16:24

Its still not solved, please see mine reply.

I need to add one more condition.

Please suggest me.

Regards,
ABHI

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-24 : 23:17:03

Hello Visakh,

Do you have any idea about this type of scenario, its really urgent.

can you please help me?

Regards,
ABHI

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-26 : 00:41:23

can you please suggest me for this solution or this type of logic is not possible, please suggest me.

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2012-02-06 : 06:05:48

Any feedback for the above problem?

I am really get stuck...

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2012-02-07 : 06:25:37

As i didnt get any feedback for this problem resolution..i think such schenario in sql is not possible.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-07 : 21:05:00

quote:

Originally posted by abhit_kumar

Sample Data(without using your code) - Your code is fine up to one GRPO case.

GRPOID	GRPONo 	GRPODate	GRPOVendor 	GRPOItemCode 	GRPOTotQty
100	550	01/01/2010	ABC Corp	ItemXXXA	25
100	550	01/01/2010	ABC Corp	ItemXXXA	25
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100

InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem		InvoiceQty
11		100		02/02/2010	VDC Corp	ItemXXXA	15
22		200		03/02/2010	HJK CORP	ItemXXXA	25
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
15		333		07/02/2010	HJK CORP	ItemXXXA	15
17		444		07/02/2010	GGG corp	ItemXXXA	20

Output should be like this:-

GRPOID	GRPONo 	GRPODate	GRPOVendor 	GRPOItemCode 	GRPOTotQty
100	550	01/01/2010	ABC Corp	ItemXXXA	25
100	550	01/01/2010	ABC Corp	ItemXXXA	25
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100
200	555	03/02/2010	DEF Corp	ItemXXXA	100


InvoiceID	InvoiceNO	InvoiceDate	InvoiceVendor	InvItem		InvoiceQty	CorrectQty
11		100		02/02/2010	VDC Corp	ItemXXXA	15		15
22		200		03/02/2010	HJK CORP	ItemXXXA	25		10 Why 10 ? you have 2 record of GRPOID/GRPONo 100/550 (Total Qty = 50) that should be able to satisfied this
22		200		03/02/2010	HJK CORP	ItemXXXA	25		15 Why 15 ? you have 2 record of GRPOID/GRPONo 100/550 (Total Qty = 50) that should be able to satisfied this
33		300		05/02/2010	MMM CORP	ItemXXXA	35		35
44		400		06/02/2010	GGG corp	ItemXXXA	45		45
15		333		07/02/2010	HJK CORP	ItemXXXA	15		5

Can you explain how each line of the expected result is matched with which GRPOID ?

what is the PK for the GRPOData ? You have multiple record with same GRPOID & GRPONo ?

KH
[spoiler]Time is always against us[/spoiler]

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2012-02-08 : 01:22:02

Dear Khtan,

Thanks for activating this thread.

Please note that there is only one record of GRPOID/GRPONo. Its showing two times because when join sales table, then its get GRPO ID occurrence is get increased.

I have attached the table details and final requirement.

Let me know if you have any query.

Regards,
AKM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-08 : 02:09:28

This is much clearer. I will give it a try later.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources