help constructing a select statement

using sqlserverce...

 

i need little constructing select statement - have product

summary table contains quarters , weeks. data can come in

asynchronously. need able select (for example) quarter = 2 ,

week = 3 or week = 2, depending on whether week 3 available yet.

 

something "select * products quarter = 2 , week = 3 (or

week = 2 if no week 3)----

 

mike p

 

 

 

hi captain,

 

if understand correctly, want data last week of quarter. if so, can use  inner join follow:

 

create table products(id int,name nvarchar(10),quarter int,week int)

go

insert into products values(1,'a',1,1)

go

insert into products values(2,'b',1,2)

go

insert into products values(3,'c',2,1)

go

insert into products values(4,'d',2,2)

go

insert into products values(5,'e',2,3)

go

 

select a.* from products a

inner join (select quarter as qa,max(week) as qb from products group by quarter) b

on a.quarter=b.qa

where a.quarter=2 and a.week=b.qb

 

if have more questions, please feel free let me know.

 

thanks,

yoyo yu

SQL Server  >  SQL Server Compact