## Archive for June 25th, 2009

## Building permutations: SQL Server

Comments enabled. I *really* need your comment

From **Stack Overflow**:

I am doing a testing scenario.

There are

6people in each`site`

:

Site People Site 1 A, B, C, D, E, F Site 2 G, H, I, J, K, L Site 3 M, N, O, P, Q, R Site 4 S, T, U, V, W, X I want to write a query that can suggests me datewise the people who can test the site, two at a time.

Here are the rules:

- If a person has tested his site on
Day 1, then his turn should come onDay 4and not before that.

This, however, is applicable for current week. So ifAandDtest a site on22nd,BandEtest it on23rdandCandFtest on24th, then for this week,AandDcan test the site only on25th. Sunday is a holiday.- Every week, the pair should change. Rule
1is still applicable with the new pair.- A person belonging to a particular site cannot test other sites.

To build these rules, we need to do the following:

- For each site, split
**6**people into**3**pairs. - Assign each pair to the appropriate day of week (pair
**1**for**Monday**and**Thursday**, pair**2**for**Tuesday**and**Friday**, pair**3**for**Wednesday**and**Saturday**) - For each new week, split the
**6**people new way so that no pairs persist.

Step **3** is most interesting here, since it requires building permutations.

To build the pairs, we need to arrange **6** people and take the pairs from the arrangements.

There are `6! = 720`

distinct ways to arrange **6** people.

But for our task the following pairs:

`(1, 2)`

and`(2, 1)`

`(3, 4)`

and`(4, 3)`

`(5, 6)`

and`(6, 5)`

are considered equal. That's why we can use only `720 / 8 = 90`

arrangements which yield non-repeating pairs.

**SQL Server** does not provide a native way to build all permutations of a set. We will have to emulate it using recursive **CTE**'s.

Let's create a table with required data:

Read the rest of this entry »