Help with the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with the query

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-05-22 : 14:44:36

Hi All,

I have two tables
Table1
(
Caseid int,
SrartDate datetime,
EndDate datetime,
EpisodeNumber int
)
insert into Table1
select 1, '01/01/2007', '02/12/2007', 1 union all
select 1, '03/01/2007', '03/30/2007', 2 union all
select 2, '03/11/2007', '04/15/2007', 1 union all
select 3, '03/12/2007', '03/22/2007', 1 union all
select 3, '04/20/2007', '04/29/2007', 2 union all
select 4, '05/02/2007', '05/15/2007', 1

Table2
(
Caseid int,
ScanDate datetime
)
insert into Table2
select 1, '03/29/2007' union all
select 2, '03/08/2007' union all
select 3, '04/01/2007' union all
select 4, '05/20/2007'

These tables can be joined by CaseID.

I need to select CaseID and EpisodeNumber from Table1
where Table2.ScanDate falls between Table1.StartDate and Table1.EndDate, which is easy. My problem is when ScanDate doesn't fall between Start and EndDate.
I need the following result from the query:
CaseID EpisodeNumber
1----------2
2----------1
3----------1
4----------1

Thanks for your help

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-22 : 14:56:01

Something like this?


Select t2.caseid, t1.episodenumber
from Table2 t2
join Table1 t1 on t1.Caseid = t2.Caseid and NOT (t2.ScanDate between t1.SrartDate and t1.EndDate )

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-05-22 : 15:10:51

Dinakar,
Thanks for the reply, but this doesn't solve my problem.
Your query will not return the result I needed:
CaseID EpisodeNumber
1----------2
2----------1
3----------1
4----------1

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-22 : 15:29:19

The query you asked for would look like

select a.Caseid,a.EpisodeNumber
from @Table1 a
inner join @Table2 b on a.CaseID=b.CaseID
where b.ScanDate not between a.SrartDate and a.EndDate

and return

1 1
2 1
3 1
3 2
4 1

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-05-22 : 15:35:11

This query does not return the result I need.
Resultset should include both cases: when scan date falls between start and end dates
and when it doesn't.
Here is what I need to be returned:
1 2
2 1
3 1
4 1

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-22 : 15:40:06

quote:
Originally posted by PurpleSun

This query does not return the result I need.
Resultset should include both cases: when scan date falls between start and end dates
and when it doesn't.
Here is what I need to be returned:
1 2
2 1
3 1
4 1

You just said you want to return the results where scan date falls between start and end date, and when it doesn't?????

Wouldn't every record qualify then?

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-05-22 : 15:49:15

Sorry, I didn't clear it out. When scan date falls between start and end dates it should return particular EpisodeNumber for the case. The rest of records with the same CaseID should not be included in the result set. (As it is with CaseID=1)
If scan date doesn't fall in between then it should return the nearest previous EpisodeNumber(like with CaseID=3 and CaseID=4).
If previous EpisodeNumber does not exist the it should return the next EpisodeNumber (like with CaseID=2)

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-23 : 01:36:41

hope episode numbers are in sequence...try this(try some other query whose cost is less when compared to this query)

Select a.caseid, EpisodeNumber = case when EpisodeNumber is null then
(select case when count(*) > 1 Then max(EpisodeNumber) -1 else max(EpisodeNumber) end
from @t1 where caseid = a.caseid group by caseid)
else EpisodeNumber end
from @t2 a left outer join @t1 b
on a.caseid = b.caseid and a.scandate between b.SrartDate and b.EndDate

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-05-23 : 09:23:34

Thanks a lot, it solves my problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources