January 31, 2017 at 3:46 pm
Hi,
Event Counted
1/1/15 Y
1/1/16 Y
3/1/16 N
6/1/16 N
8/1/16 Y
I know how to do this via some tsql code w/ cursors e.g. find root event (first event) then count 180 days from that date. Then join back to the table and get the next min event date where the event date is >=180 days from the root event, that event is now the new root, join back to the table, etc etc., I'm wondering if there's a way I could do this within a single query (or without using cursors). Any thoughts?
January 31, 2017 at 4:24 pm
I believe you'd want to use windowing functions for this. Here's an example:
declare @temptable table(
eventdate date
)
insert into @temptable(eventdate)
select '20150101'
union all select '20160101'
union all select '20160301'
union all select '20160901'
select temptable.eventdate
, lag(eventdate) over(order by eventdate) as previous_date
, case when datediff(day, isnull(lag(eventdate) over(order by eventdate), '19000101'), eventdate) >= 180 then 'Y' else 'N' end as Event_Counted
from @temptable as temptable
Windowing functions like LAG() OVER() can't be used in a where clause, so to filter on that column you would need to do something like the below.
select eventresults.eventdate
from (
select temptable.eventdate
, lag(eventdate) over(order by eventdate) as previous_date
, case when datediff(day, isnull(lag(eventdate) over(order by eventdate), '19000101'), eventdate) >= 180 then 1 else 0 end as event_counted
from @temptable as temptable
) as eventresults
where eventresults.event_counted = 1
January 31, 2017 at 4:43 pm
Andrew P - Tuesday, January 31, 2017 4:24 PMI believe you'd want to use windowing functions for this. Here's an example:
declare @temptable table(
eventdate date
)
insert into @temptable(eventdate)
select '20150101'
union all select '20160101'
union all select '20160301'
union all select '20160901'select temptable.eventdate
, lag(eventdate) over(order by eventdate) as previous_date
, case when datediff(day, isnull(lag(eventdate) over(order by eventdate), '19000101'), eventdate) >= 180 then 'Y' else 'N' end as Event_Counted
from @temptable as temptableWindowing functions like LAG() OVER() can't be used in a where clause, so to filter on that column you would need to do something like the below.
select eventresults.eventdate
from (
select temptable.eventdate
, lag(eventdate) over(order by eventdate) as previous_date
, case when datediff(day, isnull(lag(eventdate) over(order by eventdate), '19000101'), eventdate) >= 180 then 1 else 0 end as event_counted
from @temptable as temptable
) as eventresults
where eventresults.event_counted = 1
Hi Andrew,
that's not quite there--it works for that data set you provided but not with what I provided. Your query works for the logic of 180 days from the last entry, what I'm looking for is 180 days from the last *counted* entry.
February 1, 2017 at 11:09 am
I believe that this will work.
;
WITH event_pvt AS
(
SELECT t.eventdate, e.is_start,
CASE
WHEN e.is_start = 0 THEN 'N'
WHEN LAG(e.is_start, 1, 0) OVER(ORDER BY e.eventdate) = 0 THEN 'Y'
ELSE 'N'
END AS Counted
FROM @temptable t
CROSS APPLY ( VALUES(t.eventdate, CAST(1 AS BIT)), (DATEADD(DAY, 180, t.eventdate), 0) ) e(eventdate, is_start)
)
SELECT e.eventdate, e.Counted
FROM event_pvt e
WHERE e.is_start = 1
ORDER BY e.eventdate
I've used 1 for start dates and 0 for end dates. You can substitute any two distinct values for these.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 2, 2017 at 4:19 pm
Drew,
Wow--very clever. That works, I would have never thought of that. Thank you so much for sharing. TAKE MY KARMA!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply