Posted by Erland Sommarskog on 06/28/07 21:31

k4 (stburks@gmail.com) writes:
> I have two tables. Table1 contains two fields. Field1 contains names
> of job types (Accountant,Doctor,etc.) Field2 contains the number of
> cases to select from Table2 for each job type.
>
> Table2 contains three fields. Field1 contains Unit
> codes(Unit1,Unit2,etc.).Field2 contains job type.Field3 contains
> information about the action to be taken.
>
> I need to perform a select that chooses the number of cases shown in
> table2 for the job type that matches in both tables. i need to make
> sure that I select that number for every available unit and job
> function. The selections must be random.
>
> As an example: I have 16 units. Each unit contains accountants and
> doctors. Table1 has 5 in number of cases for accountants and 10 for
> doctors. Based on this, for each of the 16 units I need to randomly
> select 5 actions for accountants and 10 cases fo doctors.
>
> I am using Sql Server 2000.

There is a good advice that you for this type of questions include
CREATE TABLE statements for your tables, INSERT statements with sample
data, and the desired output given the sample. This can help to clarify
your question. Below I have such a script, that should address your
problem as I understood it, but in case I misunderstood, it will be
less useful. I could not resist including a solution for SQL 2005, since
this is a single query, whereas SQL 2005 requires a temp table.

CREATE TABLE jobs (jobtype varchar(20) NOT NULL PRIMARY KEY,
cnt int NOT NULL)
CREATE TABLE actions (jobtype varchar(20) NOT NULL,
unit varchar(20) NOT NULL,
action varchar(20) NOT NULL,
PRIMARY KEY (jobtype, unit, action))
INSERT jobs (jobtype, cnt) VALUES ('Dentist', 2)
INSERT jobs (jobtype, cnt) VALUES ('Plumber', 1)
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'A', 'Audit')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'A', 'Test')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'A', 'Visit')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'A', 'Retire')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'B', 'Audit')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'B', 'Test')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'B', 'Visit')
INSERT actions (jobtype, unit, action)
VALUES ('Dentist', 'B', 'Retire')
INSERT actions (jobtype, unit, action)
VALUES ('Plumber', 'A', 'Visit')
INSERT actions (jobtype, unit, action)
VALUES ('Plumber', 'A', 'Retire')
INSERT actions (jobtype, unit, action)
VALUES ('Plumber', 'B', 'Audit')
INSERT actions (jobtype, unit, action)
VALUES ('Plumber', 'B', 'Test')
go
-- SQL 2005
SELECT a.jobtype, a.unit, a.action
FROM (SELECT DISTINCT j.jobtype, j.cnt, a.unit
FROM jobs j
JOIN actions a ON a.jobtype = a.jobtype) AS j
CROSS APPLY (SELECT TOP(j.cnt) a.jobtype, a.unit, a.action
FROM actions a
WHERE a.jobtype = j.jobtype
AND a.unit = j.unit
ORDER BY newid()) AS a
ORDER BY a.jobtype, a.unit, a.action
go
-- SQL 2000
CREATE TABLE #temp (ident int IDENTITY,
jobtype varchar(20) NOT NULL,
unit varchar(20) NOT NULL,
action varchar(20) NOT NULL,
PRIMARY KEY (jobtype, unit, action))

INSERT #temp (jobtype, unit, action)
SELECT jobtype, unit, action
FROM actions
ORDER BY jobtype, unit, newid()

SELECT t.jobtype, t.unit, t.action
FROM #temp t
JOIN (SELECT jobtype, unit, minident = MIN(ident)
FROM #temp
GROUP BY jobtype, unit) AS t2 ON t.jobtype = t2.jobtype
AND t.unit = t2.unit
JOIN jobs j ON j.jobtype = t.jobtype
WHERE t.ident BETWEEN t2.minident AND t2.minident + j.cnt - 1
ORDER BY t.jobtype, t.unit, t.action
go
DROP TABLE jobs
DROP TABLE actions
DROP TABLE #temp



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]