Help with Query please?? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with Query please??

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2011-04-19 : 16:35:47

Hi I have data from a history table that is being populated using an update trigger. It collects all fields from the table for 'deleted' and 'inserted' and places them into a history table. Please run the code below for a brief sample.

Now I would like a query that would allow me to display this history to a visitor in my application like so:

On 4/19/2001 User 2 Change Value From 16.79 To 2.99.
On 4/19/2001 User 2 Change Value From 2.99 To 2.97.
On 4/19/2001 User 2 Change Description From LONG ROLL FILM To SHORT ROLL FILM.
On 4/19/2001 User 2 Change Description From SHORT ROLL FILM To LONG ROLL FILM.
On 4/19/2001 User 2 Change Value From 2.97 To 3.42.

So I would need to know what fields have changed, the before value and after value for each group of 2 rows. I hope I explained that correclty.

Any help would be appreciated. Thanks.

Here is some test data to work with.


USE [Test]
GO

CREATE TABLE [dbo].[Brimms_History22](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[BrimmsID] [bigint] NOT NULL,
	[PartNo] [varchar](50) NOT NULL,
	[Description] [varchar](255) NOT NULL,
	[Value] [money] NOT NULL,
	[UpdateBy] [int] NOT NULL,
	[UpdateDate] [datetime] NOT NULL,
	[TriggerFunction] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Brimms_88] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Insert Into [dbo].[Brimms_History22] (BrimmsID, PartNo, [Description], [Value], UpdateBy, UpdateDate, TriggerFunction)
Values (5,'CK-57458','LONG ROLL FILM',16.79,2,'2011-04-19 15:41:13.547','Old-Value')
Insert Into [dbo].[Brimms_History22] (BrimmsID, PartNo, [Description], [Value], UpdateBy, UpdateDate, TriggerFunction)
Values (5,'CK-57458','LONG ROLL FILM',2.99,2,'2011-04-19 15:41:13.547','New-Value')

Insert Into [dbo].[Brimms_History22] (BrimmsID, PartNo, [Description], [Value], UpdateBy, UpdateDate, TriggerFunction)
Values (5,'CK-57458','LONG ROLL FILM',2.99,2,'2011-04-19 15:41:19.547','Old-Value')
Insert Into [dbo].[Brimms_History22] (BrimmsID, PartNo, [Description], [Value], UpdateBy, UpdateDate, TriggerFunction)
Values (5,'CK-57458','LONG ROLL FILM',2.97,2,'2011-04-19 15:41:19.547','New-Value')

Insert Into [dbo].[Brimms_History22] (BrimmsID, PartNo, [Description], [Value], UpdateBy, UpdateDate, TriggerFunction)
Values (5,'CK-57458','LONG ROLL FILM',2.97,2,'2011-04-19 15:41:25.547','Old-Value')
Insert Into [dbo].[Brimms_History22] (BrimmsID, PartNo, [Description], [Value], UpdateBy, UpdateDate, TriggerFunction)
Values (5,'CK-57458','SHORT ROLL FILM',2.97,2,'2011-04-19 15:41:25.547','New-Value')

Insert Into [dbo].[Brimms_History22] (BrimmsID, PartNo, [Description], [Value], UpdateBy, UpdateDate, TriggerFunction)
Values (5,'CK-57458','SHORT ROLL FILM',2.97,2,'2011-04-19 15:41:31.547','Old-Value')
Insert Into [dbo].[Brimms_History22] (BrimmsID, PartNo, [Description], [Value], UpdateBy, UpdateDate, TriggerFunction)
Values (5,'CK-57458','LONG ROLL FILM',3.42,2,'2011-04-19 15:41:31.547','New-Value')

Select * From [dbo].[Brimms_History22]

Drop Table [dbo].[Brimms_History22]

JBelthoff
› As far as myself... I do this for fun!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-19 : 19:17:06

You can get the list of changes and what changed using a query such as this:


SELECT 
   DATEADD(dd,DATEDIFF(dd,0,n.UpdateDate),0) ChangeDate,
   n.UpdateBy,
   o.Description OldDescription,
   n.Description NewDescription,
   o.Value OldValue,
   n.Value NewValue,
   CASE WHEN o.Description != n.Description THEN 'Y' ELSE 'N' END AS Description_Changed,
   CASE WHEN o.Value != n.Value THEN 'Y' ELSE 'N' END AS Value_Changed,
   n.ID
FROM
   [dbo].[Brimms_History22] n
   INNER JOIN [dbo].[Brimms_History22] o 
      ON o.ID = n.ID-1
WHERE
   o.TriggerFunction = 'Old-Value'
   AND n.TriggerFunction = 'New-Value'

To format the output from this query into the type of string representation you want, you could concatenate the appropriate columns. If you are able to do that on the client/presentation layer, that would probably be a better approach. Or, perhaps even a tabular representation from the above query would be sufficent?

But, sometimes customers are really pesky creatures, so you have to do what you have to do. In that case, you can do it in SQL like this:


WITH CTE AS
(
--- INSERT THE CODE FROM ABOVE HERE
)
SELECT
   Id,
   'On ' 
   + cast(ChangeDate AS VARCHAR(32))
   + ' User '
   + CAST(UpdateBy AS VARCHAR(32))
   + ' Change Description From '
   + OldDescription
   + ' To '
   + NewDescription
   + '.'
FROM
   CTE
WHERE
   Description_Changed = 'Y'
UNION ALL
SELECT
   Id,
   'On ' 
   + cast(ChangeDate AS VARCHAR(32))
   + ' User '
   + CAST(UpdateBy AS VARCHAR(32))
   + ' Change Value From '
   + cast(OldValue AS VARCHAR(32))
   + ' To '
   + cast(NewValue AS VARCHAR(32))
   + '.'
FROM
   CTE
WHERE
   Value_Changed = 'Y'
ORDER BY
   Id

JBelthoff
Posting Yak Master

173 Posts

Posted - 2011-04-20 : 08:07:01

This is awesome. Thank you Thank you Thank you!

JBelthoff
› As far as myself... I do this for fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources