Complex Query: Please HELP!!!!

Question

Complex Query: Please HELP!!!!

suresh_yaram

SSC Veteran

Points: 267
More actions
September 12, 2003 at 7:05 am

#162359

Hi
We have 3 tables:
1) Table having current rows (MainTbl)
2) Table having the history rows (AuditTbl)
3) Table having both the Current rows & history rows (BaseTbl).
Create Table MainTbl(
RowID nvarchar(15),Created Datetime,
CreatedBy nvarchar(25),LastUpdated Datetime,
LastUpdatedBy nvarchar(25),
Assignee nvarchar(25),Status nvarchar(25))
GO
Insert Into MainTbl
Select '2-IHW81','2003-09-03 14:10:15.000','HARRY','2003-09-04 22:02:52.000','LUKE','LUKE','3rd Party Hold'
GO
Create Table AuditTbl(
RowID nvarchar(15),Created Datetime,
CreatedBy nvarchar(25),Field nvarchar(25),
OldValue nvarchar(25),NewValue nvarchar(25))
GO
Insert Into AuditTbl
Select '2-IHW81','2003-09-03 15:24:57.000','TOM','Assignee',NULL,'TOM'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 00:23:59.000','TOM','Status','New','In Process'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 00:24:38.000','TOM','Assignee','TOM','LUKE'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 00:24:38.000','TOM','Status','In Process','Transferred'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 15:52:27.000','LUKE','Status','Transferred','In Process'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 16:05:46.000','LUKE','Status','In Process','3rd Party Hold'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 16:47:40.000','DICK','Status','3rd Party Hold','New Mail'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 16:55:36.000','DICK','Status','3rd Party Hold','New Mail'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 16:55:36.000','LUKE','Status','New Mail','3rd Party Hold'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 19:19:47.000','LUKE','Status','New Mail','3rd Party Hold'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 21:47:10.000','KEN','Status','3rd Party Hold','New Mail'
Insert Into AuditTbl
Select '2-IHW81','2003-09-04 22:02:52.000','LUKE','Status','New Mail','3rd Party Hold'
GO
Create Table BaseTbl(
RowID nvarchar(15),Created Datetime,
CreatedBy nvarchar(25),LastUpdated Datetime,
LastUpdatedBy nvarchar(25),
Assignee nvarchar(25),Status nvarchar(25))
GO
The current Row Can be updated from the Front-end. When it is updated, the record is updated in the MainTbl and an audit row is created in the AuditTbl.
Finally,the BaseTbl should be populated with the current & History Data as shown Below.
If you see the data inserts in to AuditTbl,
there are 2 audits took place at the same time on the same field(Status) by 2 users., which is we did not foresee.
'2-IHW81','2003-09-04 16:55:36.000','DICK','Status','3rd Party Hold','New Mail'
'2-IHW81','2003-09-04 16:55:36.000','LUKE','Status','New Mail','3rd Party Hold'
Now, we have to tackle this situation, where 2 users performing audit on the same field at the same time and get the data as shown in the following BaseTbl.
We have already logic in place to get the data as shown in the BaseTbl, if the audit are done at different times.
FINAL OUTPUT
RowIDCreatedDateCreatedBy LastUpdated LastUpdatedByCurrentFlagAssignee Status
2-IHW812003-09-03 15:24:57.000TOM 2003-09-03 15:24:57.000 TOMFNULL New
2-IHW812003-09-04 00:23:59.000TOM 2003-09-04 00:23:59.000 TOMFTOM New
2-IHW812003-09-04 00:24:38.000TOM 2003-09-04 00:24:38.000 TOMFTOM In Process
2-IHW812003-09-04 15:52:27.000LUKE 2003-09-04 15:52:27.000 LUKEFLUKE Transferred
2-IHW812003-09-04 16:05:46.000LUKE 2003-09-04 16:05:46.000 LUKEFLUKE In Process
2-IHW812003-09-04 16:47:40.000DICK 2003-09-04 16:47:40.000 DICKFLUKE 3rd Party Hold
**We should get the data like this inthe BaseTbl**
2-IHW812003-09-04 16:55:36.000LUKE 2003-09-04 16:55:36.000 LUKEFLUKE New Mail
2-IHW812003-09-04 16:55:36.000DICK 2003-09-04 16:55:36.000 DICKFLUKE 3rd Party Hold
************
2-IHW812003-09-04 19:19:47.000LUKE 2003-09-04 19:19:47.000 LUKEFLUKE New Mail
2-IHW812003-09-04 21:47:10.000KEN 2003-09-04 21:47:10.000 KENFLUKE 3rd Party Hold
2-IHW812003-09-04 22:02:52.000LUKE 2003-09-04 22:02:52.000 LUKEFLUKE New Mail
2-IHW812003-09-03 14:10:15.000HARRY 2003-09-04 22:02:52.000 LUKETLUKE 3rd Party Hold

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65142 More actions · Answer 1

