lmu92 (4/7/2010)

---

I'm not sure if I fully understood the requirement...

But I would consider building a permanent table holding all variations and query that one based on the number of shippings.

Maybe the following code helps to understand what I'd do (@lvl is used to define the number of shipments):

DECLARE @lvl INT

SET @lvl=4

DECLARE @tbl TABLE

(

id INT

)

INSERT INTO @tbl

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

;WITH cte AS

(

SELECT

t1.id AS id1,

t2.id AS id2,

t3.id AS id3,

t4.id AS id4,

CASE

WHEN t2.id=t3.id THEN 3

WHEN t2.id<t3.id AND t3.id=t4.id THEN 4

ELSE 5 END AS lvl

FROM @tbl t1

CROSS JOIN @tbl t2

CROSS JOIN @tbl t3

CROSS JOIN @tbl t4

WHERE t1.id<=t2.id

AND t2.id<=t3.id

AND t3.id<=t4.id

AND t1.id<>t2.id AND t1.id<>t3.id AND t1.id<>t4.id

), cte2 AS

(

SELECT

id1,

id2,

CASE WHEN @lvl>3 AND id2<>id3 AND id2<>id4 THEN id3 ELSE 8 END AS id3,

CASE WHEN @lvl>4 AND id3<>id4 AND id2<> id4 AND id3<>id4 THEN id4 ELSE 9 END AS id4

FROM cte

WHERE lvl<= @lvl

AND id2<= @lvl

AND id3<= @lvl

AND id4<= @lvl

)

SELECT

id1,

id2,

CASE WHEN id3<8 THEN id3 ELSE 0 END AS id3,

CASE WHEN id4<9 THEN id4 ELSE 0 END AS id4

FROM cte2

WHERE id2>id1 AND id3>id2 AND id4>id3

GROUP BY id1,id2,id3,id4