Prev: <resource-list> Deadlock graph interpretation
Next: query plan for stored procedure
From: BruceL on 29 Apr 2010 16:18
Trying to assign registrations to colonies up to the colony maximum.
Original code was a cursor, but its slow and kludgy.
The problem is that the colony might already have registrations so I
need to check for not exceeding the maximum registrations (numbers
table maybe?).
Each registration can only be in 1 colony.

CREATE TABLE [Colonys] (
[Colony_Name] [varchar] (20),
[Max_registrations] [smallint] CONSTRAINT
[DF_Colony_Colony_Number_Max] DEFAULT (0),
CONSTRAINT [PK_COLONY] PRIMARY KEY NONCLUSTERED
(
[Colony_name]
)
) ON [PRIMARY]

Insert Colonys Values ('Alpha',10)
Insert Colonys Values ('Bravo',5)
Insert Colonys Values ('Charlie','15)

Create Table [Registrations] (
[Student_name] [varchar] (50)
[Registration_date] datetime
CONSTRAINT [Registrations_idx] PRIMARY KEY CLUSTERED
(
[Regprg_sysid]
)
Insert Registrations Values ('Sam Sneed',#01/15/2010#)
Insert Registrations Values ('May West', #03/23/2010#)
etc...

CREATE TABLE [Colony_Registration] (
[Colony_name] [varchar] (20),
[Student_name] [varchar] (50)
[Registration_date] datetime
CONSTRAINT [Colony_Registration_idx] PRIMARY KEY CLUSTERED
(
[Colony_name]
[Student_name]
[Registration_date]
)
)

From: Philipp Post on 30 Apr 2010 04:38
As there is no CREATE ASSERTION, you could try IF EXISTS / ROLLBACK in
a insert / update trigger (not fully set based, but most likely better
as a cursor) or a VIEW with check option

-- get the colonies which exceed max registrations
SELECT colony_name, max_registrations
FROM Colonys AS C
INNER JOIN (SELECT colony_name, COUNT(*) AS registration_count
FROM Colony_Registration
GROUP BY colony_name) AS R
ON C. colony_name = R. colony_name
WHERE registration_count > max_registrations;

An exmple for a VIEW with check option goes here:
http://sqlblog.com/blogs/hugo_kornelis/archive/2006/09/15/Snapshot-and-integrity-part-4.aspx

brgds

Philipp Post
From: TheSQLGuru on 30 Apr 2010 16:15
assuming you have the inserts required stored in a set somewhere (such as a
temp table), you should be able to count the existing rows in the reg table
and then do an insert top regmax - regcurrentcount select statement to
ensure you don't overfill the reg table. Note that you will need a HOLDLOCK
of some kind on the reg table when you count the existing rows to make sure
some other spid doesn't insert underneath of you. Note also that this will
affect concurrency some.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"BruceL" <bdloving(a)gmail.com> wrote in message
news:849ffc27-2088-46cf-b3de-5bfa5bd1d3b3(a)23g2000pre.googlegroups.com...
> Trying to assign registrations to colonies up to the colony maximum.
> Original code was a cursor, but its slow and kludgy.
> The problem is that the colony might already have registrations so I
> need to check for not exceeding the maximum registrations (numbers
> table maybe?).
> Each registration can only be in 1 colony.
>
> CREATE TABLE [Colonys] (
> [Colony_Name] [varchar] (20),
> [Max_registrations] [smallint] CONSTRAINT
> [DF_Colony_Colony_Number_Max] DEFAULT (0),
> CONSTRAINT [PK_COLONY] PRIMARY KEY NONCLUSTERED
> (
> [Colony_name]
> )
> ) ON [PRIMARY]
>
> Insert Colonys Values ('Alpha',10)
> Insert Colonys Values ('Bravo',5)
> Insert Colonys Values ('Charlie','15)
>
> Create Table [Registrations] (
> [Student_name] [varchar] (50)
> [Registration_date] datetime
> CONSTRAINT [Registrations_idx] PRIMARY KEY CLUSTERED
> (
> [Regprg_sysid]
> )
> Insert Registrations Values ('Sam Sneed',#01/15/2010#)
> Insert Registrations Values ('May West', #03/23/2010#)
> etc...
>
> CREATE TABLE [Colony_Registration] (
> [Colony_name] [varchar] (20),
> [Student_name] [varchar] (50)
> [Registration_date] datetime
> CONSTRAINT [Colony_Registration_idx] PRIMARY KEY CLUSTERED
> (
> [Colony_name]
> [Student_name]
> [Registration_date]
> )
> )
>


From: BruceL on 30 Apr 2010 16:15
trying to think this though in a set based way
[pseudo thoughts not code] so:
for the set of colonies with registration count less than colony max
insert colony registrations
from the set of registrations not already in colony registrations up
to [each?] colony max


From: BruceL on 3 May 2010 10:03
OK, I can see how to do that 1 colony at a time. But if I try it as a
'set of colonies' I get
Colony A , Person 1
Colony A, Person 2 ... up to max avail Colony A
Colony B, Person 1 (same persons!)
so, I am stuck iterating though the colonies 1 at a time since SQL
inserts the same person in every colony that has an open slot.
Still don't see it as a set based solution then.
 |  Next  |  Last
Pages: 1 2
Prev: <resource-list> Deadlock graph interpretation
Next: query plan for stored procedure