Query Suggesting records accurately

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query Suggesting records accurately

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2009-06-23 : 04:24:05

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 sites from 4 to 5, 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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 05:12:12

[code]-- Prepare sample data
DECLARE @Person TABLE
(
PersonID INT,
PersonName CHAR(1),
SiteID INT
)

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

-- Initialize user supplied parameters
DECLARE @Persons TINYINT,
@StartDate DATETIME,
@EndDate DATETIME

SELECT @Persons = 2,
@StartDate = '20090601',
@EndDate = '20090630'

-- Display the result
SELECT theDate,
MAX(Site1) AS Site1,
MAX(Site2) AS Site2,
MAX(Site3) AS Site3,
MAX(Site4) AS Site4
FROM (
SELECT DATEADD(DAY, 3 * v.Number + p.tupletID, @StartDate) AS theDate,
MIN(CASE WHEN p.SiteID = 1 THEN p.PersonName ELSE '' END) + '/' + MAX(CASE WHEN p.SiteID = 1 THEN p.PersonName ELSE '' END) AS Site1,
MIN(CASE WHEN p.SiteID = 2 THEN p.PersonName ELSE '' END) + '/' + MAX(CASE WHEN p.SiteID = 2 THEN p.PersonName ELSE '' END) AS Site2,
MIN(CASE WHEN p.SiteID = 3 THEN p.PersonName ELSE '' END) + '/' + MAX(CASE WHEN p.SiteID = 3 THEN p.PersonName ELSE '' END) AS Site3,
MIN(CASE WHEN p.SiteID = 4 THEN p.PersonName ELSE '' END) + '/' + MAX(CASE WHEN p.SiteID = 4 THEN p.PersonName ELSE '' END) AS Site4
FROM (
SELECT SiteID,
PersonName,
(ROW_NUMBER() OVER (PARTITION BY SiteID ORDER BY NEWID()) - 1) / @Persons AS tupletID
FROM @Person
) AS p
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE v.Number <= DATEDIFF(DAY, @StartDate, @EndDate)
GROUP BY DATEADD(DAY, 3 * v.Number + p.tupletID, @StartDate),
SiteID
) AS d
GROUP BY theDate
ORDER BY theDate[/code]

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-06-23 : 05:25:40

Peso - Thanks for query.

How can I implement Rule 2 in this query? Moreover Sunday is not be considered in the dates. Please see the sample data I posted to understand Rule 2.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:11:03

Awww, do I have to do all the work for you?
I have given you a clear direction how to proceed...

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-06-23 : 07:18:30

I am trying to do this but the records are generated and repeated same for every week. Can you tell me where to change the query so that the pairs are not same for every week.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 07:21:12

Fiddle around with the query and you will eventually find the solution.

You know, "Teach a person to fish and he'll live a lifetime, give a man a fish and he'll live a day."

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-06-23 : 07:40:10

:) Yes very true. Ok can you help me out with one thing. What does this line mean?

DATEADD(DAY, 3 * v.Number + p.tupletID, @StartDate)

and do i have to change this line if I increase the people per site to 10 and the pair to 3?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 08:43:40

The number three is that people gets reshuffled every three days.
The tuplet is calculated on how many persons per day.

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-06-23 : 09:04:53

ok thanks for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 16:55:00

Here is another approach

DECLARE	@Stage TABLE
	(
		Iteration INT,
		SiteID INT,
		[Path] VARCHAR(MAX)
	)

DECLARE	@Multiple INT,
	@RollOver INT,
	@Iteration INT

SELECT	@Multiple = 2,
	@RollOver = 3,
	@Iteration = 0

WHILE @@ROWCOUNT > 0 AND @Iteration < 14
	BEGIN
		SET	@Iteration = @Iteration + 1

		INSERT		@Stage
				(
					Iteration,
					SiteID,
					[Path]
				)
		SELECT		@Iteration,
				1,
				(
					SELECT		TOP (@Multiple)
							';' + p.PersonName + ';'
					FROM		Person AS p
					WHERE		p.SiteID = 1
							AND NOT EXISTS (SELECT * FROM @Stage AS s WHERE s.SiteID = 1 AND s.Iteration >= 1 + @Iteration - @RollOver AND ';' + s.[Path] + ';' LIKE '%;' + p.PersonName + ';%')
					ORDER BY	NEWID()
					FOR XML		PATH('')
				)
	END

SELECT	Iteration,
	SiteID,
	[Path]
FROM	@Stage

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources