Tricky question on predicting next records

Question

I am doing a testing scenario

6 people in each Site

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:

Rule 1: If a person has tested his site on Day 1, then his turn should come on Day 4 and not before that - APPLICABLE ONLY FOR CURRENT WEEK. So if A and D test a site on 22nd, B and E test it on 23rd and C and F test on 24th, then for this week, A and D can test the site only on 25th. Sunday is a holiday

Rule 2: Every week, the pair should change. Rule 1 is still applicable with the new pair.

Rule 3: A person belonging to a particular site cannot test other sites.

In the query, I should be able to later change the pair from 2 people and make them 3 people at a time or increase the people on each sites from 6 to 10, with few changes to the code.

SAMPLE DATA

DECLARE @Site TABLE
(
SiteID int,
SiteNm varchar(10)
)

DECLARE @Person TABLE
(
PersonID int,
PersonName char(1),
SiteID int
)

INSERT @Site
SELECT 1, 'Site1' UNION ALL
SELECT 2, 'Site2' UNION ALL
SELECT 3, 'Site3' UNION ALL
SELECT 4, 'Site4' 

INSERT @Person
SELECT 1, 'A', 1 UNION ALL
SELECT 2, 'B', 1 UNION ALL
SELECT 3, 'C', 1 UNION ALL
SELECT 4, 'D', 1 UNION ALL
SELECT 5, 'E', 1 UNION ALL
SELECT 6, 'F', 1 UNION ALL
SELECT 7, 'G', 2 UNION ALL
SELECT 8, 'H', 2 UNION ALL
SELECT 9, 'I', 2 UNION ALL
SELECT 10, 'J', 2 UNION ALL
SELECT 11, 'K', 2 UNION ALL
SELECT 12, 'L', 2 UNION ALL
SELECT 13, 'M', 3 UNION ALL
SELECT 14, 'N', 3 UNION ALL
SELECT 15, 'O', 3 UNION ALL
SELECT 16, 'P', 3 UNION ALL
SELECT 17, 'Q', 3 UNION ALL
SELECT 18, 'R', 3 UNION ALL
SELECT 19, 'S', 4 UNION ALL
SELECT 20, 'T', 4 UNION ALL
SELECT 21, 'U', 4 UNION ALL
SELECT 22, 'V', 4 UNION ALL
SELECT 23, 'W', 4 UNION ALL
SELECT 24, 'X', 4

Here's how I want the Output:

Date    Site 1    Site 2     Site 3     Site 4
22Jun    A,D        H,I       N,R        T,W
23Jun    B,E        J,K       M,P        V,X
...
26Jun    A,D         H,I       N,R        T,W  
...
29Jun    F,A         K,L       R,Q        W,U

and so on

Can you help me with the query?

In the query, I should be able to later change the pair from 2 people and make them 3 people at a time or increase the people on each sites from 6 to 10, with few changes to the code. I want this flexibility

Answer 1

This is not something SQL is meant to handle. I would DEFINATELY move this sort of algorithmic logic to code.

If, for some reason, you can't move it to the code and absolutely have to get the predictive information from the database, I would consider a CLR assembly in SqlSever if you are using MS SQL Server 2005 or 2008.

Reference from MSDN on how to use CLR in SQL

Answer 2

It's technically possible to achieve this in T-SQL with nested loops and embedded queries.

--loop over sites
    --loop over n-day periods (initially n=3, so day1->day4, discounting sundays)
        -- loop over pairs in that period
            --match people who are n+x IDs apart, x increments each n-day loop,
               --need to mod this against count(persons per site)
        -- end pairs loop
    -- end n-day loop
-- end sites loop

All hugely ugly in T-SQL. Far easier to do it in a language that's more general purpose.

Answer 3

WITH    sets AS
        (
        SELECT  SiteID, SUBSTRING(CAST(PersonID AS VARCHAR(MAX)) + SPACE(8), 1, 8)AS result, 1 AS rn
        FROM    @Person p
        UNION ALL
        SELECT  p2.SiteID, s.result + SUBSTRING(CAST(PersonID AS VARCHAR(MAX)) + SPACE(8), 1, 8), rn + 1
        FROM    sets s
        JOIN    @Person p2
        ON      p2.siteid = s.siteid
                AND PATINDEX('%' + SUBSTRING(CAST(PersonID AS VARCHAR(MAX)) + SPACE(8), 1, 8) + '%', s.result) = 0
        ),
        cal AS
        (
        SELECT  1 AS rn
        UNION ALL
        SELECT  rn + 1
        FROM    cal
        WHERE   rn < 99
        ),
        pairs AS
        (
        SELECT  SiteId, result, ROW_NUMBER() OVER (PARTITION BY siteid ORDER BY rn2 % 30) pn
        FROM    (
                SELECT  s.*,
                        ROW_NUMBER() OVER (PARTITION BY siteid ORDER BY siteid) AS rn2
                FROM    sets s
                WHERE   rn = 6
                ) q
        WHERE   rn2 % 2 > 0
                AND rn2 % 12 > 5
                AND rn2 % 240 > 119
        )
SELECT  CAST('2009-01-01' AS DATETIME) + rn, siteid,
        SUBSTRING(result, 1 + (rn % 3) * 16, 8) AS first,
        SUBSTRING(result, 1 + (rn % 3) * 16 + 8, 8) AS second
FROM    cal
JOIN    pairs
ON      pn = (rn / 7 + 1)
        AND DATEPART(weekday, CAST('2009-01-01' AS DATETIME) + rn) <> 1