Query Help

Hi all,
I have one ‘tall’ table that records the following on a regular basis:
STATE SERIAL# DATE
====== ======== ========
VA Z32WE12 12/31/2003
CA QWEFD1 05/04/2005
VA Z32WE13 01/01/2003
CA QWEFD2 05/05/2005
TX POISD21 05/03/2005
TX POISD21 05/04/2005
TX POISD21 05/05/2005
We are tracking the serial number for each state and would like to report on
the current and previous serial number for each state. Can someone please
help me with building the query in order to get the following:
State Current Serial# Since Previous Serial#
CA QWEFD2 05/05/2005 QWEFD1
VA Z32WE13 01/01/2003 Z32WE12
TX POISD21 05/03/2005 Never Changed
Thanks in advance,
-AppreciatorIt seems to be a flaw in the data:

> VA Z32WE12 12/31/2003
> VA Z32WE13 01/01/2003
Did you mean "12/31/2002" for serial# "Z32WE12"?
try:
use northwind
go
create table t1 (
state char(2) not null,
serial# varchar(10) not null,
[date] datetime,
)
go
insert into t1 values('VA', 'Z32WE12', '12/31/2002')
insert into t1 values('CA', 'QWEFD1' , '05/04/2005')
insert into t1 values('VA', 'Z32WE13', '01/01/2003')
insert into t1 values('CA', 'QWEFD2' , '05/05/2005')
insert into t1 values('TX', 'POISD21', '05/03/2005')
insert into t1 values('TX', 'POISD21', '05/04/2005')
insert into t1 values('TX', 'POISD21', '05/05/2005')
go
create view v1
as
select state, serial#, max([date]) as [date] from t1 group by state,
serial#
go
create view v2
as
select
a.state,
a.serial# as current_serial#,
a.[date] as since,
isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
previous_serail#
from
v1 as a
left join
v1 as b
on a.state = b.state and a.[date] = (select min(c.[date]) from v1 as
c
where c.state = a.state and c.[date] > b.[date])
go
select
*
from
v2 as a
where
previous_serail# != 'have_not_changed_since'
or (previous_serail# = 'have_not_changed_since' and not exists(select *
from v2 as b where b.state = a.state and b.previous_serail# !=
'have_not_changed_since'))
order by
case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
a.state
go
drop view v2, v1
go
drop table t1
go
AMB
"URG" wrote:

> Hi all,
> I have one ‘tall’ table that records the following on a regular basis:
> STATE SERIAL# DATE
> ====== ======== ========
> VA Z32WE12 12/31/2003
> CA QWEFD1 05/04/2005
> VA Z32WE13 01/01/2003
> CA QWEFD2 05/05/2005
> TX POISD21 05/03/2005
> TX POISD21 05/04/2005
> TX POISD21 05/05/2005
> We are tracking the serial number for each state and would like to report
on
> the current and previous serial number for each state. Can someone please
> help me with building the query in order to get the following:
> State Current Serial# Since Previous Serial#
> CA QWEFD2 05/05/2005 QWEFD1
> VA Z32WE13 01/01/2003 Z32WE12
> TX POISD21 05/03/2005 Never Changed
> Thanks in advance,
> -Appreciator
>|||Hi Alejandro,
Thanks a bunch! That really works perfect..!!
Sorry about the flaw - I had typed in the sample data.
URG
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> It seems to be a flaw in the data:
>
> Did you mean "12/31/2002" for serial# "Z32WE12"?
> try:
> use northwind
> go
> create table t1 (
> state char(2) not null,
> serial# varchar(10) not null,
> [date] datetime,
> )
> go
> insert into t1 values('VA', 'Z32WE12', '12/31/2002')
> insert into t1 values('CA', 'QWEFD1' , '05/04/2005')
> insert into t1 values('VA', 'Z32WE13', '01/01/2003')
> insert into t1 values('CA', 'QWEFD2' , '05/05/2005')
> insert into t1 values('TX', 'POISD21', '05/03/2005')
> insert into t1 values('TX', 'POISD21', '05/04/2005')
> insert into t1 values('TX', 'POISD21', '05/05/2005')
> go
> create view v1
> as
> select state, serial#, max([date]) as [date] from t1 group by stat
e, serial#
> go
> create view v2
> as
> select
> a.state,
> a.serial# as current_serial#,
> a.[date] as since,
> isnull(cast(b.serial# as varchar(25)), 'have_not_changed_since') as
> previous_serail#
> from
> v1 as a
> left join
> v1 as b
> on a.state = b.state and a.[date] = (select min(c.[date]) from v1
as c
> where c.state = a.state and c.[date] > b.[date])
> go
> select
> *
> from
> v2 as a
> where
> previous_serail# != 'have_not_changed_since'
> or (previous_serail# = 'have_not_changed_since' and not exists(select *
> from v2 as b where b.state = a.state and b.previous_serail# !=
> 'have_not_changed_since'))
> order by
> case when previous_serail# = 'have_not_changed_since' then 1 else 0 end,
> a.state
> go
> drop view v2, v1
> go
> drop table t1
> go
>
> AMB
> "URG" wrote:
>