Voucher number is a business entity, it could just as easily...

SELECT 'step1' AS Step, tab1.*, tab2.*

FROM tab1

INNER JOIN tab2 ON tab2.postalCode = tab1.postalCode

UNION ALL

SELECT 'step2' AS Step, tab1.*, tab2.*

FROM (

SELECT tab1.*

FROM tab1

LEFT JOIN tab2 ON tab2.postalCode = tab1.postalCode

WHERE tab2.postalCode IS...

In addition to the corrections which Lynn pointed out:

If @CurrentPartNumber<>@PartNumber

begin

--Do something

...

lmu92 (2/13/2010)

---

Maybe someone who looks at execution plans and wonder...

You can, and it looks ok, but you wouldn't want to;

DECLARE @CurveId varchar(60)

SET @CurveId = ','...

Hi Elias

Thanks again for testing this out. It's a good improvement, but still way short of expectations. Looks like time for plan B :blush:

May I suggest you send me a...

A single date doesn't constitute a period. I've assumed that a "period" is represented (in the periods table) as follows:

startdate is the date...

Who needs CTE's? 😛

SELECT p.PeriodStart, p.PeriodEnd, da.Dept, da.Acct,

ISNULL(s.[Date], p.PeriodEnd), ISNULL(s.Credit, 0) AS Credit,...

Hi Elias

Here's version 2. The facility string store has been removed and replaced with conventional processing. Collateral balances are only stored when they need to be, and are removed when...

Please edit and correct your sample data script. There's a UNION ALL on the last SELECT for #periods, when this is corrected there are further errors:

Msg 207, Level 16, State...

Luckbox72 (2/12/2010)

---

I tried

SET @query=

'SELECT * Into #tempTable FROM

(SELECT RepID, State

FROM RepBDState) src

PIVOT (count(State) FOR State IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

select * from #tempTable

and when run I get the following

(236...

This is what happens when candidates are left alone in a room with a written test and...

Hi Elias

Thanks for giving it a try. "Make it work, make it fast, make it pretty". There's plenty of scope for improvement.

Firstly, as you suggest, perhaps only one string store...

Dupe = ROW_NUMBER() OVER(PARTITION BY Col1 Col2 Col3 Col4 Col5 Col6 ORDER BY Col1 Col2 Col3 Col4 Col5 Col6 Col7)

If you set up your sample as a CREATE TABLE...

Hi Elias

Thanks for being so patient.

Don't forget - always test.

-- data setup

DROP TABLE #Working

DROP TABLE #zzzCovers

CREATE TABLE #zzzCovers(

[CollateralID] [nvarchar](50) NOT NULL,

...