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.
| Author |
Topic |
|
Hades_1989
Starting Member
2 Posts |
 Posted - 2014-10-03 : 05:54:16
|
| Hello all,Currently I'm working on a dataset where I'm trying to match the date of a page visit by a customer to the churn date of this customer. I've decided that when a customer churns between 0 and 14 days after the page visit the churn can assigned to that visit. I want to group this by: weekofyear,partner and page. The problem I have is that when a customer visits a page more than once in a week and ONLY the last visit date lies within the set conditions (between 0 and 14 days before churn) then the date of the first page visit will be selected, but this date doesn’t match the conditions. So I’m seeking for a solution which always picks the lowest page visit date that is within the mentioned conditions. This is what I have now. select TO_CHAR(TO_DATE(a.pagina_visit),'YY-IW') as weekofyear , a.partner , a.page , min(a.page_visit_date) as Page_Visit_date , max(case when a.partner = to_number(b.partner) and a.page_visit_date < b.churn_date and a.page_visit_date >b.aangemaakt_op-14 then 1 else 0 end) as CHURN_AFTER_WEB--Churn within 14 days after first webpage visit , min(b.churn_date) as CHURN_AFTER_WEB_DATE--Date of churn after visiting webpage from WEBVISIT a left join CHURN b on a.partner = to_number(b.partner) and a.page_visit_date < b.churn_date and a.page_visit_date >b.churn_date-14 group by TO_CHAR(TO_DATE(a.pagina_visit),'YY-IW') , a.partner , a.page; |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-03 : 09:03:26
|
| 1. what do you mean by churn?2. can you please provide some sample data, what you get with your query and what you want to get? |
 |
|
|
Hades_1989
Starting Member
2 Posts |
Posted - 2014-10-03 : 11:24:12
|
quote:
Originally posted by gbritton
1. what do you mean by churn?2. can you please provide some sample data, what you get with your query and what you want to get?
1. A customer who terminates his contract.2. *Sorry don't know how to put a readable table in here and the image is very small so here a link to a beter image :https://imageshack.com/i/iqun8OEdp |
|
|
|
|
|
|
|
|
Matching the right date