March 24, 2006 at 3:36 am
Hi,
I have a table with fields: Client_Ref, Year and Month. I need to create a select statement that will find all records for a particular year/month as long as the client_ref does not exists for the previous six months.
e.g. if client_ref AA1 is in year 2006 month 01 but is also in 2005 12 then disregard
but, if client_ref AA2 is in year 2006 month 01 but not in 2005 07-12 then select
This is out of my depths as only just learing! any help would be greatly appreciated.
thanks
Steve
March 24, 2006 at 7:30 am
If your table looks similar to this example, then I think this may work..
create table #x (client_ref char(3) not null, year char(4) not null, month char(2) not null )
go
insert #x
select 'AA1', '2005', '12' union all
select 'AA1', '2006', '01' union all
select 'AA2', '2005', '01' union all
select 'AA2', '2006', '01'
go
declare @year char(4), @month char(2)
select @year = '2006', @month = '01'
select *
from #x x
where year = @year
and month = @month
and not exists
( select * from #x y
where x.client_ref = y.client_ref
and datediff(month, y.year + y.month + '01', @year + @month + '01') between 1 and 5
)
go
drop table #x
go
client_ref year month
---------- ---- -----
AA2 2006 01
(1 row(s) affected)
The idea is to use your year + month + '01' columns to build a complete date, then use this in a correlated subquery to check for the existance of any client_ref that has a year + month entry that is between 1 to 5 months back from the year + month you supply as 'starting point'.
=;o)
/Kenneth
March 24, 2006 at 7:44 am
Excellent, thank you!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply