help with a difficult TSQL query

[TonyKhela]

Hi!

This is (for my opinion) a difficult question:

I got one table "Clients"
ClientID, Name.
And another Table "ClientStates",
StateID, ClientID, Date, enabled.
One client can have multiple states, or none.

For example,
if Client 1, Miss X, has the following states:
1 | 1 | 9.1.2002 | yes
2 | 1 | 6.1.2002 | no
3 | 1 | 5.1.2002 | yes
4 | 1 | 1.1.2002 | no
then this table means that she is not to be served
from the first to the fourth of January, and from
the 6th to the 8th.


I need a query that lists all clients enabled at
a specific date, e.g. the 7th of January. Miss X
would then not be listed in this query's result.
If there is a client with no state (with Date
before the given date), he/she is assumed
"enabled".

How do I do that?


Thanks a lot,

 

[MeanGreen]

This should get you what you want:

create table #test (stateid integer, clientid integer, statedate datetime,enabled varchar(3))

insert #test values(1,1,'1/25/2003','yes')
insert #test values(2,1,'1/18/2003','no')
insert #test values(3,1,'1/9/2003','yes')
insert #test values(4,1,'1/6/2003','no')
insert #test values(5,1,'1/5/2003','yes')
insert #test values(6,1,'1/1/2003','no')

insert #test values(1,2,'1/25/2003','no')
insert #test values(2,2,'1/18/2003','yes')
insert #test values(3,2,'1/5/2003','no')



declare @lookupdate datetime
set @lookupdate = '1/5/2003'

select * from (
select a1.clientid,a1.statedate,min(isnull(dateadd(day,-1,a2.statedate),getdate())) StopDate
from #test a1
left outer join (select stateid,clientid,statedate from #test where enabled = 'no')
a2
on
a1.clientid = a2.clientid
and a2.statedate > a1.statedate
where enabled = 'yes'
group by a1.clientid,a1.statedate
) mytable
where @lookupdate between statedate and Stopdate

Hope this helps.

 

[Zathras]

Here is a slightly simpler version that should return similar results, plus including clients with no entries in ClientStates:

create table #Clients (ClientID int, Name varchar(20))
create table #ClientStates (StateID int, ClientID int,
      Date Datetime, Enabled varchar(3))

insert into #Clients values(1,'Miss X')
insert into #Clients values(2,'Miss Y')
insert into #Clients values(3,'Miss Z')
insert into #ClientStates values(1,1,'1/9/2002','yes')
insert into #ClientStates values(2,1,'1/6/2002','no')
insert into #ClientStates values(3,1,'1/5/2002','yes')
insert into #ClientStates values(4,1,'1/1/2002','no')

Declare @RefDate as Datetime
Select @RefDate = '1/7/2002'

select C.* from #Clients C
  Left Outer Join
     (select ClientID, Max(Date) as LastRefDate
      from #ClientStates where Date <= @RefDate
      group by ClientID) T on T.ClientID=C.ClientID
  Left Outer Join #ClientStates CS on C.ClientID=CS.ClientID
 where IsNull(T.LastRefDate,@RefDate)=IsNull(CS.Date,@RefDate)
   and IsNull(CS.Enabled,'yes')='yes'

drop table #ClientStates
drop table #Clients

 

[TonyKhela]

Thanks to both of you. The scripts really helped.