Not sure what your question is but presume the problem is with identical datetime's. If you want to guarantee sequence of events in AuditTbl and BaseTbl then add an IDENTITY column and use it in an order by eg RowID ASC,Created ASC,[identitycolumn] ASC where [identitycolumn] is the name of the identity column you add.

Far away is close at hand in the images of elsewhere.
Anon.

suresh_yaram SSC Veteran Points: 267 More actions · Answer 2

Hi David

We dont have privilege to add an identity column to the AuditTbl. We have read-only (SELECT) permissions. We takethe data from the MainTbl & AuditTbl and process on our server and build the BaseTbl.

suresh_yaram SSC Veteran Points: 267 More actions · Answer 3

MainTbl:

RowIDCreatedCreatedByLastUpdatedLastUpdatedByAssigneeStatus

2-IHW812003-09-03 14:10:15.000HARRY2003-09-04 22:02:52.000LUKELUKE3rd Party Hold

AuditTbl:

RowIDCreatedCreatedByFieldOldValueNewValue

2-IHW812003-09-04 22:02:52.000LUKEStatusNew Mail3rd Party Hold

2-IHW812003-09-04 21:47:10.000KENStatus3rd Party HoldNew Mail

2-IHW812003-09-04 19:19:47.000LUKEStatusNew Mail3rd Party Hold

2-IHW812003-09-04 16:55:36.000DICKStatus3rd Party HoldNew Mail

2-IHW812003-09-04 16:55:36.000LUKEStatusNew Mail3rd Party Hold

2-IHW812003-09-04 16:47:40.000DICKStatus3rd Party HoldNew Mail

2-IHW812003-09-04 16:05:46.000LUKEStatusIn Process 3rd Party Hold

2-IHW812003-09-04 15:52:27.000LUKEStatusTransferredIn Process

2-IHW812003-09-04 00:24:38.000TOMAssigneeTOMLUKE

2-IHW812003-09-04 00:24:38.000TOMStatusIn ProcessTransferred

2-IHW812003-09-04 00:23:59.000TOMStatusNewIn Process

2-IHW812003-09-03 15:24:57.000TOMAssigneeNULLTOM

The table structures will remain the same and will not change owing to some business constraints. We cannot add an identity to the AuditTbl table so that the insert/update trigger can do the ordering of the AuditTbl rows. All we have is the OldValue & the NewValue columns based on which we need to order the AuditTbl rows.

Suppose the AuditTbl is Ordered By Created DESC.

The latest AuditTbl row becomes the the 1st row. You can see that the MainTbl.Status (3rd Party Hold)=AuditTbl.NewValue(3rd Party Hold).

The 2nd row is the one created on '2003-09-04 21:47:10.000'. The OldValue (New Mail) of previous record (2003-09-04 22:02:52.000) is the NewValue (New Mail) of the this record.

The 3rd row is the one created on '2003-09-04 19:19:47.000'. The OldValue (3rd Party Hold) of previous record (2003-09-04 21:47:10.000) is the NewValue (3rd Party Hold) of the this record.

Both the 4th & 5th rows are created at the same time by different users and there is a problem in ordering. The best way to get the 4th rows is the match the NewValue with the OldValue (New Mail) of the previous record (2003-09-04 19:19:47.000). Here the record created by 'DICK' becomes the 4th rows as its NewValue matches with the OldValue of previous record. The record created by 'LUKE' becomes the 5th rows & so on.

I am finding it difficult to order this programmatically. Can you suggest how to do this?

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 4

Suresh_yaram just a side attraction.

Why do the dates seem to to the nearest second or is just coincidence. DateTime is to the nearest 3/1000 of a second which may help to lessen the occurrence of the problem, although it may still occur.

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Try this, it may do it.

SELECTa.RowID, 

a.Created, 

a.CreatedBy, 

a.Field, 

a.OldValue, 

a.NewValue 

FROMAuditTbl a 

ORDER BY a.RowID, 

a.Created, 

a.Field, 

(CASE WHEN (SELECT TOP 1 NewValue 

FROM AuditTbl 

WHERE RowID = a.RowID 

AND Field = a.Field 

AND Created < a.Created 

ORDER BY Created DESC) = a.OldValue THEN 1 ELSE 2 END)

Far away is close at hand in the images of elsewhere.
Anon.

tnvenkat SSC Enthusiast Points: 154 More actions · Answer 6

tnvenkat

SSC Enthusiast

Points: 154

September 15, 2003 at 8:22 am

#474191

Good One..

Venkat

tnvenkat SSC Enthusiast Points: 154 More actions · Answer 7

Thanks A Lot for the suggestion.

It helps a lot considering 2 users modifying at the same time for a RowID. But this does not seem to work with more than 2 users updating at the same time or for the data given below.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AuditTbl1]') and OBJECTPROPERTY(id, N'IsUserTable')

= 1)

Drop Table [dbo].[AuditTbl1]

GO

Create Table AuditTbl1(

RowID nvarchar(15),

Created Datetime,CreatedBy nvarchar(25),

Field nvarchar(25),

OldValue nvarchar(25),NewValue nvarchar(25))

GO

Insert Into AuditTbl1

Select '2-IHW81','2003-09-03 15:24:57.000','TOM','Assignee',NULL,'TOM'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 00:23:59.000','TOM','Status','New','In Process'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 00:24:38.000','TOM','Assignee','TOM','LUKE'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 00:24:38.000','TOM','Status','In Process','Transferred'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 15:52:27.000','LUKE','Status','Transferred','In Process'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 16:05:46.000','LUKE','Status','In Process','3rd Party Hold'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 16:47:40.000','DICK','Status','3rd Party Hold','New Mail'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 16:55:36.000','LUKE','Status','New Mail','3rd Party Hold'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 16:55:36.000','DICK','Status','3rd Party Hold','New Mail'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 17:55:36.000','LUCK','Status','New Mail','Updated'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 17:55:36.000','SAM','Status','Updated','3rd Party Hold'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 17:55:36.000','FLUKE','Status','3rd Party Hold','Updated'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 17:55:36.000','LICK','Status','Updated','New Mail'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 19:19:47.000','LUKE','Status','New Mail','3rd Party Hold'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 21:47:10.000','KEN','Status','3rd Party Hold','New Mail'

Insert Into AuditTbl1

Select '2-IHW81','2003-09-04 22:02:52.000','LUKE','Status','New Mail','3rd Party Hold'

GO

SELECTa.RowID,a.Created,a.CreatedBy,a.Field,a.OldValue,a.NewValue

FROMAuditTbl1 a

ORDER BY a.RowID,a.Created,a.Field,

(CASE WHEN (SELECT TOP 1 NewValue FROM AuditTbl1 WHERE RowID = a.RowID AND Field = a.Field AND Created < a.Created

ORDER BY Created DESC) = a.OldValue THEN 1 ELSE 2 END)

01) 2-IHW812003-09-03 15:24:57.000TOMAssigneeNULLTOM

02) 2-IHW812003-09-04 00:23:59.000TOMStatusNewIn Process

03) 2-IHW812003-09-04 00:24:38.000TOMAssigneeTOMLUKE

04) 2-IHW812003-09-04 00:24:38.000TOMStatusIn ProcessTransferred

05) 2-IHW812003-09-04 15:52:27.000LUKEStatusTransferredIn Process

06) 2-IHW812003-09-04 16:05:46.000LUKEStatusIn Process 3rd Party Hold

07) 2-IHW812003-09-04 16:47:40.000DICKStatus3rd Party HoldNew Mail

08) 2-IHW812003-09-04 16:55:36.000LUKEStatusNew Mail3rd Party Hold

09) 2-IHW812003-09-04 16:55:36.000DICKStatus3rd Party HoldNew Mail

10) 2-IHW812003-09-04 17:55:36.000FLUKEStatus3rd Party HoldUpdated

11) 2-IHW812003-09-04 17:55:36.000LICKStatusUpdatedNew Mail

12) 2-IHW812003-09-04 17:55:36.000LUCKStatusNew MailUpdated

13) 2-IHW812003-09-04 17:55:36.000SAMStatusUpdated3rd Party Hold

14) 2-IHW812003-09-04 19:19:47.000LUKEStatusNew Mail3rd Party Hold

15) 2-IHW812003-09-04 21:47:10.000KENStatus3rd Party HoldNew Mail

16) 2-IHW812003-09-04 22:02:52.000LUKEStatusNew Mail3rd Party Hold

The Order should be 01,02,03,04,05,06,07,08,09,12,13,10,11,14,15,16.

David Burrows SSC Guru Points: 65142 More actions · Answer 8

See if this works using a temp table.

CREATE TABLE #A(ID1 int,ID2 INT, 

RowID nvarchar(15), 

Created Datetime,CreatedBy nvarchar(25), 

Field nvarchar(25), 

OldValue nvarchar(25),NewValue nvarchar(25)) 



INSERT INTO #A 

SELECT 0,0,RowID,Created,CreatedBy,Field,OldValue,NewValue 

FROM AuditTbl 

ORDER BY RowID,Created,Field 



DECLARE @ID1 int,@ID2 int,@RowID nvarchar(15) 

DECLARE @Created Datetime,@Field nvarchar(25) 

DECLARE @NewValue nvarchar(25),@Flag int 

SET @ID1 = 0 

SET @RowID = '' 

SET @Created = '' 

SET @Field = '' 



UPDATE #A 

SET @ID1 = @ID1 + (CASE WHEN RowID = @RowID 

AND Created = @Created 

AND Field = @Field THEN 0 ELSE 1 END), 

ID1 = @ID1, 

@RowID = RowID, 

@Created = Created, 

@Field = Field 



SET @ID1 = 0 

SELECT @ID1 = ID1 FROM #A GROUP BY ID1 HAVING COUNT(*) > 1 

WHILE (@ID1 <> 0) 

BEGIN 

SELECT @RowID = RowID,@Field = Field FROM #A WHERE ID1 = @ID1 

SET @NewValue = '' 

IF (@ID1 = 1) 

BEGIN 

SET @NewValue = (SELECT TOP 1 a.OldValue 

FROM #A a 

INNER JOIN #A b 

ON b.ID1 = a.ID1 

AND a.NewValue = b.OldValue 

WHERE a.ID1 = @ID1) 

select @NewValue 

END 

ELSE 

BEGIN 

SELECT @NewValue = NewValue 

FROM #A 

WHERE RowID = @RowID 

AND Field = @Field 

