T-SQL Question - 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)

T-SQL Question

Author

Topic

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-01-08 : 09:11:07

Hello,

My apologies if my inquiry should instead be posted in the New To SQL Server forum. This query has given me much trouble, to the point I'm wondering if its possible at all;

Table 1 includes records detailing certain functions (modify, delete, create, activity code, activity time, etc).

I am trying to parse out the records which contain 2 specific activity codes (delete & modify), where the 2nd activity code has a date stamp AFTER the original activity. ie - files deleted, but subsquently modified.

Thus far, I have extracted all deleted records to a temp table, and created the following query;

select * from dbo.historytable as a
join dbo.tempdeletedrecordstable as b on a.sysid = b.sysid
where b.activity_code = 'XX'
and b.activity_datestamp > a.activity_datestamp

This query returns zero results. I know there are records which fit the scenario described above.

Can anyone please point out where I'm going wrong with this??

Many thanks to all who reply.
D.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 09:22:58

Is sysid the pk for records? Also what's the data type of activity_datestamp?
If you can provide the table structures with some sample data that would be great

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-01-08 : 09:24:59

Yes, sysid is the pk in both tables.

The data type for actitivy_datestamp is datetime.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 09:54:58

Cant spot anything unusual. Can you provide some sample data for your tables and expected o/p?

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-01-08 : 10:39:27

Sure.

The following two rows are taken from the dbo.historytable;

SYSID          ACTIVITY         ACTIVITY          ACTIVITY 
                                CODE              DATESTAMP
507335         Modify           21                2007-05-14 
506587         Modify           21                2007-05-14

What I'm hoping this will produce is a list of all sysid's from dbo.historytable which also have a corresponding record in dbo.tempdeleteddrecordstable (and have therefore been previously deleted).

The exact output would look just like the records above, as my original script selects all columns.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 11:16:18

quote:
Originally posted by munkdogg

Sure.

The following two rows are taken from the dbo.historytable;

SYSID ACTIVITY ACTIVITY ACTIVITY
CODE DATESTAMP
507335 Modify 21 2007-05-14
506587 Modify 21 2007-05-14

What I'm hoping this will produce is a list of all sysid's from dbo.historytable which also have a corresponding record in dbo.tempdeleteddrecordstable (and have therefore been previously deleted).

The exact output would look just like the records above, as my original script selects all columns.

this means the date in tempdeletedrecordstable will be less than that in history. so shouldnt condition be

select * from dbo.historytable as a
join dbo.tempdeletedrecordstable as b on a.sysid = b.sysid
where b.activity_code = 'XX'
and b.activity_datestamp < a.activity_datestamp

Also not sure why you are checking code from deleted table. Shouldnt that be from history table as well?

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-01-08 : 11:58:08

I am looking for records where the delete activity took place prior to the modify activity. Yes, this sounds backwards - but some records have not been properly deleted, and as such are still being modified.

The deleted table lists all records with a delete action. I was trying to use this sort of like a FK to join with the history table.

An alternative approach might be to scan the history table for all sysid's where a delete entry PLUS subsequent modify entry exist. This type of script is beyond my ability :-)

Thanks so much for all your input thus far.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 12:08:28

quote:
Originally posted by munkdogg

I am looking for records where the delete activity took place prior to the modify activity. Yes, this sounds backwards - but some records have not been properly deleted, and as such are still being modified.

The deleted table lists all records with a delete action. I was trying to use this sort of like a FK to join with the history table.

An alternative approach might be to scan the history table for all sysid's where a delete entry PLUS subsequent modify entry exist. This type of script is beyond my ability :-)

Thanks so much for all your input thus far.

I think this can be checked as follows. Try:-

;
With Hist_CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY SYSID ORDER BY ACTIVITYDATESTAMP) AS 'RowNo',
SYSID,
ACTIVITY,
ACTIVITYCODE,
ACTIVITYDATESTAMP
FROM dbo.historytable 
)


SELECT *
FROM Hist_CTE h1
INNER JOIN Hist_CTE h2
ON h2.SYSID=h1.SYSID
AND h2.RowNo = h1.RowNo+1
AND h2.ACTIVITY='Modify'--Modify following delete
AND h1.ACTIVITY='Delete'

munkdogg
Yak Posting Veteran

53 Posts

Posted - 2008-01-08 : 12:21:32

Wow - that worked. Your kung-fu is very strong!

Thanks very much visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 12:30:40

Cheers munkdogg. Please feel free to come back whenever you face difficulty.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources