Please ignore this post, any Calculations are executed at runtime not when the cube is processed.

Good eyes 😉

Select *

,charindex(',',reverse(hid)) as last_comma

,LEN(HID) as string_length

,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999) as new_String

,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999)) as new_string_next_Comma

,reverse(SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999))-1)) As endResult

FROm #test

Same as above but slightly different approach

create table #test(HID varchar (50))

insert into #test values

('12,3,16'),

('16,10,256'),

('2,150,110,200'),

('5,70,4'),

('2,100,110,150,200,123,159602,1568')

Select *

,charindex(',',reverse(hid)) as last_comma

,LEN(HID) as string_length

,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999) as new_String

,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999)) as new_string_next_Comma

,SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,charindex(',',SUBSTRING(reverse(hid),charindex(',',reverse(hid))+1,999))-1) As endResult

FROm #test

This should be fine, just need to take a backup in case you need to revert back to the old compatibility level.

You don't create Power View reports in the SSDT

http://technet.microsoft.com/en-us/library/hh231522%28v=sql.110%29.aspx

Thanks Keith, but I don't think I explained it very well, I need to compare the start_time between the current row and previous row ordered by userid and start_time.

e.g.

userid ...

Thanks, I have already taken this into consideration and have created the same indexes on both the origanl and load tables.

I have now implemented this logic and seems to be...

Thanks for all the help everyone. I have now found out what the user was actually trying to do and this update was to every row in the table,...

When the Inner Joins are used all dates are post 1753 (Min 1940 Max 2020) but as the table contains values pre 1753 it still caused an error, I didn't...

Thanks Anthony, it would seem that even though the inner joins removed the years prior to 1753 somehow this records still required processing so caused the conversion error. I...

This contains years prior to 1753 but these are removed once I add the inner joins into the query.

SELECT

DISTINCT

YEAR(U.START_DATE)

FROM

CSMADM.dbo.USAGES U

INNER JOIN CSMAUAT_MSCRM.dbo.Account A

on CONVERT(VARCHAR(12),U.MEM_MEMBER_NO) = A.AccountNumber

INNER JOIN ...

Thanks for that guys much appreciated.

That link you provided is very useful and exactly what I was looking for, should be able to rewrite the proc using this method.

Thanks Again.

Richie

Thanks for your response Sturner and appreciate this is a substantial piece of work. Not asking anyone to re-write this for me but if you can point me in...