Query to obtain history of a transaction...

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)

Query to obtain history of a transaction...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-10-27 : 12:05:53

Hi Guys,

I am trying to develop an sp where you enter in a ref no, and it will display the ref no, and all ref no's associated with that ref no (via an old_ref_no link)


CREATE TABLE TEMP_REF_HISTORY (REF_NO varchar(10), OLD_REF_NO varchar(10), Amount decimal(18,2))

INSERT INTO TEMP_REF_HISTORY VALUES('RA-1','','3963.96')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-2','','32974.92')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-3','','31450.32')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-4','','304.92')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-5','','2490.18')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-6','RA-2','3150.84')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-7','','25743.96')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-8','','493.68')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-9','','5713.62')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-10','','499183.08')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-11','','32982.18')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-12','','316122.18')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-13','','3296.04')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-14','','972.84')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-15','','3959988.78')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-16','RA-6','29.04')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-17','','329858.1')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-18','','486.42')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-19','','50.82')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-20','','43.56')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-21','','50.82')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-22','','551.76')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-23','','232.32')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-24','','3963.96')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-25','RA-16','36.3')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-26','RA-10','524564.04')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-27','','3288.78')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-28','','2889.48')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-29','','97516.32')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-30','RA-26','24858.24')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-31','','97516.32')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-32','','24858.24')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-33','','39356.46')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-34','','392.04')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-35','','29.04')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-36','','326.7')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-37','RA-30','326.7')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-38','','32982.18')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-39','','316122.18')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-40','RA-25','399.3')

Now when I run the example searches below, I want the following results:

Search for "RA-2":
REF_NO OLD_REF_NO AMOUNT
RA-2 32974.92
RA-6 RA-2 3150.84
RA-16 RA-6 29.04
RA-25 RA-16 36.3
RA-40 RA-25 399.3

Search for "RA-32":
REF_NO OLD_REF_NO AMOUNT
RA-3 31450.32

Search for "RA-26":
REF_NO OLD_REF_NO AMOUNT
RA-10 499183.08
RA-26 RA-10 524564.04
RA-30 RA-26 24858.24
RA-37 RA-30 326.7

So basically when the user plugs in a ref_no, they should e able to see the history of any transactions with a relating reference no.

Any help would be much appreciated!

Thanks!!

mfemenel
Professor Frink

1421 Posts

Posted - 2009-10-27 : 13:52:23

a recursive CTE does it nicely

declare @refno varchar(10)
set @refno='RA-2'
;with result as (
select ref_no,old_ref_no,amount
from #temp_ref_history
where ref_no=@refno
UNION ALL

select t.ref_no,t.old_ref_no,t.amount
from #temp_ref_history t
inner join result r
on t.old_ref_no=r.ref_no
)
select * from result
Mike
"oh, that monkey is going to pay"

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-10-28 : 05:36:58

Hi Mike,

That works fine for search 1 and 2 in my examples, but when I try to run search 3 (Search for "RA-26")

I would also like to see the full transaction history, not just the transactions after "RA-26"

i,e, your query for "RA-26" would return:
ref_no old_ref_no amount
RA-26 RA-10 524564.04
RA-30 RA-26 24858.24
RA-37 RA-30 326.70

Where as I would like to see
REF_NO OLD_REF_NO AMOUNT
RA-10 499183.08
RA-26 RA-10 524564.04
RA-30 RA-26 24858.24
RA-37 RA-30 326.7

As you can see RA-10 would also form part of the results.

Please can you assist?

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2009-10-28 : 15:34:41

Interesting. I must be missing something here because it behaves as it should with the first two. how strange. Will look at this some more.

Mike
"oh, that monkey is going to pay"

mfemenel
Professor Frink

1421 Posts

Posted - 2009-10-28 : 16:22:16

Well I'm less than satisfied with the elegance of my query here but this version gets you the right answer. I'm not convinced it's the most elegant/best performing solution however but I've been in training classes all day and my brain is not quite focusing.

