I have a table which could be created with this DDL

CREATE TABLE [tran_detail] (
[ticket_number] [int],
[open_number] [int],
[tran_date] [datetime] ,
[product_name] [nvarchar] (255)
)


I need to compare product_name values between consective dates (the
tran_date value) but I'm stuck...

any help would be greatly appreciated. I know that I must join the
table to itself (which would be done on the ticket_number and
open_number) but I can't quite get it.

Thanks in advance.
Danielle

I don=B4t know if anyone can guess what you want to do (I can=B4t) , it
was a very short explanation.
Jens Suessmeyer.

Jens -

Sorry about that... let me try again. This table is updated daily at
12:01 AM ...

CREATE TABLE [tran_detail] (
[ticket_number] [int],
[open_number] [int],
[tran_date] [datetime] ,
[product_name] [nvarchar] (255)
)

insert into tran_detail
values (1, 1, '12/24/2005','Product 1')

insert into tran_detail
values (1, 1, '12/25/2005','Product 1')

insert into tran_detail
values (1, 1, '12/26/2005','Product 2')

Notice how the 4th column, product_name changed - from Product 1 to
Product 2... I need to write a query that can detect that Product is
different....

Does this help clarify?

Danielle

Hi Danielle - try to join also on

... tab1.tran_date = dateadd(tab2.tran_date,1 day)
and tab1.product_name <> tab2.product_name.

(tab1 and tab2 of course the same table, and the "other" join criteria
ticket_number, opern_number have to be added, too.)

Be aware of "outer join" conditions - use a left outer or full outer if
you need these , too.
Might also have to be reconsidered if dates are not completely
consecutive "every day" (gaps in the dates) - this would need a
different approach.
Pls. lookup syntax for dateadd or datediff in BOL ...have nothing here
just now.

Hope this helps....

mattp -

Thanks very much - I am still working thru the rest of my code, but
your suggestion has given me the format I need - Thanks!

Danielle