I'm sure I've been bitten by this issue before, but I'm trying to replicate it without success.

Maybe it has something to do with this:

From BOL

The size of the string is...

That's why my first suggestion was to insert only the missing transactions. You reduce your I/O especially with the accounts with lots of transactions.

IF OBJECT_ID('Staging_Transactions') > 0

...

Of course, you can do it with a Cross tab approach.

It's all explained in here with comparisons to the pivot operator:

Cross tabs Part 1[/url]

Cross tabs Part 2[/url]

SELECT

...

LoudClear (3/11/2015)

---

I knew when I wrote...

LoudClear (3/11/2015)

---

Here's a ultra-simplistic version.

- Adam

;with Claim_Status as

(

select 100 as [Claim Number] , 1 as [Status]

union all select 100, 3

union all select 101, 1

union all select 102, 1

union all...

Is TransactionID independent from AccountID? Or do you need to include AccountID to uniquely identify a transaction?

How common is the use of the ID column?

Do you have Foreign keys referencing...

andyscott (3/11/2015)

---

Depends on your SET DATEFIRST settings: Whilst the default (English,US) is 7, meaning weeks start on Sunday, in the UK it is...

You're using a recursive CTE that counts which can cause a disaster. Read the following link to know the implications: http://www.sqlservercentral.com/articles/T-SQL/74118/

I came up with this formula in 2 versions.

SELECT MonthStart,

...

You could bulk insert into a staging table and insert the missing transactions. If needed, you could update the existing transactions.

If it's not used, I would change the clustered index...

Or use RIGHT JOIN. 😉

You're abusing the GOs. GOs are batch separator, so variables won't be available in the next batch.

The following part is useless.

declare @columnz varchar (8000)

go

Remove all the GOs from your code...

Koen Verbeeck (3/3/2015)

---

Keep in mind that you are connected live to that SSAS database.

If you make changes...

The option is in Menu File -> Open -> Analysis Services Database...

Something like this?

SELECT *,

CASE WHEN DATEADD(MM, DATEDIFF(MM, DOB, ApptDt2), DOB) > ApptDt2

THEN DATEDIFF(MM, DOB, ApptDt2) - 1

...

m.i.k.e (3/3/2015)

---

Hi,

I am working on a few exercises in SQL but I can't seem to finish the last one. I added the question in a picture and the code...