Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say...

Siobhan Perricone (3/12/2013)

---

Yeah, I'm testing now. Looks...

This

declare @Rid bigint

set @Rid =106,108;

generates an error, because bigint datatype holds a single bigint, not a list of them. If @Rid was a character type then you would be able...

Set up a million-row (or more) test table (see Jeff Moden's articles) and do a triangular join running totals on it.

Gather index frag stats. This one is from Glen Berry's...

No worries mate. I had the day off yesterday and picked it up at home - when you hadn't posted up by this morning it was ready to go.

Gosh :blush: thanks!

A gentle reminder - always always test 🙂 If this solution works, it can be condensed and tweaked for performance.

This solution I belive matches your description:

IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE #FirstChoice;

IF OBJECT_ID('tempdb..#SecondChoice') IS NOT NULL DROP TABLE #SecondChoice;

IF OBJECT_ID('tempdb..#RemainingPitches') IS NOT NULL DROP TABLE #RemainingPitches;

IF OBJECT_ID('tempdb..#RemainingPunters') IS...

This proposed solution is slightly different.

Start by allocating punters picked at random, to pitches, based on punter's first choice. Using the sample set, most towns have all pitches allocated,...

This might give you a little boost too:

SELECT a.id, a.GUID, a.SubmissionID, b.id, b.GUID, b.SubmissionID, 1

FROM dbo.All_keys_ AS a

INNER JOIN dbo.Load1_keys_ AS b

on a.mkMatchKeyType1 = b.mkMatchKeyType1

WHERE NOT EXISTS (SELECT 1 from...

Here's a proposed solution. I've broken it up into stages using #temp tables which can be read for the purpose of verifying the results.

IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE...

Here's a working dataload:

-- set identity insert on for lottery

SET IDENTITY_INSERT survey.FirewoodLottery ON

-- entering sample data into lottery table

INSERT INTO [survey].[FirewoodLottery]

(FirewoodLottery_id --DateValue, Value, YearValue, Monthvalue,

,[Firstname]

,[MiddleInitial]

,[Lastname]

,[Suffix]

,[address]

,[city]

,[state]

,[zip]

,[phone]

,

,[postdate]

,127.0.0.1

,[choice1]

,[choice2]

,[assigned]

,[status]

,[District]

,[sortindex]

,[choice]

,[Notes]

--,[possible_dup]

)

select 1,'Wombat',NULL,'Champine',NULL,'15...

RZ52 (3/7/2013)

---

That's exactly what Jason's code is designed to do....

Nice one geezer 😎

It makes for a surprisingly succinct query.

winmansoft (3/7/2013)

---

ChrisM@Work (3/7/2013)

---

winmansoft (3/7/2013)

---

ChrisM@Work (3/7/2013)

---

yes - do this instead:

DECLARE @Parameter VARCHAR(8000)

SET @Parameter = 'seh,jyo,mhf'

SELECT *

INTO #Elements

FROM dbo.DelimitedSplit8K (@Parameter,',')

...

SELECT d.Item

FROM (

SELECT

a.Item,

Vendor_Code

FROM Purch_Inv_Line a

INNER JOIN Purch_Inv_Header a

ON a.[Document No_] = b.Item

AND b.[Posting Date] BETWEEN '2010-01-01' AND '2010-12-31'

GROUP BY a.Item, Vendor_Code

) d

GROUP BY d.Item

HAVING COUNT(*)...