SQL Help please

[Ruprect]

OK Smarty pantses

how about this:

table "customer_snapshot"

fields customer_surname, customer_dob, snapshot_date, customer_email(other fields...)

for the purposes of this exercise customer_surname, customer_dob and snapshot_date are a composite PK.

What I want to know is:
1) which customers exist today that didn't exist yesterday and
2) which customers existed yesterday that don't exist today and
3) which customers have changed their email addresses since yesterday.

DB: Sybase, Number of records in table - about 2.5 million.

 

[Spacecadet]

use derived tables

Can't be arsed writing out the full SQL, you can do that yourself.
Heres a starter for 10 though

select <stuff>
from (select * from customer_snapshot where date = <today>) as Today
full outer join
(select * from customer_snapshot where date = <yesterday>) as Yesterday
on today.<primary key fields> = yesterday.<primary key fields>
where
<add your where clauses here>

 

[Ruprect]

Thanks I'll give it a go

 

[Ruprect]

One last qn - without a full outer join available to me in sybase is there any way to do this in one query or do I just have to do 2 queries?

ta.

 

[Spacecadet]

(select distinct <primary key fields> from customer_snapshot where date in (<today>, <tomorrow>) as clist
left outer join
(select * from customer_snapshot where date = <today>) as today
on clist.<pkey fields> = today.<pkey fields>
left outer join
(select * from customer_snapshot where date = <yesterday>) as yesterday
on clist.<pkey fields> = yesterday.<pkey fields>

it will hamper performance though.. there's probably a better way but with only 1 table to look at its difficult to tell