declare @refno varchar(10)
set @refno='RA-26'
;with result as (
select ref_no,old_ref_no,amount
from #temp_ref_history
where ref_no=@refno
UNION ALL

select t.ref_no,t.old_ref_no,t.amount
from #temp_ref_history t
inner join result r
on t.old_ref_no=r.ref_no
)
select * into #base from result

;with uppercheck as(
select ref_no,old_ref_no,amount
from #base
UNION ALL
select t.ref_no,t.old_ref_no,t.amount
from #temp_ref_history t
inner join uppercheck u
on u.old_ref_no=t.ref_no
)
select distinct * from UpperCheck

Mike
"oh, that monkey is going to pay"

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-10-31 : 06:21:38

Thank you for all your suggestions now. My requirment has no slightly changed. Basically I need to create a field called associated refs, with all associated ref_no's and old_ref_no's in that field delimited by a ";". Now I have written some script for this, which works. But when I need to run this on > 30,000 it has taken over an hour to work and I need it to run quicker.

Please let me know of any suggestions which may speed up the process, including any variations of scripting to do it, my code is long, but the end effect is the TEMP_REF_HISTORY (where I believe the field Associated_Refs) can be created using much simplier code...

Thanks!




--CREATE TEST_TABLE
IF OBJECT_ID('TEMP_REF_HISTORY') IS NOT NULL
	DROP TABLE dbo.TEMP_REF_HISTORY

CREATE TABLE TEMP_REF_HISTORY 
(
REF_NO varchar(10)
, OLD_REF_NO varchar(10)
, Amount decimal(18,2)
, Associated_Refs nvarchar(1000)
, Search_Refs nvarchar(1000)
, Associated_Count bigint not null default ''
)

INSERT INTO TEMP_REF_HISTORY VALUES('RA-01','','3963.96','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-02','','32974.92','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-03','','31450.32','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-04','','304.92','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-05','','2490.18','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-06','RA-02','3150.84','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-07','','25743.96','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-08','','493.68','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-09','','5713.62','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-10','','499183.08','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-11','','32982.18','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-12','','316122.18','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-13','','3296.04','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-14','','972.84','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-15','','3959988.78','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-16','RA-06','29.04','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-17','','329858.1','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-18','','486.42','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-19','','50.82','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-20','','43.56','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-21','','50.82','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-22','','551.76','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-23','','232.32','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-24','','3963.96','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-25','RA-16','36.3','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-26','RA-10','524564.04','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-27','','3288.78','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-28','','2889.48','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-29','','97516.32','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-30','RA-26','24858.24','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-31','','97516.32','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-32','','24858.24','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-33','RA-28','39356.46','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-34','','392.04','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-35','','29.04','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-36','','326.7','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-37','RA-30','326.7','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-38','','32982.18','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-39','','316122.18','','','')
INSERT INTO TEMP_REF_HISTORY VALUES('RA-40','RA-25','399.3','','','')


--Create as an SP
Declare @sql nvarchar(4000),
		@Counter int,
		@Max int

IF OBJECT_ID('TEMP_REF_HISTORY_ASSOCIATIONS') IS NOT NULL
	DROP TABLE dbo.TEMP_REF_HISTORY_ASSOCIATIONS

SELECT REF_NO
, SPACE(10) AS A_REF_NO_1
, SPACE(10) AS A_REF_NO_2
, SPACE(10) AS A_REF_NO_3
, SPACE(10) AS A_REF_NO_4
, SPACE(10) AS A_REF_NO_5
, SPACE(10) AS A_REF_NO_6
, SPACE(10) AS A_REF_NO_7
, SPACE(10) AS A_REF_NO_8
, SPACE(10) AS A_REF_NO_9
, SPACE(10) AS A_REF_NO_10
, SPACE(1000) AS ALL_A_REFS_COMBINED
, SPACE(1) AS ASSOCIATED_COUNT
INTO TEMP_REF_HISTORY_ASSOCIATIONS
FROM TEMP_REF_HISTORY
--WHERE LEN(OLD_REF_NO) < 1  --Originating Ref

--Update first A Ref Column
UPDATE a
SET a.A_REF_NO_1 = b.REF_NO
FROM TEMP_REF_HISTORY_ASSOCIATIONS a
INNER JOIN TEMP_REF_HISTORY b
ON a.REF_NO = b.OLD_REF_NO
WHERE LEN(B.OLD_REF_NO) > 0

SET @Counter = 2
SET @Max = 10 --Number of potential associated refs

--Set Combined Refs 
UPDATE TEMP_REF_HISTORY_ASSOCIATIONS
SET ALL_A_REFS_COMBINED = REF_NO


While @Counter <= @Max
	Begin
		Set @sql = 
		'UPDATE a
		SET a.A_REF_NO_' + cast(@Counter as nvarchar(10)) + ' = b.REF_NO
		FROM TEMP_REF_HISTORY_ASSOCIATIONS a
		INNER JOIN TEMP_REF_HISTORY b
		ON a.A_REF_NO_'+ cast(@Counter-1 as nvarchar(10)) +  ' = b.OLD_REF_NO
		WHERE LEN(B.OLD_REF_NO) > 0
		'
		EXEC (@sql)
		--Update Combined Refs
		Set @sql =
		'UPDATE TEMP_REF_HISTORY_ASSOCIATIONS
		SET ALL_A_REFS_COMBINED = ALL_A_REFS_COMBINED + '' ; '' + A_REF_NO_' + cast(@Counter-1 as nvarchar(10)) + ' 
		WHERE LEN(A_REF_NO_'+ cast(@Counter-1 as nvarchar(10)) + ') > 0'
		EXEC (@sql)
		Set @Counter = @Counter+1
	End

UPDATE TEMP_REF_HISTORY_ASSOCIATIONS
SET ALL_A_REFS_COMBINED = REF_NO
WHERE LEN(ALL_A_REFS_COMBINED) < 1

--Update original table with associated ref numbers
UPDATE a
	SET a.Associated_Refs = b.ALL_A_REFS_COMBINED
		, a.Search_Refs = b.ALL_A_REFS_COMBINED
FROM TEMP_REF_HISTORY a
INNER JOIN TEMP_REF_HISTORY_ASSOCIATIONS b
ON b.ALL_A_REFS_COMBINED LIKE '%'+a.REf_No+'%'

--Blank out Associated_Refs where tere is no associated ref
UPDATE TEMP_REF_HISTORY
SET Associated_Refs = ''
WHERE Associated_Refs  = REF_NO

--Set Associated_Count of refs
UPDATE TEMP_REF_HISTORY
SET Associated_Count = len(Associated_refs)-len(replace(Associated_Refs,';','')) --dbo.[ufn_CountChar](b.ALL_A_REFS_COMBINED,';')

SELECT * FROM TEMP_REF_HISTORY

So the main point being that the Associated_Refs field has all the refs relating to it.

i.e.
REF_NO OLD_REF_NO Amount Associated_Refs
RA-06 RA-02 3150.84 RA-02 ; RA-06 ; RA-16 ; RA-25 ; RA-40
RA-07 25743.96RA-07
RA-10 499183.08 RA-10 ; RA-26 ; RA-30 ; RA-37

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-10-31 : 10:43:15

Guys, I've just run this again (on a lot more fields and it just seems to stop working running...)
i.e:

After EXEC (@Sql)

I put in a msg row:
Print 'Update Deal Ref No: ' + cast(@Counter as nvarchar(10)) + ' Time: ' + Convert(CHAR(19), getdate(), 120)

and It loops through nicely to Row 96 of 109, but then just doesnt seem to be doing anything afterwards...

(2 row(s) affected)
Counter: 95 Time: 2009-10-31 14:40:00

(2 row(s) affected)
Counter: 96 Time: 2009-10-31 14:40:00

Any reason why this would happen??

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2009-11-01 : 03:05:23

Guys, it's ok I've worked out why, there was an old ref which was the same as another old ref causing the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources