find and mark difference in a table

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 2000 Forums

SQL Server Development (2000)

find and mark difference in a table

Author

Topic

solent
Starting Member

33 Posts

Posted - 2006-02-22 : 09:29:15

[code]
use tempdb
go
IF OBJECT_ID('tempdb.dbo.txns', 'U') IS NOT NULL DROP TABLE tempdb.dbo.txns
create table txns(
TXNID varchar(29),
DATE datetime)
CREATE NONCLUSTERED INDEX [IX_txns] ON [dbo].[txns]
( [TXNID] ASC,[DATE] ASC)

insert into txns values ('1', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('2', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('3', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('4', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('5', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('6', (CONVERT(DATETIME, '20-02-2006', 103)))

insert into txns values ('1', (CONVERT(DATETIME, '17-02-2006', 103)))
insert into txns values ('2', (CONVERT(DATETIME, '17-02-2006', 103)))
insert into txns values ('3', (CONVERT(DATETIME, '17-02-2006', 103)))
insert into txns values ('4', (CONVERT(DATETIME, '17-02-2006', 103)))
insert into txns values ('5', (CONVERT(DATETIME, '17-02-2006', 103)))
[/code]
i have the above table and i want to identfy on a new column which TXNID's are new. This is based on the DATE field. For example above on the 17-02 there where only 5 TXNIDs and on the 20th a 6th one came up. I need to identify this new field and mark it on a new column say 'NEW TXN ID'. How can i do this?

Norwich
Posting Yak Master

158 Posts

Posted - 2006-02-23 : 04:57:57

quote:

Originally posted by solent


use tempdb
go
IF OBJECT_ID('tempdb.dbo.txns', 'U') IS NOT NULL DROP TABLE tempdb.dbo.txns
create table txns(
TXNID varchar(29),
DATE datetime)
CREATE NONCLUSTERED INDEX [IX_txns] ON [dbo].[txns] 
(	[TXNID] ASC,[DATE] ASC)

insert into txns values ('1', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('2', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('3', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('4', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('5', (CONVERT(DATETIME, '20-02-2006', 103)))
insert into txns values ('6', (CONVERT(DATETIME, '20-02-2006', 103)))

insert into txns values ('1', (CONVERT(DATETIME, '17-02-2006', 103)))
insert into txns values ('2', (CONVERT(DATETIME, '17-02-2006', 103)))
insert into txns values ('3', (CONVERT(DATETIME, '17-02-2006', 103)))
insert into txns values ('4', (CONVERT(DATETIME, '17-02-2006', 103)))
insert into txns values ('5', (CONVERT(DATETIME, '17-02-2006', 103)))

i have the above table and i want to identfy on a new column which TXNID's are new. This is based on the DATE field. For example above on the 17-02 there where only 5 TXNIDs and on the 20th a 6th one came up. I need to identify this new field and mark it on a new column say 'NEW TXN ID'. How can i do this?

Maybe I'm missing something, in your INSERT you are inserting 6 items on the 20th but in your question you are saying there's a new 1 on the 20th.

Please explain a bit more and give us your expected result set

Regards
N

The revolution won't be televised!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-23 : 05:40:52

So, basically you are after something like this?


use tempdb
go
IF OBJECT_ID('tempdb.dbo.txns', 'U') IS NOT NULL DROP TABLE tempdb.dbo.txns
create table txns(
TXNID varchar(29),
[DATE] datetime,
IsNew bit)
CREATE NONCLUSTERED INDEX [IX_txns] ON [dbo].[txns] 
(	[TXNID] ASC,[DATE] ASC)

insert into txns values ('1', (CONVERT(DATETIME, '20-02-2006', 103)),0)
insert into txns values ('2', (CONVERT(DATETIME, '20-02-2006', 103)),0)
insert into txns values ('3', (CONVERT(DATETIME, '20-02-2006', 103)),0)
insert into txns values ('4', (CONVERT(DATETIME, '20-02-2006', 103)),0)
insert into txns values ('5', (CONVERT(DATETIME, '20-02-2006', 103)),0)
insert into txns values ('6', (CONVERT(DATETIME, '20-02-2006', 103)),0)

insert into txns values ('1', (CONVERT(DATETIME, '17-02-2006', 103)),0)
insert into txns values ('2', (CONVERT(DATETIME, '17-02-2006', 103)),0)
insert into txns values ('3', (CONVERT(DATETIME, '17-02-2006', 103)),0)
insert into txns values ('4', (CONVERT(DATETIME, '17-02-2006', 103)),0)
insert into txns values ('5', (CONVERT(DATETIME, '17-02-2006', 103)),0)

declare @lastDate datetime

select @lastDate = max([Date]) from txns

update txns
set IsNew = 1
from txns where txnid not in (select txnid from txns where [date] < @lastDate)

select txnid,[date],isnew from txns

You will also need a process to set them back to 0 when they are no longer new records, a simple update based on the date maybe..


update txns set IsNew = 0 where [date] < getdate()

Or something similar..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources