select TOP 50 *
from   (
           select top 100 *
           from   table
           order by cola
       ) a
order by cola desc

declare @table table
(
	plt 	varchar(10),
	qty 	int,
	rmk	varchar(10)
)
insert into @table
select	'A', 1, 'a'	union all
select	'A', 1, 'b'	union all
select	'A', 1, 'c'	union all
select	'A', 1, 'd'	union all
select	'A', 2, 'e'

-- ANY 2 records with plt = 'A' and Qty = 1
select	top 2 *
from	@table
where	plt	= 'A'
and	qty	= 1
order by newid()

SELECT MaxItems  AS TotalPallet,
			FaceValue AS QtyPerPlt,
			a.ItemStorageID
		FROM	#Data
			
		LEFT JOIN (
			SELECT Top MaxItems  FROM tblItemDetail d 
			)  a ON FaceValue=d.OriginQty-d.QtyPerPlt-d.HoldQty-d.QcQty+d.AdjQty

quote:

---

dunno if u understand full bahasa melayu or not

---

original data from tblitemdetail
itemstorageid Qty
A1		3
A2		3
A3		2
A4		3

peter code will sum match Qty combination = 6 (this is done)
MaxItems       Qty
2		3    
(which means 2 data for Qty=3---> 2x3 match with user's parameter=6)

and i need to return back what the itemstorageid (here i stuck)
MaxItems	Qty	ITEMSTORAGEID
2		3	A1
2		3	A2

-- Prepare sample data
DECLARE	@Detail TABLE (Storage VARCHAR(2), Qty INT)

INSERT	@Detail
SELECT	'A1', 3 UNION ALL
SELECT	'A2', 2 UNION ALL
SELECT	'A3', 3 UNION ALL
SELECT	'A4', 4 UNION ALL
SELECT	'A5', 3 UNION ALL
SELECT	'A6', 4 UNION ALL
SELECT	'A7', 4

DECLARE	@Data TABLE (Items INT, QTY INT)

INSERT	@Data
SELECT	2, 3 UNION ALL
SELECT	1, 4

-- Show the expected output
SELECT		det.Storage,
		det.Qty
FROM		@Detail AS det
LEFT JOIN	@Data AS dat ON dat.Qty = det.Qty
WHERE		dat.Items >= (SELECT COUNT(*) FROM @Detail AS x WHERE x.Qty = det.Qty AND x.Storage <= det.Storage)
ORDER BY	det.Storage,
		det.Qty

quote:

---

Originally posted by khtan

Frankly speaking, i don't really understand his code

---

quote:

---

Originally posted by maya_zakry

hi peter.. there u are!!!
khtan-> i dont understand too.. i just paste.. change variables.. and then paste back here when facing problem.. :(

~~~Focus on problem, not solution~~~

---

-- Show the expected output
SELECT		det.ItemStorageID,
		det.Qty
FROM		tblItemDetail AS det
LEFT JOIN	#Data AS dat ON dat.Qty = det.Qty
WHERE		dat.MaxItems >= (SELECT COUNT(*) FROM tblItemDetail AS x WHERE x.Qty = det.Qty AND x.ItemStorageID <= det.ItemStorageID)
ORDER BY	det.ItemStorageID,
		det.Qty

quote:

---

Originally posted by Peso

quote:

---

Originally posted by khtan

Frankly speaking, i don't really understand his code

---

Yes you do! You are only too modest when you write that!

Write in English. It will not look pretty if I write in Swedish back to you, like this:

Hej alla. Nu förstår ni varför man ska skriva på engelska. Det är ingen fler än skandinaver och några finnar som kommer att begripa vad här står, eller hur?

Decode that!


Peter Larsson
Helsingborg, Sweden

---

quote:

---

Originally posted by Peso

You mean this?

-- Show the expected output
SELECT		det.ItemStorageID,
		det.Qty
FROM		tblItemDetail AS det
LEFT JOIN	#Data AS dat ON dat.Qty = det.Qty
WHERE		dat.MaxItems >= (SELECT COUNT(*) FROM tblItemDetail AS x WHERE x.Qty = det.Qty AND x.ItemStorageID <= det.ItemStorageID)
ORDER BY	det.ItemStorageID,
		det.Qty

Peter Larsson
Helsingborg, Sweden

---

SELECT maxItems AS TotalPallet,
			 FaceValue AS QtyPerPlt,
			 d.ItemStorageID
		FROM	#Data AS data
		LEFT JOIN tblItemDetail AS d ON data.maxItems= d.OriginQty-d.PickQty-d.HoldQty-d.BookQty-d.QCQty+d.AdjQty
		WHERE data.MaxItems >= (SELECT COUNT(*) FROM tblItemDetail AS x 
					     WHERE x.OriginQty-x.PickQty-x.HoldQty-x.BookQty-x.QCQty+x.AdjQty=d.OriginQty-d.PickQty-d.HoldQty-d.BookQty-d.QCQty+d.AdjQty
						AND  x.ItemStorageID <= d.ItemStorageID )
				ORDER BY	d.ItemStorageID, data.MaxItems