October 17, 2017 at 10:04 am
Hello everyone,
I have an issue in pulling out members having three or more events in a two-week period.
for example in the below table, id 111 is having 3 events in two week period. i.e; ranks 2,3,4 event dates are in 2 week period. so we need to pull this member.
Similarly for id 222 is having 5 events in 2 week period i.e;ranks with 1,2,3,4,5 , so we need to pull this member.
for id 333 is having only 2 events in 2 week period, i.e; rannks 1,2 are in 2 weeks but rank 3 is having event date '2016-02-21 00:00:00.000' which is 2 months from ranks 1 and 2.so we should not need to pull this member.Let me know if is not clear.
Thanks in advance. below is the sample data
declare
@events table
(
id
int
,
eventdate datetime,
ranks int
)
insert
into @events values (111,'2016-09-30 00:00:00.000',1)
insert into @events values (111,'2016-08-17 00:00:00.000',2)
insert into @events values (111,'2016-08-15 00:00:00.000',3)
insert into @events values (111,'2016-08-11 00:00:00.000',4)
insert into @events values (222,'2015-04-17 00:00:00.000',1)
insert into @events values (222,'2015-04-13 00:00:00.000',2)
insert into @events values (222,'2015-04-13 00:00:00.000',3)
insert into @events values (222,'2015-04-13 00:00:00.000',4)
insert into @events values (222,'2015-04-10 00:00:00.000',5)
insert into @events values (333,'2016-04-20 00:00:00.000',1)
insert into @events values (333,'2016-04-05 00:00:00.000',2)
insert into @events values (333,'2016-02-21 00:00:00.000',3)
Select * From @events
order by 1,3
October 17, 2017 at 11:28 am
dhanekulakalyan - Tuesday, October 17, 2017 10:04 AMHello everyone,
I have an issue in pulling out members having three or more events in a two-week period.
for example in the below table, id 111 is having 3 events in two week period. i.e; ranks 2,3,4 event dates are in 2 week period. so we need to pull this member.
Similarly for id 222 is having 5 events in 2 week period i.e;ranks with 1,2,3,4,5 , so we need to pull this member.
for id 333 is having only 2 events in 2 week period, i.e; rannks 1,2 are in 2 weeks but rank 3 is having event date '2016-02-21 00:00:00.000' which is 2 months from ranks 1 and 2.so we should not need to pull this member.Let me know if is not clear.
Thanks in advance. below is the sample data
declare
@events table
(
id int,
eventdate datetime,
ranks int
)
insert into @events values (111,'2016-09-30 00:00:00.000',1)
insert into @events values (111,'2016-08-17 00:00:00.000',2)
insert into @events values (111,'2016-08-15 00:00:00.000',3)
insert into @events values (111,'2016-08-11 00:00:00.000',4)
insert into @events values (222,'2015-04-17 00:00:00.000',1)
insert into @events values (222,'2015-04-13 00:00:00.000',2)
insert into @events values (222,'2015-04-13 00:00:00.000',3)
insert into @events values (222,'2015-04-13 00:00:00.000',4)
insert into @events values (222,'2015-04-10 00:00:00.000',5)
insert into @events values (333,'2016-04-20 00:00:00.000',1)
insert into @events values (333,'2016-04-05 00:00:00.000',2)
insert into @events values (333,'2016-02-21 00:00:00.000',3)
Select * From @events
order by 1,3
Maybe something like this could work:
WITH CTE AS(
Select *,
CASE WHEN DATEADD( wk, -2, eventdate) <= LAG( eventdate, 2) OVER(PARTITION BY id ORDER BY eventdate)
THEN 1 ELSE 0 END AS ThreeOrMoreEvents
From @events
)
SELECT DISTINCT id
FROM CTE
WHERE ThreeOrMoreEvents = 1;
October 17, 2017 at 2:32 pm
It is working for me, Thank you for keeping this reply.
October 17, 2017 at 3:39 pm
dhanekulakalyan - Tuesday, October 17, 2017 2:32 PMIt is working for me, Thank you for keeping this reply.
Thank you for the feedback. Do you understand how it works?
October 17, 2017 at 7:08 pm
Hi Luis,
I think I understand this but the way you used LAG function for finding ThreeOrMoreEvents was good thought. Can you also understand me how did you used this and how can I use this type of code or functions in this way in other requirements.
Thanks!!!
October 18, 2017 at 7:11 am
dhanekulakalyan - Tuesday, October 17, 2017 7:08 PMHi Luis,
I think I understand this but the way you used LAG function for finding ThreeOrMoreEvents was good thought. Can you also understand me how did you used this and how can I use this type of code or functions in this way in other requirements.Thanks!!!
LAG is used to retrieve the value of a column in a previous row according to the partition and order defined in the OVER clause. In this case, I'm going back 2 rows. That means that the previous row is also in the range period.
You could actually run the code in the CTE to check what is doing.
Select *, LAG( eventdate, 2) OVER(PARTITION BY id ORDER BY eventdate),
CASE WHEN DATEADD( wk, -2, eventdate) <= LAG( eventdate, 2) OVER(PARTITION BY id ORDER BY eventdate)
THEN 1 ELSE 0 END AS ThreeOrMoreEvents
From @events
Since I can't use OVER() in the WHERE clause, I need to define it as a CTE (or a derived table) and then use the column defined in the WHERE clause.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply