Need Query for Groups and Islands Problem
-
Hi There,
I'm having a table which have the data about the service activation and expiry day of customers .
Customer can activate an service at any time .
I need to find the in active days of every customers between certain period
create table #sample ( Name varchar(100),ServiceName varchar(100), Activationdate datetime,Expirydate datetime )
insert into #sample Values ('HeavenGuy','A','2014-02-01 00:00:00', '2014-02-10 23:59:59')
insert into #sample Values ('HeavenGuy','A','2014-02-09 00:00:00', '2014-02-21 23:59:59')
insert into #sample Values ('HeavenGuy','A','2014-02-24 00:00:00', '2014-02-28 23:59:59')
insert into #sample Values ('HeavenGuy','A','2014-03-05 00:00:00', '2014-03-22 23:59:59')
From the above sample I need attached output
Inactivity should fall between 2014-02-01 to 2014-03-30
-
Hello all,
I myself prepared query for the above scenario...
But have some deviation...
I could not able to retrieve last row in the given output
;with cte as (
select ROW_NUMBER() over(order by activationdate) rno,* from #sample
),
cte2 as (
select s1.*, datediff(dd,s1.Expirydate,s2.Activationdate)-1 addup from cte s1 join cte s2
on s1.Name = s2.Name
and s1.rno = s2.rno - 1 )
select Name,DATEADD(dd,1,expirydate),DATEADD(dd,addup,expirydate) from cte2 where addup > 0
Suggest me if any good approach to handle this & solve the rest of the part...
-
I was able to get the desired output by adding a row to the sample data when the end date is outside the range in the sample data, like this:
;with
sample as (
select * from #sample
union
select * from(
select Name, ServiceName, '2014-03-31' IntervalEnd, '2014-02-01' IntervalStart
from #sample
group by Name, Servicename
having '2014-03-31' > MAX(Expirydate)) q
),
cte as (
select ROW_NUMBER() over(order by activationdate) rno,* from sample
),
cte2 as (
select s1.*, datediff(dd,s1.Expirydate,s2.Activationdate)-1 addup from cte s1 join cte s2
on s1.Name = s2.Name
and s1.rno = s2.rno - 1 )
select Name,DATEADD(dd,1,expirydate),DATEADD(dd,addup,expirydate) from cte2 where addup > 0
If this works for you, you might need to add a similar row at the beginning of the interval.
Gerald Britton, Pluralsight courses
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply