I'll help a little further. The script you posted (althought syntactically incorrect) does exactly what the RN_MULTINOMIAL function does:

UPDATE BOOKINGS

SET DepartureDate

CASE WHEN RAND() Result = Between 0 and 0.3...

Perhaps one additional clarification.

To generate a single multinomial random number, you do it like this:

SELECT MNRN=dbo.RN_MULTINOMIAL(@MultinomialProbabilities, URN)

FROM (SELECT URN=RAND(CHECKSUM(NEWID()))) a

Provided because I wasn't sure if when you referring to your...

Here is how to generate a sample set of multinomially distributed random numbers. First, you need to create a TYPE and a FUNCTION by running this script:

CREATE TYPE Distribution...

OK. Give me a few minutes and I'll code up an example.

wafw1971 (2/20/2013)

---

I have just been told I have done it completely wrong by my boss and the query above hasn't randomised anything on our data, I have got 48000 records...

wafw1971 (2/20/2013)

---

The following query gives me a random date between 1 and 28 days after the arrival date:

SELECT ArrivalDate, DATEADD(day, 1 + RAND(checksum(NEWID()))

* LengthOfStay.LengthofStay, ArrivalDate) AS DepartureDate ...

Jeff Moden (2/19/2013)

---

Ah. Understood. It would be interesting to find out which is actually faster, though. The mTVF with a loop or the iTVF with the recursive...

Jeff Moden (2/18/2013)

---

Yes,...

In looking at this, I don't think you need to use a temp table. However in order to show you how, you'd need to provide:

- DDL for your [EODVolumeStats]...

You can also do this with a recursive CTE, which can be put into an iTVF.

See the first example, in the third article in my signature links.

Something like this perhaps?

;WITH MyData (orderid, Invoice, netprice, pricechange) AS (

SELECT 1234,98989,39.99,' '

UNION ALL...

Forgive me but isn't this easily done as follows?

;WITH AuditDates (AgentID, AuditDate, Completed) AS (

SELECT 54321, '2013-01-01', 1

UNION ALL SELECT 54321,'2013-01-15', 1

...

Sergiy (2/13/2013)

---

dwain.c (1/15/2013)

---

I understand you already have a solution but this might be a bit more efficient. The technique is based on an article by Jeff Moden on Grouping...

Magoo you've done it again!

Pretty fast for sure.

The record counts I posted might have been before I made some changes to the test harness.

You can try something like this:

-- Create temporary tables.

CREATE TABLE #permutations(id TINYINT)

CREATE TABLE #items (item varchar(20))

-- Insert two rows for permutations.

INSERT INTO #permutations

VALUES (1)

INSERT INTO #permutations

VALUES (2)

-- Insert Items to...