Audit Example, Sql Server

Audit Example

--https://stackoverflow.com/questions/10781160/sql-return-audit-for-records-for-each-column/16243274#16243274

CREATE TABLE dbo.OrderHistory (
	ChangeDate datetime NOT NULL,
	OrderID int NOT NULL,
	DeliveryDate datetime NOT NULL,
	Quantity int NOT NULL,
	SpecialNotes varchar(100) NULL
);

INSERT dbo.OrderHistory
VALUES
	('20130301 11:28', 1, '20130401', 25, NULL),
	('20130301 11:56', 1, '20130401', 30, NULL),
	('20130305 10:18', 1, '20130402', 30, 'Customer called to ask for delivery date adjustment.'),
	('20130301 11:37', 2, '20130305', 17, NULL)
;

WITH ColValues AS (
	SELECT
		Grp = Row_Number() OVER (PARTITION BY H.OrderID, U.ColName ORDER BY H.ChangeDate ASC, X.Which) / 2,
		H.OrderID,
		H.ChangeDate,
		U.*,
		X.Which
	FROM
		dbo.OrderHistory H
		CROSS APPLY (VALUES
			('DeliveryDate', Convert(varchar(1000), DeliveryDate, 121)),
			('Quantity', Convert(varchar(1000), Quantity)),
			('SpecialNotes', Convert(varchar(1000), SpecialNotes))
		) U (ColName, Value)
		CROSS JOIN (VALUES (1), (2)) X (Which)
)
SELECT
	V.OrderID,
	V.ColName,
	DateChanged = Max(V.ChangeDate),
	OldValue = Max(F.Value),
	NewValue = Max(T.Value)
FROM
	ColValues V
	OUTER APPLY (SELECT V.ColName, V.Value WHERE V.Which = 2) F
	OUTER APPLY (SELECT V.ColName, V.Value WHERE V.Which = 1) T
GROUP BY
	OrderID,
	V.ColName,
	V.Grp
HAVING
	Count(*) = 2
	AND EXISTS (
		SELECT Max(F.Value)
		EXCEPT SELECT Max(T.Value)
	)
;


run \| edit \| history \| help	0