AND ID1 = (SELECT MAX(ID1) FROM #A WHERE ID1 < @ID1) 

END 

SET @ID2 = 0 

SET @Flag = 1 

WHILE (@Flag > 0) 

BEGIN 

SET @Flag = 0  

SET @ID2 = @ID2 + 1 

UPDATE #A 

SET @Flag = @Flag + 1, 

ID2 = (CASE WHEN @Flag = 1 THEN @ID2 ELSE ID2 END), 

@NewValue = (CASE WHEN @Flag = 1 THEN NewValue ELSE @NewValue END) 

WHERE ID1 = @ID1 AND ID2 = 0 

AND ((OldValue IS NULL AND @NewValue IS NULL) OR (OldValue = @NewValue)) 

END 

SET @ID1 = 0 

SELECT @ID1 = ID1 FROM #A WHERE ID2 = 0 GROUP BY ID1 HAVING COUNT(*) > 1 

END 



SELECT RowID,Created,CreatedBy,Field,OldValue,NewValue FROM #A ORDER BY ID1,ID2 



DROP TABLE #A

Far away is close at hand in the images of elsewhere.
Anon.

tnvenkat SSC Enthusiast Points: 154 More actions · Answer 9

Hi David,

Thanks a Lot for the beautiful peace of code. You are a saviour

tnvenkat SSC Enthusiast Points: 154 More actions · Answer 10

Hi David,

The suggestion worked fine uptil a point. Once the number of audited fields for a user, for a created date combination became more than 1, the system is going into a loop and hanging.

SET NOCOUNT ON

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AuditTbl2]') and OBJECTPROPERTY(id, N'IsUserTable')

= 1)

Drop Table [dbo].[AuditTbl2]

GO

Create Table AuditTbl2(

RowID nvarchar(15),

Created Datetime,CreatedBy nvarchar(25),

Field nvarchar(25),

OldValue nvarchar(25),NewValue nvarchar(25))

GO

Insert Into AuditTbl2

Select '2-IHW81','2003-09-03 15:24:57.000','TOM','Assignee',NULL,'TOM'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 00:23:59.000','TOM','Status','New','In Process'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 00:24:38.000','TOM','Assignee','TOM','LUKE'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 00:24:38.000','TOM','Status','In Process','Transferred'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 15:52:27.000','LUKE','Status','Transferred','In Process'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 16:05:46.000','LUKE','Status','In Process','3rd Party Hold'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 16:47:40.000','DICK','Status','3rd Party Hold','New Mail'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 16:55:36.000','LUKE','Status','New Mail','3rd Party Hold'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 16:55:36.000','DICK','Status','3rd Party Hold','New Mail'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 17:55:36.000','LUCK','Assignee','LUKE','LUCK'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 17:55:36.000','LUCK','Status','New Mail','Updated'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 17:55:36.000','SAM','Assignee','LUCK','SAM'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 17:55:36.000','SAM','Status','Updated','3rd Party Hold'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 17:55:36.000','FLUKE','Status','3rd Party Hold','Updated'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 17:55:36.000','LICK','Status','Updated','New Mail'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 19:19:47.000','LUKE','Status','New Mail','3rd Party Hold'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 21:47:10.000','KEN','Status','3rd Party Hold','New Mail'

Insert Into AuditTbl2

Select '2-IHW81','2003-09-04 22:02:52.000','LUKE','Status','New Mail','3rd Party Hold'

A person can change more than 1 field at a go.

TOM has modified 2 fields (Assignee & Status) at the same time (2003-09-04 00:24:38.000). So both of the records should have the same Order. Likewise, SAM has modified 2 fields (Assignee & Status) at the same time (2003-09-04 17:55:36.000) and LUCK has modified 2 fields (Assignee & Status) at the same time (2003-09-04 17:55:36.000). Looking at the OldValue and NewValue, the order should be as follows:

SNoRowIDCreatedCreatedByField

12-IHW812003-09-03 15:24:57.000TOMAssignee

22-IHW812003-09-04 00:23:59.000TOMStatus

32-IHW812003-09-04 00:24:38.000TOMAssignee

32-IHW812003-09-04 00:24:38.000TOMStatus

42-IHW812003-09-04 15:52:27.000LUKEStatus

52-IHW812003-09-04 16:05:46.000LUKEStatus

62-IHW812003-09-04 16:47:40.000DICKStatus

72-IHW812003-09-04 16:55:36.000LUKEStatus

82-IHW812003-09-04 16:55:36.000DICKStatus

92-IHW812003-09-04 17:55:36.000LUCKAssignee

92-IHW812003-09-04 17:55:36.000LUCKStatus

102-IHW812003-09-04 17:55:36.000SAMAssignee

102-IHW812003-09-04 17:55:36.000SAMStatus

112-IHW812003-09-04 17:55:36.000FLUKEStatus

122-IHW812003-09-04 17:55:36.000LICKStatus

132-IHW812003-09-04 19:19:47.000LUKEStatus

142-IHW812003-09-04 21:47:10.000KENStatus

152-IHW812003-09-04 22:02:52.000LUKEStatus

Finally, the idea is to consolidate into one single row the data created at a given time for a user regardless of the number of fields. It should be similar to running the query given below:

SELECT DISTINCT RowID,Created,CreatedBy

FROM AuditTbl2.

Thanks in Advance!!!

David Burrows SSC Guru Points: 65142 More actions · Answer 11

CREATE TABLE #A(ID1 int,ID2 INT, 

RowID nvarchar(15), 

Created Datetime,CreatedBy nvarchar(25), 

Field nvarchar(25), 

OldValue nvarchar(25),NewValue nvarchar(25)) 

INSERT INTO #A 

SELECT 0,0,RowID,Created,CreatedBy,Field,OldValue,NewValue 

FROM AuditTbl 

ORDER BY RowID,Created,Field 

DECLARE @ID1 int,@ID2 int,@RowID nvarchar(15) 

DECLARE @Created Datetime,@Field nvarchar(25) 

DECLARE @NewValue nvarchar(25),@Flag int 

SET @ID1 = 0 

SET @RowID = '' 

SET @Created = '' 

SET @Field = '' 

UPDATE #A 

SET @ID1 = @ID1 + (CASE WHEN RowID = @RowID 

AND Created = @Created 

AND Field = @Field THEN 0 ELSE 1 END), 

ID1 = @ID1, 

@RowID = RowID, 

@Created = Created, 

@Field = Field 

SET @ID1 = 0 

SELECT @ID1 = ID1 FROM #A GROUP BY ID1 HAVING COUNT(*) > 1 

WHILE (@ID1 <> 0) 

BEGIN 

SELECT @RowID = RowID,@Field = Field FROM #A WHERE ID1 = @ID1 

SET @NewValue = '' 

IF (@ID1 = 1) 

BEGIN 

SET @NewValue = (SELECT TOP 1 a.OldValue 

FROM #A a 

INNER JOIN #A b 

ON b.ID1 = a.ID1 

AND b.Field = a.Field 

AND a.NewValue = b.OldValue 

WHERE a.ID1 = @ID1) 

END 

ELSE 

BEGIN 

SELECT @NewValue = NewValue 

FROM #A 

WHERE RowID = @RowID 

AND Field = @Field 

AND ID1 = (SELECT MAX(ID1) FROM #A WHERE ID1 < @ID1 AND Field = @Field) 

END 

SET @ID2 = 0 

SET @Flag = 1 

WHILE (@Flag > 0) 

BEGIN 

SET @Flag = 0  

SET @ID2 = @ID2 + 1 

UPDATE #A 

SET @Flag = @Flag + 1, 

ID2 = (CASE WHEN @Flag = 1 THEN @ID2 ELSE ID2 END), 

@NewValue = (CASE WHEN @Flag = 1 THEN NewValue ELSE @NewValue END) 

WHERE ID1 = @ID1 AND ID2 = 0 

AND ((OldValue IS NULL AND @NewValue IS NULL) OR (OldValue = @NewValue)) 

END 

SET @ID1 = 0 

SELECT @ID1 = ID1 FROM #A WHERE ID2 = 0 GROUP BY ID1 HAVING COUNT(*) > 1 

END 

SELECT RowID,Created,CreatedBy,Field,OldValue,NewValue FROM #A ORDER BY ID1,ID2 

DROP TABLE #A

quote:
Finally, the idea is to consolidate into one single row the data created at a given time for a user regardless of the number of fields. It should be similar to running the query given below:
SELECT DISTINCT RowID,Created,CreatedBy
FROM AuditTbl2

Can you be a bit more specific on what data is to be consolidated with example output.

Far away is close at hand in the images of elsewhere.
Anon.

tnvenkat SSC Enthusiast Points: 154 More actions · Answer 12

Hi David,

Thanks for the Code. The system does not hang anymore..

Regarding the consolidation thing, I will reply back very soon.