how to find the initial update time

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

General SQL Server Forums

New to SQL Server Programming

how to find the initial update time

Author

Topic

zhangn
Starting Member

29 Posts

Posted - 2009-09-29 : 05:28:53

Hi all,

I am trying to query some data with original issue time stamp.

I have two tables,
header table
-Whenever you updates the data in the header table, the columns update time will be updated as well
relationship table
-The data may come from different source. Therefore, the user is able to track where is the data come from in this table. For example, by selecting targetheaderid, you will find the source headerid.

I want to find the initial updated time, which is associated with the initial headerid.

any suggestion will be appreciated!

Ning

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 05:52:52

do you mean this?


SELECT h.*
FROM header h
INNER JOIN relationship r
ON r.targetheaderid=h.headerid

if not illustrate your scenario with some sample data

zhangn
Starting Member

29 Posts

Posted - 2009-09-29 : 06:09:21

thanks visakh16,

I am not sure whether the inner join will work in this case.

If the data have been uploaded to the database once, it will be record in the header table. You won't find anything in the relationship table.

However, if the data has been modified. The new headerid in the header table will be generated. And you will be able to see the intial header id will be stored as SourceHeaderId and the new headerid in the header table will be stored as TargetHeaderId in the relationship table.

If the data has been modifiy a few times, i think it will require some loop to find the original headerid.

That's the part I got confuse!

So the result i want to return from the query is

a) HeaderId in the header table, if data has not been modified
+
b) Initial SourceHeaderId in the relationship table, if the data has been modified

=> Search for the data update time from the above two headerid groups.

If there is any further suggestion, i will be very much appreciated!

Ning

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:19:12

are you using sql 2005? also which table has data update time? what represents data column?

zhangn
Starting Member

29 Posts

Posted - 2009-09-29 : 08:10:59

Yes I am using SQL 2005.

Header Table
HeaderId [PK]
UpdateTime

Relationship
SourceHeaderId [FK]
TargetHeaderId [FK]

Both sourceheaderid and targetheaderid are the foreign key for the
HeaderId in the Header Table.

What I am trying to do is trying to find the original updatetime.

For example

11(SourceHeaderId) <-12(TargetHeaderId)
12(SourceHeaderId) <-13(TargetHeaderId)
13(SourceHeaderId) <-14(TargetHeaderId)

11 is the headerId I am looking forward by giving 14. Then I can find the corrected updatedtime with headerid 11.

Many thanks for your help!

Ning

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 08:55:01

use a recursive CTE


;With CTE(Source,Destination) AS
(
SELECT SourceHeaderId ,
TargetHeaderId ,
0 AS Level
FROM Relationship
WHERE TargetHeaderId =@ID
UNION ALL
SELECT r.SourceHeaderId ,
r.TargetHeaderId ,
c.Level + 1
FROM Relationship r
INNER JOIN CTE c
ON c.SourceHeaderId=r.TargetHeaderId
)

SELECT h.*
FROM header h
INNER JOIN(
SELECT TOP 1 SourceHeaderId
FROM CTE
ORDER BY Level DESC
)t
ON h.headerid=t.sourceheaderid

zhangn
Starting Member

29 Posts

Posted - 2009-09-29 : 10:34:06

Thanks so much! it works.

By giving the targetheaderid, i am able to find the initial sourceheaderid with your query. I have finished the first part of the query with your help!

Thanks so much again.

My second part of query requires extract the list of unique headerid from the header table.

Unique means (case a+ case b)
a) Data has not been modified
-Return the headerId

b) Data has been modified
-Return the initial SourceHeaderId. (As your previous query have done!)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:22:04

make the previous query as a function and use it like


SELECT h.headerid
FROM header h
LEFT JOIN relationship r
ON r.sourceheaderid=h.headerid
where r.sourceheaderid IS NULL
UNION ALL
SELECT dbo.Function(h.headerid)
FROM header h
WHERE EXISTS (SELECT 1 FROM relationship WHERE SourceHeaderID=h.headerid)

zhangn
Starting Member

29 Posts

Posted - 2009-10-01 : 05:04:21

thanks! it works.

I have found another way to do it as well.

SELECT h.headerid
FROM header h
where h.headerid not in
(SELECT h.headerid
FROM header h
inner join relationship r
on h.headerid = r.targetheaderid)

ON r.sourceheaderid=h.headerid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 05:08:14

great.. cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources