Query to trace history of change of data in a table

[karthik555]

Hi!

I have data as below

CREATE TABLE #Input (OldAsset Char(20), CurrAsset Char(20), Status Char(3), InpSrlNo INT Identity(1,1), GUID uniqueidentifier, InpProcstat char(1))


DECLARE @GUID Uniqueidentifier
SELECT @GUID = NewID()

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A1', 'A2', 'CAN', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A1', 'A2', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A1', 'A3', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A3', 'A4', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A4', 'A5', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A5', 'A6', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A6', 'A7', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A7', 'A8', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A8', 'A9', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A9', 'A10', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A10', 'A11', 'CAN', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A10', 'A11', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A11', 'A8', 'CON', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A8', 'A10', 'CAN', @GUID, 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, GUID, InpProcStat)
VALUES
('A8', 'A10', 'CON', @GUID, 'N')
go

This is nothing but change of name of an asset over a period of time.

IF the user gives any Asset (as CurrAsset) then I have to write a query to find all the changes it has undergone.

In the above example, if the user gives A10, then I have to display all the rows. (ProcStatus and GUID are for processing purpose only. I need to display only the first three columns)

However there can be other rows belonging to other asset in the table as well.

Tried with expanding hierarchy concept. Doesn't work eactly.

If anyone has any idea how to go about, it will be great help.

Thanks in advance..

Karthik

 

[vongrunt]

> This is nothing but change of name of an asset over a period of time.

What about primary key? It is not logged in history table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[karthik555]

There is only an identity column which serves the purpose of Primary key(system key). There is no user key. WHich means A1 can become A2, A2 can become A3 and A3 can become A1 and the whole cycle can repeat again.

This adds to the complexity.

If the user gives A3 as input I have to retrieve data pertaining to both the above cycles.

Regards

Karthik

 

[vongrunt]

OK, what with rows 11/12 and 14/15? For these entries looks like OldAsset got changed to CurrAssed twice... do you want both or only last one (w/ greater identity value)? That said, what is the meaning of CAN/CON values?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[karthik555]

Thanks for response...

If you see the differece between 11 and 12 it is the status..
Former is cancelled (CAN) and the later is confirmed (CON)..

Same is true of 14 and 15.

All the four records need to be shown in the output in the same order (apart from other records).

Thanks again for your response..

 

[karthik555]

Well!
I managed to write a query...

Thanks for your response though!


CREATE TABLE #Input (OldAsset Char(20), CurrAsset Char(20), Status Char(3), InpSrlNo INT Identity(1,1), InpProcstat char(1))



INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A1', 'A2', 'CAN', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A1', 'A2', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('Ax', 'AAX', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('AAX', 'AAY', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A2', 'A3', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A3', 'A4', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A4', 'A5', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A5', 'A6', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A6', 'A7', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A7', 'A8', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A8', 'A9', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A9', 'A10', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A10', 'A11', 'CAN', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A10', 'A11', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A11', 'A8', 'CON', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A8', 'A10', 'CAN', 'N')

INSERT INTO #Input (OldAsset, CurrAsset, Status, InpProcStat)
VALUES
('A8', 'A10', 'CON', 'N')
go



CREATE TABLE #stack
(
OldAsset char(20), CurrAsset char(20), Status char(3),
Currstatus Char(3), Oldstatus char(1), StackSrlno int
Identity(1,1), ParentSrlNo int
)

DECLARE @CurrAsset Char(20), @CurrCount Int, @CurrStatusCount Int, @OldStatusCount INT,
@InpSrlNo Int, @StackIdent Int, @InpSrlNo_Tmp INT

SELECT @CurrAsset = 'A10'

SELECT @InpSrlNo = InpSrlNo
FROM #Input
WHERE CurrAsset = @CurrAsset
AND InpSrlNo = (SELECT MAX(InpSrlNO) FROM #Input WHERE CurrAsset = @CurrAsset)


WHILE IsNull(@InpSrlNo,0) <> 0
BEGIN
PRINT '@InpSrlNo'
PRINT @InpSrlNo

INSERT INTO #Stack
(OldAsset, CurrAsset, Status, ParentSrlNo)
SELECT OldAsset, CurrAsset, Status, InpSrlNo
FROM #Input
WHERE InpSrlNo = @InpSrlNo

SELECT @StackIdent = @@Identity

-- SELECT '#St after ins',* FROM #Stack
SELECT @InpSrlNo_Tmp = @InpSrlNo

SELECT TOP 1 @InpSrlNo = InpSrlNo
FROM #Input I
WHERE I.CurrAsset = (SELECT OldAsset FROM #Stack where StackSrlNo = @StackIdent)
-- AND I.InpSrlNo < S.ParentSrlNo
AND InpSrlNo < @InpSrlNo
ORDER BY InpSrlNo DESC

IF @InpSrlNo_Tmp = @InpSrlNo
BREAK
END

INSERT INTO #Stack
(OldAsset, CurrAsset, Status, ParentSrlNo)
SELECT I.OldAsset, I.CurrAsset, I.Status, I.InpSrlNo
FROM #Input I--, #Stack S
WHERE I.oldAsset IN (SELECT OldAsset FROM #Stack)
AND I.InpSrlNO NOT IN (SELECT ParentSrlNo FROM #Stack)

INSERT INTO #Stack
(OldAsset, CurrAsset, Status, ParentSrlNo)
SELECT I.OldAsset, I.CurrAsset, I.Status, I.InpSrlNo
FROM #Input I--, #Stack S
WHERE I.CurrAsset IN (SELECT CurrAsset FROM #Stack )
AND I.InpSrlNO NOT IN (SELECT ParentSrlNo FROM #Stack)

SELECT '#Stack',* FROM #Stack order by ParentSrlNo

DROP TABLE #INPUT
DROP TABLE #Stack