Databases
StackOverflow2013
Postsdbo
POTricky question on predicting next records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTricky question on predicting next records
    primarykey
    data
    text
    <p>I am doing a testing scenario</p> <p>6 people in each Site</p> <pre><code>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 </code></pre> <p>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:</p> <p><strong>Rule 1:</strong> 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</p> <p><strong>Rule 2:</strong> Every week, the pair should change. Rule 1 is still applicable with the new pair.</p> <p><strong>Rule 3:</strong> A person belonging to a particular site cannot test other sites.</p> <blockquote> <p>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.</p> </blockquote> <p><strong>SAMPLE DATA</strong></p> <pre><code>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 </code></pre> <p><strong>Here's how I want the Output:</strong></p> <pre><code>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 </code></pre> <p>and so on</p> <p>Can you help me with the query?</p> <blockquote> <p>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</p> </blockquote>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USlols
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTricky question on predicting next records
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthis is going to be much easier in code ... both to maintain and implement
      singulars
      1. POTricky question on predicting next records
      1. USSam Saffron
    2. COI did not understand what you mean? You mean LINQ? Can you show how. thanks
      singulars
      1. POTricky question on predicting next records
      1. USlols
    3. COHahahaha. You'll need one or more of the following: a) self-joins with non-trivial join predicates, b) recursive CTEs, c) ranking functions, d) datepart and dateadd functions. Sounds like a homework assignment, honestly, with the arbitrary rules.
      singulars
      1. POTricky question on predicting next records
      1. USPeter Radocchia
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload