Thanks Jonathan. The Cross apply actually gives the best performance and the difference between your version and mine are minimal.

also, FWIW, only the Cross Apply gives the accurate results. I...

I think what I want is this..... but I believe there is an issue with my data.... no surprises there.

 

SELECT t1.CATEGORY, t1.ITEMID 
FROM ecomm.Promo_Items t1
CROSS APPLY (
SELECT...

default install of SQL will have "Failed Logins" logging enabled.

Right Click the server name, and then click Properties. Under the Security page....

Only pointing this out because if you...

we are running Dynamics AX 2012 for Retail here. (all in azure but that is irrelevant, only mentioning it for sympathy)

anyway, the transaction ID are suppose to be...

thanks Jeff. Much appreciated. Will you be in Seattle this year? we could catch up then....

this is great info. thanks so much.

one question though.

when you ALTER an existing table and ADD the needed datetime2 columns and then ALTER it again to SET SYSTEM_VERSION = ON,...

thanks guys.

running 2014 here and was not aware of LAG.... that is working perfectly.

Geoff A

Luis Cazares (5/20/2014)

---

It's still not pretty, it will read the table 4 times, but it should work better than the cursor. The recommendation is that you test both solutions to...

i came up with a solution. not pretty but it will work for now...

declare @bo-2 table ([ID] int, Col1 varchar(50), Col2 varchar(50), col3 varchar(50))

insert into

I was came to that conclusion a few minutes ago....

my data will include an Identity column....

create table TEMPDB..BO ([ID] int, Col1 varchar(50), Col2 varchar(50))

insert into TEMPDB..BO values

(1,'01','009920140516102116'),

(2,'071710811019600001000005',''),

(3,'070534524264000001000005',''),

(4,'001806505517000001000005',''),

(5,'001806595105200001000005',''),

(6,'001806505903100001000005',''),

(7,'02','009920140516102145'),

(8,'000000002703000001000003',''),

(9,'000000000259900001000003',''),

(10,'000000007025800001000003',''),

(11,'000000007056400001000003',''),

(12,'000000001920500001000003',''),

(13,'05','009920140516102250'),

(14,'007648400910500001000005',''),

(15,'007648400922800001000005',''),

(16,'007648401441300001000005',''),

(17,'007648401446800001000005',''),

(18,'007648401460400001000005','')

select *...

the logging they are referring to is transnational logging. It is eating up disk space and making your backups larger.

you could keep the process the same, but INSERT INTO could...