Query to get max term date - 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 2000 Forums

SQL Server Development (2000)

Query to get max term date

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-11-27 : 14:53:55

I need to get the max term date from a series of records. The term date may also be null if the record is still active.
How do I do this? Here's what I have so far, but if there is a null and a term date, I want to return a null and I'm getting the term date.

select prov_tag, a.prov_num, min(deal_eff) as deal_eff,
max(deal_term) as deal_term
from ptags a
left join passn b on a.prov_num = b.prov_num
left join pdeals c on b.deal_num = c.deal_num
where prov_tag LIKE 'UWHE%'
and net_work in(173, 175,2)
group by prov_tag, a.prov_num

Thanks,
Marcie

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 15:07:32

assuming you want deal_eff is not a concern, this maybe what you are looking for...

--get inactive ones
select prov_tag, a.prov_num, min(deal_eff) as deal_eff,
max(deal_term) as deal_term
from ptags a
left join (select distinct prov_num from ptags where deal_term is null) as still_active on still_active.prov_num = a.prov_num
left join passn b on a.prov_num = b.prov_num
left join pdeals c on b.deal_num = c.deal_num
where prov_tag LIKE 'UWHE%'
and net_work in(173, 175,2)
and still_active.prov_num is null
group by prov_tag, a.prov_num
--then the inactive ones
union all
select prov_tag, a.prov_num, min(deal_eff) as deal_eff,
null as deal_term
from ptags a
join (select distinct prov_num from ptags where deal_term is null) as still_active on still_active.prov_num = a.prov_num
left join passn b on a.prov_num = b.prov_num
left join pdeals c on b.deal_num = c.deal_num
where prov_tag LIKE 'UWHE%'
and net_work in(173, 175,2)
group by prov_tag, a.prov_num

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-11-27 : 15:20:47

Thanks,that looks like it will work. Is a union the only way to do this? I tried to use a case statement, but couldn't get the syntax right, so didn't think that was an option.

anonymous1
Posting Yak Master

185 Posts

Posted - 2007-11-27 : 15:58:30

if it is not high concurrency you can try this...

declare @now datetime
set @now = getdate()

select prov_tag, a.prov_num, min(deal_eff) as deal_eff,
nullif(max(isnull(deal_term, @now)), @now) as deal_term
from ptags a
left join passn b on a.prov_num = b.prov_num
left join pdeals c on b.deal_num = c.deal_num
where prov_tag LIKE 'UWHE%'
and net_work in(173, 175,2)
group by prov_tag, a.prov_num

memarcie
Yak Posting Veteran

91 Posts

Posted - 2007-11-27 : 18:58:44

Here's what I think I'm doing. I need to write an update process after I determine data. I will set the date to a totally obvious number then set deal_term = null where deal_term = '2222-12-12'.

I'll use this to get the data:

select prov_tag, a.prov_num, min(deal_eff) as deal_eff,
max(
case
when deal_term is null
then '2222-12-12'
else
deal_term end ) as deal_term
from ptags a
left join passn b on a.prov_num = b.prov_num
left join pdeals c on b.deal_num = c.deal_num
where prov_tag LIKE 'UWHE%'
and net_work in(173, 175,2)
group by prov_tag, a.prov_num

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources