Mr. Kapsicum (6/14/2013)

---

In want to find which columns are updated in a row of a table, and what is their new value and previous value.

i know a trigger can work...

may be this will get you started....

you will also need a "Tally" table...search this site for guides

suggest you read up on dynamic pivot/cross tabs as well

CREATE TABLE [dbo].[TransData](

[Fromdate] [datetime] NOT...

SQL is a BIG subject....as you don't give any details of what...

Jeff Moden (6/5/2013)

---

Tyekhan (6/5/2013)

---

Tired that code again but still not working,

Randomizing ContactID's and CallDateTime's...

(1000000 row(s) affected)

Building/populating the #HistoryTest table...

Msg 208, Level 16, State 1, Line 19

Invalid object name 'AdventureWorks.Person.Contact'.

J Livingston...

that's not the code from Jeff Moden...that's mine...go back a few posts

suggest you revisit the solution that Jeff Moden gave you.....just swap out the table name and I think you will need to replace "Num" with "PID"...that should give you what...

deleted....misread number pf rows

Hi Jeff

as always, you have done a lot more detailed testing on this.....have you come to any further conclusions ??

sorry for late reply but have had a few days vacation.

regards

Tyekhan (6/4/2013)

---

Hope this helps a bit,

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

...

hmmm....take a look at this data set....I don't think you are going to get what you expect.

suggest that you read up on using a calendar table instead of the CTE...

see if this helps....

DECLARE @Date DateTime;

set @Date = GetDate();

/* see changes made to the CTE */

WITH CTE AS

(

SELECT

TrnYear,

TrnMonth,

ROW_NUMBER() OVER( ORDER BY TrnYear DESC, TrnMonth DESC) rn

FROM (

SELECT DISTINCT

TrnYear,

TrnMonth

FROM #mytemptable

WHERE...

so....heres some dev code to get you started...have left it all broken out into tables so that you can see what is going on....

not sure how performant it will be..maybe...

Hi..just to be sure and possibly save time later on...does your sample data provide all the possible situations you have in the real data.

if it doesn't...then please provide all the...

Hi

where do these two columns come from

"TOTAL Remaining Points (Rewards + Bonus points)"

you have pointsearned and pointsused

??

maybe something along these lines

SELECT Product.ProductId, COUNT([Transaction].transactionId)

FROM Product LEFT OUTER JOIN

...