Min/ Max date 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

General SQL Server Forums

New to SQL Server Programming

Min/ Max date query

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-04-16 : 12:02:18

Hi All,

I have to get all the customers who called in the first time to cancel during the 30 day trial . Below is my query for this part.

Part1
select order_id,order_received_date,call_date,cancel_date,call_reason_id
into #temp_first_call
from #temp_orders t with (nolock)
where Call_Reason_ID =5---cancelled calls
and call_date=(select min(call_date) from #temp_orders with (nolock)
where t.order_id=order_id)
and datediff(dd,order_received_date,call_date)<30

Then I have to get all the customers who called in to cancel the first time but were retained
Below is my query for that part

Part2
select order_id,order_received_date,call_date,cancel_date,call_reason_id
into #temp_saved
from #temp_orders t with (nolock)
where Call_Reason_ID =7 ----Saved calls
and call_date=(select min(call_date) from #temp_orders with (nolock)
where t.order_id=order_id)
and datediff(dd,order_received_date,call_date)<30

Now I have to get all the customers who were saved in part2 but called in again the second time and cancelled.
Not sure how to go about it. Using SQL Server 2005.

Thanks,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-16 : 13:46:12

[code]
select t.*
from #temp_saved s
cross apply (select order_id,order_received_date,call_date,cancel_date,call_reason_id
from #temp_orders o1
inner join (select order_id,min(call_date) as first
from #temp_orders
where o1.Call_Reason_ID =5
and o1.order_id=s.order_id
and o1.call_date > s.call_date
group by order_id) o2
on o2.order_id=o1.order_id
and o2.first=o1.call_date
where o1.Call_Reason_ID =5
and o1.order_id=s.order_id
and o1.call_date > s.call_date
and datediff(dd,o1.order_received_date,o1.call_date)<30) t
[/code]

Petronas
Posting Yak Master

134 Posts

Posted - 2009-04-21 : 11:29:09

Visakh16,

Thanks for your response. Sorry for the delay.. I was out sick.
I got it to work..

Thanks again and as usual appreciate your help,
Petronas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources