Script Help - 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

General SQL Server Forums

Script Library

Script Help

Author

Topic

Clarkmeister
Starting Member

4 Posts

Posted - 2009-06-23 : 12:05:32

I have a file which contains orders but different order types. I want to select where the item and qty match across different order types.

File contains following fields : Item, Qty, Order Type, Order No, Ref.

Item1 10 RQ 410 213
Item2 20 RQ 410 213
Item3 30 RQ 410 213
Item1 10 DO 411
Item1 20 DO 412
Item2 10 DO 412
Item2 20 DO 411
Item3 30 DO 411
Item3 20 DO 412

In the above case I'd like to return the following :

RQ 410 Item1 10 DO 411
RQ 410 Item2 20 DO 411
RQ 410 Item3 30 DO 411

Ideally I'd like to only return :

RQ 410 DO 411 Ref 213

Any ideas ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-23 : 21:26:32

you have to tell us how to get to the result that you want. What's the rule / condition here ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-23 : 21:30:24

this is what you want ?


select r.[Order Type], r.[Order No], r.[Item], r.[Qty], d.[Order Type], d.[Order No]
from   orders r
       inner join orders d on  r.[Item] = d.[Item] 
                           and r.[Qty]  = d.[Qty]
where  r.[Order Type] = 'RQ'
and    d.[Order Type] = 'DO'

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

Clarkmeister
Starting Member

4 Posts

Posted - 2009-06-24 : 04:37:55

quote:

Originally posted by khtan

this is what you want ?


select r.[Order Type], r.[Order No], r.[Item], r.[Qty], d.[Order Type], d.[Order No]
from   orders r
       inner join orders d on  r.[Item] = d.[Item] 
                           and r.[Qty]  = d.[Qty]
where  r.[Order Type] = 'RQ'
and    d.[Order Type] = 'DO'

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

Thats not far away KH.

Can I do 2 inner joins as I have given you some wrong info? The order type is actually in a different file.

Also is there a way of matching on part of a field. For example i have one field the has a result of "190" in it and another field that has a result of "102=>190". I want to chop that field and take the last 3 characters.

Thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 04:43:35

can you post the sample data for such scenario ?

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

Clarkmeister
Starting Member

4 Posts

Posted - 2009-06-24 : 05:41:01

quote:
Originally posted by khtan

can you post the sample data for such scenario ?

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

File Name : MGLINE

Fields : MRTRNR, MRRORN, MRITNO, MRTRQT

Data :

410 213 Item1 10
410 213 Item2 20
410 213 Item3 30
411 ___ Item1 10
411 ___ Item2 20
411 ___ Item3 30
412 ___ Item1 20
412 ___ Item2 10
412 ___ Item3 20

File : MGHEAD

Fields : MGTRNR, MGTRTP, MGTRDT, MGTWLO, MGWHLO

Date :

410 R01 280609 ___ 190
411 DR1 150609 190 102
412 DR0 160609 190 102

In the above example I want join MGHEAD and MGLINE on MRTRNR.

This should give me a file that looks like :

410 R01 280609 ________ 190 213 Item1 10
410 R01 280609 ________ 190 213 Item2 20
410 R01 280609 ________ 190 213 Item3 30
411 DR1 150609 102=>190 102 ___ Item1 10
411 DR1 150609 102=>190 102 ___ Item2 20
411 DR1 150609 102=>190 102 ___ Item2 30
412 DR0 160609 190 102 ___ Item1 20
412 DR0 160609 190 102 ___ Item2 10
412 DR0 160609 190 102 ___ Item3 20

I need 2 sets of the same data.

I then want to match where a.MGWHSL=b.MGTWLO(last 3 char) and a.MRTRTP<>b.MRTRTP and a.MRITNO=b.MRITNO and a.MRTRQT=b.MRTRQT

The result I expect to show is this :

410 R01 280609 411 DR1 150609 190 Item1 10
410 R01 280609 411 DR1 150609 190 Item2 20
410 R01 280609 411 DR1 150609 190 Item3 30

but really all I need is 1 return of

410 R01 280609 411 DR1 150609 190

but I can filter out manually if need be.

Hope this is enough information.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 06:27:56

[code]DECLARE @Sample TABLE
(
Item VARCHAR(10),
Qty INT,
OrderType VARCHAR(2),
OrderNo INT,
Ref INT
)

INSERT @Sample
SELECT 'Item1', 10, 'RQ', 410, 213 UNION ALL
SELECT 'Item2', 20, 'RQ', 410, 213 UNION ALL
SELECT 'Item3', 30, 'RQ', 410, 213 UNION ALL
SELECT 'Item1', 10, 'DO', 411, NULL UNION ALL
SELECT 'Item1', 20, 'DO', 412, NULL UNION ALL
SELECT 'Item2', 10, 'DO', 412, NULL UNION ALL
SELECT 'Item2', 20, 'DO', 411, NULL UNION ALL
SELECT 'Item3', 30, 'DO', 411, NULL UNION ALL
SELECT 'Item3', 20, 'DO', 412, NULL

; WITH Yak (OrderType, OrderNo, Ref, colID, grpID)
AS (
SELECT s.OrderType,
s.OrderNo,
s.Ref,
ROW_NUMBER() OVER (PARTITION BY w.Item, w.Qty ORDER BY s.OrderType, s.OrderNo),
DENSE_RANK() OVER (ORDER BY w.Item, w.Qty)
FROM (
SELECT Item,
Qty
FROM @Sample
GROUP BY Item,
Qty
HAVING COUNT(DISTINCT OrderType) > 1
) AS w
INNER JOIN @Sample AS s ON s.Item = w.Item
AND s.Qty = w.Qty
)

SELECT DISTINCT MAX(CASE WHEN colID = 1 THEN OrderType ELSE NULL END),
MAX(CASE WHEN colID = 1 THEN OrderNo ELSE NULL END),
MAX(CASE WHEN colID = 2 THEN OrderType ELSE NULL END),
MAX(CASE WHEN colID = 2 THEN OrderNo ELSE NULL END),
MAX(Ref)
FROM Yak
GROUP BY grpID[/code]

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

Clarkmeister
Starting Member

4 Posts

Posted - 2009-06-24 : 10:11:32

quote:

Originally posted by Peso

DECLARE	@Sample TABLE
	(
		Item VARCHAR(10),
		Qty INT,
		OrderType VARCHAR(2),
		OrderNo INT,
		Ref INT
	)

INSERT	@Sample
SELECT	'Item1', 10, 'RQ', 410,  213 UNION ALL
SELECT	'Item2', 20, 'RQ', 410,  213 UNION ALL
SELECT	'Item3', 30, 'RQ', 410,  213 UNION ALL
SELECT	'Item1', 10, 'DO', 411, NULL UNION ALL
SELECT	'Item1', 20, 'DO', 412, NULL UNION ALL
SELECT	'Item2', 10, 'DO', 412, NULL UNION ALL
SELECT	'Item2', 20, 'DO', 411, NULL UNION ALL
SELECT	'Item3', 30, 'DO', 411, NULL UNION ALL
SELECT	'Item3', 20, 'DO', 412, NULL

; WITH Yak (OrderType, OrderNo, Ref, colID, grpID)
AS (
	SELECT		s.OrderType,
			s.OrderNo,
			s.Ref,
			ROW_NUMBER() OVER (PARTITION BY w.Item, w.Qty ORDER BY s.OrderType, s.OrderNo),
			DENSE_RANK() OVER (ORDER BY w.Item, w.Qty)
	FROM		(
				SELECT		Item,
						Qty
				FROM		@Sample 
				GROUP BY	Item,
						Qty
				HAVING		COUNT(DISTINCT OrderType) > 1
			) AS w
	INNER JOIN	@Sample AS s ON s.Item = w.Item
				AND s.Qty = w.Qty
)

SELECT DISTINCT	MAX(CASE WHEN colID = 1 THEN OrderType ELSE NULL END),
		MAX(CASE WHEN colID = 1 THEN OrderNo ELSE NULL END),
		MAX(CASE WHEN colID = 2 THEN OrderType ELSE NULL END),
		MAX(CASE WHEN colID = 2 THEN OrderNo ELSE NULL END),
		MAX(Ref)
FROM		Yak
GROUP BY	grpID

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

You lost me a bit on this one !!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources